Using One Front End to Manage Multiple Databases

[psycoperl]

I have been tasked with implementing a method of creating a databases and a front end for them.

What we need to have happen is:
For each term we need to have a new backend database (this is not optional it is part of the requirements from up on high)
We want to have a standard frontend that can access all the backends. What I would like is when the front end is opened, the users are presented with a choice of terms to work on.

My plan would be to have three standard access files
TERMXXX.BE.accdb - Which would be kept in a folder with all the other data for the term where XXX = Term Code
DBCATABLOG.accdb - Which would be kept in a standard folder and contain the locations of all the TERM databases
SYSTEMFE.accdb (.accdr) - Which would be our front end.

Is there a way to via VBA or Macro to on the choice of the term to have all the linked tables updated to view the appropriate data?

 

[SydneyGeek]

Yes, you loop through the connection strings and rewrite them to point at the alternate database.

The sequence would be:
1. Grab one connection string for a table you know is linked. Extract the database connection path. Give this a variable name, eg DB_OLD
2. Get the name of the new database from the user's selection. This could be based on data in a utility table, eg ztblBackends. This could be DB_NEW
3. Loop through the table connection strings, replacing DB_OLD with DB_NEW. You need to save each one as you process it.

Note: If you have database passwords on the back end databases, make sure that you use the SAME password on each database, or you will need to update passwords as well.

Denis

 

[HiTechCoach]

This may help:

How to Manage Your Linked Tables