Link to home
Start Free TrialLog in
Avatar of Bobby
BobbyFlag for United States of America

asked on

Access 2003, find all instances of database ODBC

We have a MySQL database we'll call DB1. We now also have a DB2 (with a different name on same server), which replaces DB1. How can I find all references to DB1 in code and behind queries and either list them so I can manually change them OR change them all to DB2?
Avatar of PatHartman
PatHartman
Flag of United States of America image

In any Access database,  choose Navigation options (right click on All Access Objects at the top of the Nav Pane in A2016).  Choose the option that says Show System Objects.  The MSysObjects table lists all objects in the database.  For linked Jet/Ace tables, Type = 6 and Database = fulll path to linked database.

So, viewing this table in each database will tell you where all tables are linked

SELECT MSysObjects.Database, MSysObjects.Type
FROM MSysObjects
WHERE MSysObjects.Type =6
GROUP BY MSysObjects.Database, MSysObjects.Type

If the BE you are concerned with is always in the same location, change the WHERE clause to look for it specifically.

SELECT MSysObjects.Database, MSysObjects.Type
FROM MSysObjects
WHERE MSysObjects.Database = "C:\Data\UsefulDatabases\c2012TableMaint\TableMaintExample.mdb"
GROUP BY MSysObjects.Database, MSysObjects.Type

I
Avatar of Bobby

ASKER

In Access 2003 I'm not seeing "Navigation" options. Btw, if it matters, the backend of the .mdb is not JET, it's SQL and MySQL. The new DB2 is a MySQL db on our remote web server that is connected via ODBC.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial