Get a list of linked tables from a database

[Access nubie]

looking for a way to export the list of table names, table types & if they are linked (e.g. tbl Sales Linked .dbf or tbl Staff linked to excel) from a database - this has to be done for about 300 databases.

in an individual db, I have a make table query off of the table MSysObjects to get the data. The Database field tells me where the source of the linked table resides & the ForeignName field gives me an idea of the format of the data source (e.g. dbf or excel). I could manually import that query into each db, run it to get the table names, then copy & paste.... i'm trying to eliminate some of the manual labor. Any ideas would be appreciated.

 

[Pat Hartman]

This is a query that selects objects from the MSysObjects table based on their type. 6 = linked ODBC (Excel, SQL Server, etc) and 4 = linked Jet/ACE. There may be other types but that's all I have in this database.

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=4)) OR (((MSysObjects.Type)=6));

I don't have any code handy but if you set the db object to the database where you want to run the query, then run the query as a string rather than by running a querydef, that should do it for you. So, you'll need a loop that reads directories and finds the databases. Inside the loop, you set the db to what ever you just found. Then run the query string using that db. Piece o' cake

 

[Access nubie]

wow, ok, thank you. I'll need to get someone to help me with your above suggestion. this would be done as VB code within access? and then setup the looping to read all MDBs in the directory & puts the results into a table that will show the db names that have linked DBF files (or maybe the db names & names of tables linked to DBFs)?

 

[Pat Hartman]

No. What I posted was a query. Open the query builder and switch to SQL view. Copy the SQL string and paste it there. Then press the run button. Save the query and give it a name. you can then run the query whenever you need to.

The "MSys" tables are hidden by default so you probably won't see MSysObjects listed. If you want to see it, right-click at the top of the navigation pane to bring up the options. Check the box that says "show system tables". These are the secret, hidden tables that Access uses to store information about all the objects you create in your database. You can't change the data in these tables but you can view them and use them in queries.