get list of MSAccess dbs that link to ODBC tables

[barlee]

Is there a way to find out what MS Access databases on my network have links to an ODBC table on my SQL Server?

I want to see how many databases are calling to any given SQL table so I can make some decisions about the maintenance of the SQL table.

Thanks....:banghead:

 

[CJ_London]

Do you know where all the db's are and do you have access to them?

If so you could write some VBA to open each db in turn and either inspect MsysObjects or the TableDefs collection to see which ones have a link to the SQL Server table

 

[barlee]

yes, I know where they are located, and I have access to them. I don't have the VBA expertise to know how to frame a script to look in dozens of different folders for dozens of databases..... what other options might work?

 

[CJ_London]

Don't think there are any - So far as I know SQL Server does not have any security around granting access to specific Access applications.

Is it practical for you to populate a table with the path and db name of all of these db's. If so, I can write up a quick vba routine for you to get you on your way.

 

[CJ_London]

If you have say 3 dozen paths and 250 dbs across these paths you could have two tables - one with the paths and a PK and one with the dbnames plus a FK to the paths table