Cycle through tables in another DB from code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
I want to reference the table names in one Access DB from another Access DB
via code. Any suggestions on how to do this?
Click to expand...

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.
 
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?
Click to expand...


You can open the other db using OpenDatabase then reference
a table through the TableDefs collection.
 
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];
 
Back
Top