Table List via ADO

[Steve Murphy]

Does ADO provide an option like the DAO TableDefs collection to get a list
of tables in a database?

Related question: What is the industry norm regarding DAO and ADO with
Access? Which of the two is normally used today?

 

[david epsom dot com dot au]

Use DAO with a Jet MDB database, or if you are more
comfortable with DAO. Use ADO with a SQL Server/MSDE
database, or if you are more comfortable with ADO.

ADOX has a catalog collection. You can also do some
xml stuff, or (with sql server) select from the
schema 'table', or with jet, select from the
MsysObjects table.

(david)

 

[Douglas J. Steele]

David's already given you an answer about DAO vs. ADO.

To get a list of tables strictly using ADO, you can use:

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

 

[Steve Murphy]

David and Doug,

Thanks for the help.

I've also found that I can use the AllTables collection. Are there any
foreseen problems with that approach?

Thanks again,
Steve Murphy

 

[Brendan Reynolds]

It depends what you want to do. If you just want a list of table names, the
AllTables collection is fine, but if you need to manipulate the table
definitions in some way, the AllTables collection does not expose the
properties and methods that are exposed by the TableDefs collection.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.