Mysterious Tables in ADOX Deleting

[PSUIVERSON]

I wrote some VBA that will delete all tables at the beginning of each month before running all my imports and queries etc. to setup monthly tables etc. I DO NOT WANT TO DELETE TWO TABLES so I wrote some VBA using ADOX to do this...Very weird problem is occurring that I need some help on. When I run this code and to a breakpoint and trap the tablename count and tblName at certain points it's returning table names that I do not have or too large count. For example - I manually deleted all the tables but TABLE1 and TABLE2. So theoretically when going through the code it should have a tbl.count = 2 and tblNames should be those two when checking. However I am getting a table count = 9 and names such as "MySysAccessObjects" going through. What is this?

HERE IS THE CODE:

Function Cleanup___Pre_Monthly_Conversion()
On Error GoTo Cleanup_Err

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim tblName As String
Dim tableExists As Boolean
Dim i As Integer

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

i = cat.Tables.count

For Each tbl In cat.Tables
tblName = tbl.Name
If tblName <> &quot;TABLE1&quot; And tblName <> &quot;TABLE2&quot; Then
cat.Tables.Delete (tbl.Name)
Else
MsgBox (&quot;We cannot delete the following table &quot; & tbl.Name & &quot; .&quot
End If
Continue:
Next

MsgBox &quot;&quot;&quot;Clean Up Completed. You may now begin the monthly conversion process.&quot;&quot;&quot;, vbOKOnly, &quot;&quot;

Cleanup_Err:
MsgBox Error$
Resume Continue



End Function

 

[cmmrfrds]

Before going through the delete process create a loop and display all the table names and table TYPES. Only attempt to delete user tables NOT system tables as that will cause you problems. To see the hidden system tables go to the menubar and check the hidden table and object check boxes.

 

[PSUIVERSON]

Excellent advice but now it's not seeing every table when it goes through the code:

&quot;For Each tbl In cat.Tables&quot;

It seems to selectively pick which tables it sees. Do you know what I mean? I have to keep clicking the RUNCODE and executing until all the tables are gone. THEORETICALLY it should run once and go through all the tables and skip those that are ACCESS TABLES, SYSTEM TABLES or NAMED the two I pinpointed - while delelting the rest? I feel as though that line of code above is not working correctly. Any help?

HERE IS MY MOST RECENT CODE:

Function Cleanup___Pre_Monthly_Conversion()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim tblName As Variant
Dim tblType As Variant
Dim i As Integer
Dim count As Integer

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

count = cat.Tables.count

For Each tbl In cat.Tables
tblType = tbl.Type
tblName = tbl.Name
If tblName <> &quot;finsum_import&quot; And tblName <> &quot;ONNN&quot; And tblType <> &quot;ACCESS TABLE&quot; And tblType <> &quot;SYSTEM TABLE&quot; Then
cat.Tables.Delete (tbl.Name)
Else
MsgBox (&quot;The following table cannot be deleted &quot; & tbl.Name & &quot; .&quot
End If
Next i

MsgBox (&quot;Clean Up Completed. You may now begin the monthly conversion process.&quot

End Function

 

[cmmrfrds]

It almost sounds like the delete is messing up the table list in the for each loop. Maybe try picking up the table names to delete and putting them in an array, then issue a delete on each table in the array.

 

[PSUIVERSON]

Yes - that is what is occurring. Thanks for your help. I'll post the final code once I get it up and running...

 

[PSUIVERSON]

I ran it with a large cushion in the array. I should have less than 50 tables so I put the array at 50. I am somewhat green with this stuff and tried capturing a variable for table.fields.count # into i then declaring the ARRAY(i) but that obviously doesn't work. Any suggestions...My solution which works great...

Function Cleanup___Pre_Monthly_Conversion()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Dim i As Integer
Dim counter As Integer

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

' Array to capture table values
Dim tblType(50) As Variant
Dim tblName(50) As Variant

counter = cat.Tables.count
i = 0
counter = 0
' Assign values to table array
For Each tbl In cat.Tables
tblType(i) = tbl.Type
tblName(i) = tbl.Name
i = i + 1
counter = counter + 1
Next

For i = 0 To counter - 1
If tblName(i) <> &quot;TABLE1&quot; And tblType(i) <> &quot;SYSTEM TABLE&quot; AND tblType(i) <> &quot;ACCESS TABLE&quot; Then
cat.Tables.Delete (tblName(i))
Else
MsgBox (&quot;The following table cannot be deleted &quot; & tblName(i) & &quot; .&quot
End If
Next

MsgBox (&quot;Clean Up Completed. You may now begin the monthly conversion process.&quot

End Function