Deleting Attached Tables (XP)

Topic

New Reply

Thanks for the fast answer to my last post Hans. When I change from a jet backend to an SQL backend (or vice versa) is there a fast way to delete the existing table attachments without deleting front end tables? I’ve used to following that looks at the Foreign name to tell if it is an attached table – it seems to work but are there going to be any instances where the foreign field won’t give me what I want?

SQL1 = “SELECT Len([ForeignName]) AS LenFN, MSysObjects.Name ” _
& “FROM MSysObjects WHERE (((Len([ForeignName]))>0));”

Set rstAttachedTables = CurrentDb.OpenRecordset(SQL1, dbOpenSnapshot)

Do Until rstAttachedTables.EOF

strTableName = rstAttachedTables!Name
CurrentDb().TableDefs.Delete strTableName
rstAttachedTables.MoveNext
Loop

Reply | Quote

Replies

The ForeignName property is probably dependable. but I would use the Connect property of the TableDef object:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
dbs.TableDefs.Delete tdf.Name
End If
Next tdf

Set tdf = Nothing
Set dbs = Nothing

Reply | Quote

Your code works but requires 4 passes to delete all the tables, I have stepped through the code and all looks OK but each time I run it it leaves some undeleted. Very strange.

Reply | Quote

Does this work better?

Dim dbs As DAO.Database
Dim i As Long

Set dbs = CurrentDb
For i = dbs.TableDefs.Count – 1 To 0 Step -1
If Len(dbs.TableDefs(i).Connect) > 0 Then
dbs.TableDefs.Delete dbs.TableDefs(i).Name
End If
Next i

Set dbs = Nothing

By looping backwards, you should avoid the problem, hopefully.

Reply | Quote

That got it – thanks.

Reply | Quote

That got it – thanks.

Reply | Quote

Does this work better?

Dim dbs As DAO.Database
Dim i As Long

Set dbs = CurrentDb
For i = dbs.TableDefs.Count – 1 To 0 Step -1
If Len(dbs.TableDefs(i).Connect) > 0 Then
dbs.TableDefs.Delete dbs.TableDefs(i).Name
End If
Next i

Set dbs = Nothing

By looping backwards, you should avoid the problem, hopefully.

Reply | Quote

Your code works but requires 4 passes to delete all the tables, I have stepped through the code and all looks OK but each time I run it it leaves some undeleted. Very strange.

Reply | Quote

The ForeignName property is probably dependable. but I would use the Connect property of the TableDef object:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
dbs.TableDefs.Delete tdf.Name
End If
Next tdf

Set tdf = Nothing
Set dbs = Nothing

Reply | Quote