How to retrieve the odbc database name of tables in ms-access VBA

Question

I have a ms-access database and several ODBC linked tables in it.

As I have 2 set of ODBC database ,one is for production the other is for development, they have different names Development and Production.

How can I get the odbc info of the linked tables by using VBA?

Answer 1

Using the local name of the table, you can query the MSysObjects system table (typically hidden) for the table's Foreign Name.

SELECT MSysObjects.ForeignName
FROM MSysObjects
WHERE (((MSysObjects.Name)="LocalTableName"));

If you need more information about the foreign table, try your hand at parsing the 'Connect' column from the same table.

Answer 2

In fact, you can use ODBC to connect to the .mdb file as if it were an Access database. The linked tables will show up in that ODBC connection and be accessible at full ODBC speeds.

The nice thing about doing it that way is your program doesn't even have to know if the tables are linked tables or not. It's nice to contain all these sysadmin-level details in a single place.

Answer 3

Since you mentioned using VBA to retrieve information, I'll suggest the TableDef object.

You can determine the remote database where the source table resides:

CurrentDB.TableDefs("YourLinkedTableName").Connect

You can determine the name of the remote table to which your link points:

CurrentDB.TableDefs("YourLinkedTableName").SourceTableName

Beyond that, I'm not clear about what information you're looking for.