Cycle through tables in another DB from code

C

cherman

I want to reference the table names in one Access DB from another Access DB
via code. Any suggestions on how to do this?

Thanks in advance!
 
M

MargaretBartley

I want to reference the table names in one Access DB from another Access DB
via code. Any suggestions on how to do this?

This will list out all the tables in a database:


Option Compare Database
Option Explicit

Sub ListFieldsinRemoteDB()
Dim db As Database
Dim tdefs As TableDefs, tdef As TableDef
Set db = DBEngine.OpenDatabase("C:\BC\BSS\Moore\DOP Data.mdb")
For Each tdef In db.TableDefs
Debug.Print tdef.Name
Next tdef
db.Close
Set db = Nothing
End Sub

You can, of course, use a variable for the name of the remote database.

This will list out all the tables, including the system tables. You can add
some logic to exclude the system tables, if you choose.
 
M

Marshall Barton

cherman said:
I want to reference the table names in one Access DB from another Access DB
via code. Any suggestions on how to do this?


You can open the other db using OpenDatabase then reference
a table through the TableDefs collection.
 
A

Allen Browne

Here's another solution, using a simple query to read the tables in another
database:

SELECT MSysObjects.[Name]
FROM MSysObjects IN 'C:\MyFolder\MyFile.mdb'
WHERE (MSysObjects.[Type] = 1)
AND NOT ((MSysObjects.[Name] Like "~*")
OR (MSysObjects.[Name] Like "MSys*"))
ORDER BY MSysObjects.[Name];
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top