Not all tables deleted in code 1

[Quehay]

I'm deleting Table Links in code. One iteration through the process leaves some tables still there. This was fixed by an outer "FOR i = 1 to 4" loop, but this isn't a pristine solution.

Any insight into why this code leaves some linked tables still in the TDefs collection?
[tt]
Private Sub DeleteLinks()
On Error GoTo Error_DeleteLinks
Dim Db As DAO.Database
Dim Tdf As DAO.TableDef
Dim strTable As String
Dim intCount As Integer

Set Db = CurrentDb

For Each Tdf In Db.TableDefs

If Len(Tdf.Connect) Then

strTable = Tdf.Name

Db.TableDefs.Delete strTable
Db.TableDefs.Refresh



End If

Next

Exit_Error_DeleteLinks:
Exit Sub

Error_DeleteLinks:
Debug.Print Err.Number & vbTab & Err.Description
Resume Exit_Error_DeleteLinks
End Sub
[/tt]

 

[JimHorton]

I've ran across the same thing. Put the names into an array--ReDim'd with the count of tabledefs. It's 2 loops, one to fill the array, one to loop the array and delete, but it's a sure thing.
--Jim

 

[Quehay]

Jim,

Something like this? Then do the delete link with each item in the array?
[tt]

Dim Db As DAO.Database
Dim Tdf As DAO.TableDef
Dim intArr As Integer
Dim vntTemp() As Variant

intArr = 0

For Each Tdf In Db.TableDefs

If Left(Tdf.Name, 4) <> &quot;msys&quot; Then

If Len(Tdf.Connect) Then

ReDim Preserve vntTemp((intArr) + 1)
vntTemp(intArr) = Tdf.Name

intArr = intArr + 1

End if

End If

Next

[/tt]

 

[JimHorton]

Even better:

Set rst = db.openrecordset(&quot;Select Name from MsysOBjects where type = 6&quot;,dbopensnapshot)

do until rst.eof
db.Tabledefs.Delete rst!name
rst.movenext
loop

 

[Quehay]

Jim,

Thanks for the example! I'm puzzled though, the first time you said fill an array and then iterate through its members with the tdefs.delete code. This is just one iteration through a recordset that comes from Msys (pretty slick--I've never seen this before).

Is this something that you found and like better? Do you find this reliable? (I'll try it to see...)

FYI: Even with an outer i= 1 to 4 loop as mentioned in the first post (going through the tdf's 4 X and deleting every table with a connect string) some tables are left. This looks like an object/design failure...I tried doing a Refresh after each deletion from the TDF's collection, but a cursory reading of the Refresh Help description indicates that this is not really pertinent here. Similarly, the DBEngine.Idle doesn't appear to have any effect either--I'd thought maybe it was a timing issue.

 

[JimHorton]

The MsysObjects in one I use for combo boxes alot, when I want to list tables, queries, etc. It's also good for ad-hoc version control, check the DateUpdated on different objects in different .mdb copies, since the file date of an mdb typically changes by simply opening the db.

Anyway, as I was doing the array example, I started thinking about getting the actual count of the type of tables to delete, for the ReDim, and started with Dcount(&quot;name&quot;,&quot;Msysobjects&quot;,&quot;Type = 6&quot, then it dawned on me to just use a snapshot from Msys.

In the original example though, I had just redim'd the array with the total tabledef count, then looped once to fill the array with the names of the linked tables, not caring that there were unused array elements, then looped the filled array elements and deleted.

It seems with the very first way of doing it, just looping and deleting, the tabledefs collection is dynamically refreshing, with or without the explicit refresh. Then when you hit the 'new' max number in the collection, the original max number in the For loopis still being used, so the error comes. That's just my guess, but it seems logical.
--Jim

 

[Quehay]

OK, I see your rationale--this can be used to fill the array cleanly with only linked tables(type 6 is a linked table). The array sample I put in above is another way of checking for the proper table (linked) to put into the array--it checks to make sure that it's not an &quot;Msys&quot; table
(first IF) and then if Len(tdf.connect) (returns TRUE if it's a linked table) REDIM PRESERVE Array(intIndex + 1).


Maybe another way would be to do would be count backwards through each item in tdfs and delete if table passes the two tests:

[tt]
Dim intCount as Integer

FOR intCount = (Db.tdfs.count - 1) to 0 Step -1

If tdfs(intCount).Name <> &quot;msys&quot; Then

If Len(tdfs(intCount.Connect) Then


tdfs(intCount).Delete [/tt]

etc.

If this cleans out the tables with one pass I'll post.


Thanks for your input Jim!

 

[Quehay]

OK, this removed over 20 tables cleanly both times that I ran it, with no stragglers:

[tt]
Private Sub DeleteLinksa()
On Error GoTo Error_DeleteLinks
Dim Db As DAO.Database
Dim Tdfs As DAO.TableDefs
Dim Tdf As DAO.TableDef
Dim strTable As String
Dim intCount As Integer

Set Db = CurrentDb

Set Tdfs = Db.TableDefs

For intCount = (Tdfs.Count - 1) To 0 Step -1

If Left(Tdfs(intCount).Name, 4) <> &quot;Msys&quot; Then

If Len(Tdfs(intCount).Connect) Then


Tdfs.Delete (Tdfs(intCount).Name)


End If

End If



Next

Exit_Error_DeleteLinks:
Debug.Print Err.Number & Err.Description
Exit Sub

Error_DeleteLinks:

MsgBox &quot;An error has occurred in the table linking--please contact Administrator&quot;, _
vbCritical, &quot;TABLE LINKING ERROR&quot;

Debug.Print Err.Number & vbTab & Err.Description
Resume Exit_Error_DeleteLinks
End Sub


[/tt]

 

[JimHorton]

Who ever thought going backwards could be such an elegant solution--sometimes the best way is right under our noses, so close we have trouble seeing it right away!
--Jim

 

[Guest_imported]

The problem you are experiencing with looping though the tabledefs collection is this.

When you delete a table from the collection Access moves removes the table pointer and then updates the index positions of all the tables in the collection. When you remove the table at index 3, all table positions above 3 get decrememented by 1. The table that was at position 4 becomes 3 and gets skipped over by the loop.

This is also explains why looping backwards works.

Hope this helps clear up the mystery

 

[Quehay]

Lewman,

Thanks for pointing that out--it's what I suspected was happening but then thought &quot;Why when the (&quot;Name&quot rather than (index) was being used???&quot; Oh well... ;-)