Using VBA to modify linked table in a database

[Bob Piro]

I have several Access 2002 databases that have the same
structure/tables/queries etc. Each is named differently
according to geographic area. I have a common database
for reporting purposes that contains links to each
respective geographic database. One copy of the reporting
database contains links to one geographic database. I
currently need one copy of the reporting database for
each geographic database because the links are different.
I would like to create the links dynamically using VBA
code so that the modied links can be used by Queries and
Reports in the reporting database. In other words, the
result of the VBA code should be seen in the Linked Table
Manager window by selecting the menu item: Tools/Database
Utilities/Linked Table Manager.

Is this possible?

 

[Marshall Barton]

I have several Access 2002 databases that have the same
structure/tables/queries etc. Each is named differently
according to geographic area. I have a common database
for reporting purposes that contains links to each
respective geographic database. One copy of the reporting
database contains links to one geographic database. I
currently need one copy of the reporting database for
each geographic database because the links are different.
I would like to create the links dynamically using VBA
code so that the modied links can be used by Queries and
Reports in the reporting database. In other words, the
result of the VBA code should be seen in the Linked Table
Manager window by selecting the menu item: Tools/Database
Utilities/Linked Table Manager.

Fairly easy. Loop through the TableDefs collection and
check each one for a Connect property. If it has something
in the property, then change it to the new path.

To see the syntax in your linked table's Connect property,
use Debug.Print, but if it's linked to another mdb file,
it'll look like ";DATABASE=thefullpath".

Don't forget to use RefreshLink after you change a Connect
property.