Display all table names (Beginner)

[Mike H]

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)

[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

(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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

"Mike H" <mikehol...@beeb.net> wrote in message
news:O2GtdlDS...@TK2MSFTNGP12.phx.gbl...

[Douglas J. Steele]

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

"Mike H" <mikehol...@beeb.net> wrote in message

news:#IYJ35EX...@TK2MSFTNGP10.phx.gbl...
> Hello sorry if that is an inappropriate question for the newgroup. Can you
> advise me which newsgroup deals with questions about Access VBA (I
> misunderstood the Access developer as refering to writing code in Access).
>
>
>
> Thanks
>
>
>
> Mike
>
>
>
> "Douglas J. Steele" <djst...@canada.com> wrote in message
> news:#8xSWFGS...@tk2msftngp13.phx.gbl...