Drop or delete selected queries and all table links in the database.

[Heri101]

Experts,
Please I need some sort of VBA code to drop selected queries (qryA,qryB,qryC ) and all table links in (MS Access 2007) database.

I am just a beginner, so please help me with complete code,

Thanks and best regards to all of you,

Heri.

[ADezii]

Be very careful on how you use this code! The following code will Delete all Queries starting with 'qry', and all Tables starting with 'tbl':

Dim qdf As DAO.QueryDef
Dim tbl As DAO.TableDef

For Each qdf In CurrentDb.QueryDefs
  If Left$(qdf.Name, 3) = "qry" Then
    CurrentDb.QueryDefs.Delete qdf.Name
  End If
Next

For Each tbl In CurrentDb.TableDefs
  If Left$(tbl.Name, 3) = "tbl" Then
    CurrentDb.TableDefs.Delete tbl.Name
  End If
Next

[Heri101]

ADezii,
Thanks for reply, my tables have no "tbl" prefix, but I can differentiate them as linked tables; I want to drop all links but not the current database tables, could you please be more specific,
Best regards,
Heri.

[ADezii]

You would then reference the Connect Property of the Tables, then Delete only those whose Connect String <> "" or > 0.

[Heri101]

ADezii,
I really appritiate your help, looks like code does work, but it will not refresh the navigation pan, unless I piont my cursor on one of the object name, any idea?

[ADezii]

After you execute the code, place this as the last line in the Procedure:

RefreshDatabaseWindow

[Heri101]

ADezii,
It works perfectly, once again thanks for your great help.

[ADezii]

You are quite welcome.