Drowning in an ocean of tables.

[thornmastr]

I am looking at a database with a large but unknown number of tables. I need to capture the names of all the tables on the database. Doing a search of google, the consensus of opinion is I cannot build such a list using any ADO constructs but DAO will work quite nicely and it does. Below is the routine I am using.

Public aTableNames() As String

Public Sub sTestNames()
Dim dbMyDB As Database
Dim td As TableDef
Dim f As Field
Dim sStr As String
Dim sMypath As String
Dim iknt As Integer
sMypath = App.Path & "\dbaseIII.mdb"
Set dbMyDB = OpenDatabase(sMypath)
iknt = 0
ReDim aTableNames(0 To 1000) As String
For Each td In dbMyDB.TableDefs
If Left$(td.Name, 4) <> "MSys" Then
aTableNames(iknt) = td.Name
iknt = iknt + 1
End If
Next
ReDim Preserve aTableNames(0 To iknt - 1) As String
End Sub


While this works very nicely, I am used to and more comfortable referencing only the ADO libraries and now I find myself referencing both ADO and DAO.

Is there not some ADO functionality to do the above?

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@earthlink.net

 

[ArtieChoke]

You bet! Check out the OpenSchema method of the connection object. Works great. The only problem I had with it is for a large amount of tables, the query might time out, so you might need to up the default timeout value.

 

[LostInCode]

Search this forum and the VB Databases forum for OpenSchema.

You can also use ADOX (reference to MS Ado Ext. xx for DDL and Security) to do as you are familiar with DAO.TableDefs:

Public Sub ADOX_Tables()
Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = Form1.conn
Debug.Print cat.Tables.Count
Stop
End Sub

 

[thornmastr]

Thanks to both ArtieChoke and LostInCode. Both methods work very nicely. My preference, simply because of the way I code, is to go with the ADOX methodology.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@earthlink.net