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.
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
Set dbs = CurrentDbDim 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
New keyword.