CODEWITHSUNDEEP
excelvbams-accessado
kamelkid2
kamelkid2

Reputation: 534

return a list of all access (.mdb) tables with an excel macro

I have an mdb file location defined in Sheet1 range B1. This value is:

"C:\Users\User\Desktop\Test.mdb"

What I am looking to do is generate a list of all the tables in this file and return it within excel. I have a partially working script but it is returning unwanted items

I am working off of this:

Sub GetTableNames()

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim lRow As Long
Dim szConnect As String

LastRowSetup = Worksheets("Setup").Cells(Rows.Count, 1).End(xlUp).Row 'last row where table names populate

If LastRowSetup < 10 Then
    LastRowSetup = 10 'so we dont accidentally clear important data above this
End If

Sheets("Setup").Range("A10:A" & LastRowSetup & "").ClearContents 'clear old data

fStr = Sheets("Setup").Range("C2").Value 'file location of mdb

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fStr & ";"

Set cnn = New ADODB.Connection
cnn.Open szConnect
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn

lRow = 10
For Each tbl In cat.Tables
Sheet1.Cells(lRow, 1).Value = tbl.Name
lRow = lRow + 1
Next tbl

cnn.Close
Set cat = Nothing
Set cnn = Nothing

End Sub

however it is returning many additional things that are NOT table names. for instance

~TMPCLP313341
~TMPCLP74661
Approved_table1
Approved_table2    
MSysAccessStorage
MSysAccessXML
MSysACEs
MSysNameMap
MSysNavPaneGroupCategories
MSysNavPaneGroups
MSysNavPaneGroupToObjects
MSysNavPaneObjectIDs
MSysObjects
MSysQueries
MSysRelationships

when i physically open the mdb all i see are 'table 1' and 'table 2'. is there a way to implement an additional filter in the ADODB connection to not return all of the temp files and Msys objects or is this something i will just have to filter after importing.

note that i had to set a reference to the Microsoft ADO Ext. 2.X for DDL and Security object library as well as the normal ADO object library.

Upvotes: 1

Views: 1771

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123419

is there a way to implement an additional filter in the ADODB connection to not return all of the temp files and Msys objects or is this something i will just have to filter after importing.

No, you will have to loop through the table names and simply ignore the system ("MSys...") and temporary ("~...") tables.

Upvotes: 1

Fadi
Fadi

Reputation: 3322

try this code:

For Each tbl In cat.Tables
 If Left(tbl.Name, 4) <> "MSys" And Left(tbl.Name, 1) <> "~" then
  Sheet1.Cells(lRow, 1).Value = tbl.Name
  lRow = lRow + 1
 end if
Next tbl

Upvotes: 4

Related Questions