Display all table names (Beginner)

Display all table names (Beginner)

Post by Mike » Sun, 13 Jul 2003 14:49:29


Hello, I need to display a series of msgboxes with the name of one table in
each, and going on until all the tables in my database have been displayed.

I would expect to use a for each loop, but can't work out what object
collection I
would be looping through

eg. I would expect something like...

for eaxh x in 'tables'
msgbox x.name
next x

How do I do that in access (and why? - I'm trying to learn Access VBA)
 
 
 

Display all table names (Beginner)

Post by Douglas J. » Sun, 13 Jul 2003 19:41:42

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

(Note that if you're using Access 2000 or 2002, you'll need to add a
reference to DAO. With any code module open, select Tools | References from
the menu bar, scroll through the list of available references until you find
the one for Microsoft DAO 3.6 Object Library, and select it.)

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;

By the way, I don't think this question was particularly appropriate for the
developer toolkit newsgroups. These groups are for questions about using the
Developer edition, which allows you package your application along with a
royalty-free run-time version of Access. People without Access installed can
install the run-time, and use your application.





in
displayed.

 
 
 

Display all table names (Beginner)

Post by Douglas J. » Fri, 08 Aug 2003 06:40:46

icrosoft.public.access.modulesdaovba (or
microsoft.public.access.modulesdaovba.ado, if your question is specifically
related to using ADO)


"Mike H" < XXXX@XXXXX.COM > wrote in message
news:# XXXX@XXXXX.COM ...
a