Re-linking tables

[ggoldberg]

I am creating a procedure to copy the tables from an SQL database to a split database on a laptop (for standalone operation) and then link them up to a newly refreshed copy of the application data base. I have a form in the backend folder that does the following:
a) Import all SQL tables into the backend folder (app_be.mdb) for the application.
b) Import all primary key information from the SQL server and apply to newly imported tables.
c) Download a fresh copy of the application database (app.mdb) into the application folder.
d) Re-link all tables in the app.mdb to the backend database app_be.mdb. This linking is to be done from the backend database.

My question is: How can I do the linking step from a form located in the app_be.mdb database? Is there some way I can use TransferDatabase but specify that I want to link tables in the application database to the tables in the backend database, even though the executing code is located in the backend database?

Thanks...

 

[psemianonymous]

Two things:

1. Neat process you have there.
2. You are going to have to update the ".Connect" property of each TableDef in your database front-end. This means that at the least, you are going to have to find some way to open up the front-end database programmatically.

Consider instead just loading the tables directly into the front-end itself. (i.e. delete all the table links and just import new tables)

Otherwise, I'd recommend you make a function that updates all your table .Connect properties, make a macro that calls the function, and then open the front-end from your back-end using "Shell ("start pathname\yourFRONT-END-MDBFILE.MDB /x macroname&quot". This will satisfy part d) of your original question.
That way you can skip the meddlesome step of updating tabledef properties.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm