Search for Specific Access DBs - Outside Connections?

[thbart]

Our company has over 2000 different Access DBs. Some of them communicate with other systems. We are undergoing an upgrade and am wondering if there is any way to poll all DBs (special search tool or ??) to see if they connect to these other systems.



I know some of the Access DBs do not but I am looking for a way to see which ones do without having to locate and launch all of them to see if they include the communication with other systems.

[rpeare]

You could write some vba to cycle through all folders in your system, looking for access databases, when it finds one, open it up, see if it has any linked tables in it then 'flag' that access database (i.e. write to a table the path/name of the database and a yes/no if it contains external links). I haven't ever tried this but it should be possible with a combination of filesystemobject commands and looking at the mysysobjects table of each database.

here's how to cycle through folders with filesystemobject:
http://www.4guysfromrolla.com/webtec...ect/faq5.shtml

here's how to look at mysysobjects tables within MS access (NOTE: Be extremely careful if you go this route, do not try to change any data on the table or you *will* corrupt your database(s), try your code on a database that does not matter first to make sure it functions the way you want before going any farther):

http://bytes.com/topic/access/answer...-2007-database

Counts tables linked to other ODBC sources (not other access databases)

You can use len(tdf.database) to determine if other access database tables are linked into the database too.