Create an array containing names of tables in the database

[Dom]

Dear Newsgroup,

I would like to populate a VB array with the names of all
the tables in the database. Is there a way to get a list
by querying the database if I don't know the table names
apriori?

Hope you can give me some leads

Dom

 

[Douglas J. Steele]

There are at least 4 approaches that I'm aware of.

One is to loop through the DAO TableDefs collection:

Dim tdfCurr As TableDef

For Each tdfCurr In CurrentDB().TableDefs
PrintDebug tdfCurr.Name
Next

Set tdfCurr = Nothing

Another is to loop through the ADOX Tables Collection:

Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table

sTable = "customers"

Set oCat = New ADOX.Catalog
' Replace advworks.mdb with the name of your database
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\advworks.mdb;"

For Each oTable In oCat.Tables
PrintDebug oTable.Name
Next

Set oTable = Nothing
Set oCat = Nothing

You can also use ADO:

Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset

Set oConn = New ADODB.Connection
' Replace advworks.mdb with the name of your database
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\advworks.mdb;"

Set oRs = oConn.OpenSchema(adSchemaTables)
Do Until oRs.EOF
Debug.Print oRs!TABLE_NAME
oRs.MoveNext
Loop

oRs.Close
Set oRs = Nothing
Set oConn = Nothing

The 4th involves querying the System Catalogs:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "msys*") AND ((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name;

 

[Dom]

Douglas,

More than I could ever have hoped for.
Thank you so much for your help.

Dom

-----Original Message-----
There are at least 4 approaches that I'm aware of.

One is to loop through the DAO TableDefs collection:

Dim tdfCurr As TableDef

For Each tdfCurr In CurrentDB().TableDefs
PrintDebug tdfCurr.Name
Next

Set tdfCurr = Nothing

Another is to loop through the ADOX Tables Collection:

Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table

sTable = "customers"

Set oCat = New ADOX.Catalog
' Replace advworks.mdb with the name of your database
oCat.ActiveConnection

= "Provider=Microsoft.Jet.OLEDB.4.0;" & _