Cycle through tables in another DB from code

[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!

 

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

 

[Marshall Barton]

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.

 

[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];