Dynamic Linking of Tables at Runtime

[ffrippy]

I have inherited a number of ship databases in MS Access, all with a very similar front end, each linked to two mdb files, one containing Baseline Data and one containing Working Data. I want to create a single front end which will allow me to access the Baseline and Working data for each ship by, say clicking a button with the ships name on it, but I can't find how to dynamically link the tables in VBA. It seems logical that tables can be linked at runtime but I just can't find a command that will allow me to do it. Has anyone any ideas?

Many Thanks In advance

ffripp

[Seth Schrock]

See the following link: Is it possible to store the location of the backend database in a table?

Seeing as how you have two backends, you might want to create a table that lists all of your split tables and their location and loop through that table when you want to check where it is so you can connect to the different locations.

Just curious, why do you need the tables to be dynamically linked in VBA? Access stores the linking information on its own and as long as the backend location doesn't change, it will remained linked without any VBA involved.

[ffrippy]

Thanks for your response Seth,I'll have a go at creating a table listing the split tables based on the code you have provided.

Each of the Backend pairs contain exactly the same tables (same names) but with different data. There are 10 ship databases, and the least work option seemed to be to leave the tables alone and just link them as required.

Many Thanks


ffripp

[Seth Schrock]

If the names of the tables are the exact same, then all you would need is a table with a list of possible locations. You would then have a combo box that would allow you select which location you want to connect to. You might have to disconnect from the previous location before you can connect to the new location, but I'm not sure.

This seems like a really strange design. I think it would be easier to combine the information from like table names into one table. If separation is needed, then add a Category field so that all the records from location 1 would have a category of "Location 1" and all the records from location 2 would have a category of "Location 2". Then you wouldn't have to worry about the relinking.

[NeoPa]

The link should provide full details of how to handle the technical side of that. What you need to do now is to decide when you want the links to change and what should trigger that. This will determine where the new code should go in your project.