Switching linked tables by code

[JimmyTheHand]

Hello

I'm designing an Access frontend to an ODBC database. Tables of the database are linked into the frontend, and I have queries in the frontend that rely on the linked tables.

Now, the database has a test instance and a live instance. I do the development in the test environment, and every time when a new module is ready, I have to link the frontend to the live DB. Which means, that I have to delete the linked tables of the test DB, and link to the same tables in the live DB. Considering that the development is a long process, and the users want to use everything that is already usuable, I have to do this quite often.

I'm looking for a way to automate the process. Is it possible?

I noticed that table MySysObjects has a field called 'Connect', which stores information about the linked database. It would be so simple to replace that data by the new one, but Access doesn't seem to like the idea, for it resists modification. Any workaround to that? Other ideas?

Thanks,

Jimmy

[HiTechCoach]

Jimmy,

See is this works for you: Relink ODBC tables from code (Click Here) (http://access.mvps.org/access/tables/tbl0010.htm)

[JimmyTheHand]

Wow, this is my lucky day :)
I didn't expect a useful answer so quickly. I'll try the code (which will take a few days, I suspect), and get back to you with a feedback.

Thanks again,

Jimmy

[HiTechCoach]

Jimmy,

You're welcome.

Let us know how it goes.