Dynamic table linking to different geograpic db with same tables

[Guest]

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?

 

[John Nurick]

Hi Bob,

Yes, it's possible. The code here
http://www.mvps.org/access/tables/tbl0009.htm should get you started.
Some Access books - e.g. the Access [your version] Developer's Handbook
- have similar procedures.

 

[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.

This should give you some ideas about hhow to do it:

http://www.mvps.org/access/tables/tbl0009.htm