How to get the list of ACCESS tables ?

  • Thread starter Thread starter christine.dessus
  • Start date Start date
C

christine.dessus

I would like to run a query (or a macro or ...) to extract
the list of tables (and if possible their structure) of an
ACCESS database.
The ACCESS database is Access97 version.

Thanks.
 
There is a built-in documenter:
Tools | Analyze | Documenter

For a query that returns the list of tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

To programmatically list the field names, types, and properties, see:
http://allenbrowne.com/func-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"(e-mail address removed)"
 
Here's the VBA Version:

Public Sub ShowTableDefs()

'Create a variable to hold a reference to the database.
Dim db As DAO.Database
'Create a variable to hold a reference to a database's TableDefs
Collection.
Dim tds As DAO.TableDefs
'Create a variable to hold a reference to a TableDef in the TableDefs
Collection.
Dim td As DAO.TableDef

'Set the db variable equal to the current database.
Set db = CurrentDb()
'Set the tds variable equal to the current database's TableDefs
Collection.
Set tds = db.TableDefs

'Loop through every TableDef in the TableDefs collection.
For Each td In tds
'Alter the following line to put the names wherever
'they are needed.
'Otherwise, this will print out the table names in
'the Immediate Window.
Debug.Print td.Name
Next td

'Clode the database variable.
'NOTE: The TableDef's Collection Variable tds cannot be closed,
' trying causes an error.
'NOTE: The "For-Each" loop deals with the td variable, too, trying
' to close it will cause an error.
db.Close
Set td = Nothing
Set tds = Nothing
Set db = Nothing

End Sub


This works in Access 2000 SR-3, Jet 4.0 SP-6, DAO 3.6. I'm *think* it will
work in Access 97, but don't know for sure.


"(e-mail address removed)"
 
Back
Top