CODEWITHSUNDEEP
vbams-access
Lucas Frugier
Lucas Frugier

Reputation: 287

Delete all linked tables in VBA

I need to delete all tables which are linked in my access database.

This is the query which is returned all the linked tables (odbc and access linked tables) :

SELECT [MSysObjects].[Name] FROM MSysObjects WHERE Left([Name],1) <> "~" AND (MSysObjects.Type = 6 OR MSysObjects.Type = 4) ORDER BY MSysObjects.Name;

But now i think i have to do a for each with this DoCmd.DeleteObject acTable, Table.name in order to close all my tables, but i don't know how to do.

Upvotes: 3

Views: 5694

Answers (2)

rshah4u
rshah4u

Reputation: 481

For Each iterates through a collection in an un-ordered sequence and by deleting an object within that collection during iteration is the reason you encounter an error.

I would suggest that you iterate through the collection in an ordered sequence in reverse order to delete the TableDef objects using:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
For index = dbs.TableDefs.Count – 1 To 0 Step -1
    Set tdf = dbs.TableDefs(index)
    If Left(tdf.Connect, 5) = "ODBC;" Then
    DoCmd.DeleteObject acTable, tdf.Name
    End If
Next index

Upvotes: 5

E Mett
E Mett

Reputation: 2302

Dim rs as New ADODB.Recordset
Dim strSql as string

strSql = [put your sql statement here]

rs.open strSql, CurrentProject.connection
Do while not rs.eof
   Docmd.DeleteObject acTable,rs.Fields(0)
   rs.MoveNext
Loop
rs.Close

Upvotes: 1

Related Questions