Linked Tables Question

[Dave_cha]

Hi,

I have several Access db's setup, all using linked tables back to a MySQL db server on the network. So effectively, MS Access is the frontend with MySQL providing the backend.

One problem I've always had when modifying existing MS Access frontends is that I'm typically having to work with production backend data. To develop within a test environment would require me to replace all the linked tables with links to the test server tables and then re-link back to the production tables when ready to move to production. This is a pain and time consuming so I generally end up working with test data on the production database.

Does anyone know of a quick way to point linked tables from one server to another and back? Is there VBA syntax to script the setup of linked tables and delete existing ones? That would at least allow me to write a routine which might take a while to write but at least I could then run it whenever I need to switch from one environment (Prod / Test) to another.

Any help would be appreciated.

Thanks,

Dave

 

[tehNellie]

You can view the connection information in Msysobjects, but a quick try and altering the settings in the "connect" column hasn't worked. Not much help I know, but it might give you some pointers.

I've no idea if this will work, but if you're starting from a DSN, does editing the DSN from production to Live servers/database and then refreshing the linked tables make any difference?

 

[Dave_cha]

Hi. Thanks for the reply though unfortunately I've previously tried these options without success.
Editing the Connect field in Msysobjects would in theory do the trick however this field doesn't allow edits and I can't find a way around this.
Also, when creating a DSN linked table, certain criteria such as database, server and password are hardcoded into the Connect field therefore changing the DSN doesn't work.

 

[tehNellie]

Right then, assuming your table names are the same.

1) Create two DSNs, one for dev, one for live.
2) Link you tables using the dev DSN
3) Select Linked Table manager
4) Select the tables that apply and tick the box "always prompt for new location"
5) Click OK
6) In the DSN link choose your Live DSN and click "OK"

Your table names should stay the same, but you should see the server detail in the linked table manager update.

You can also just use a single DNS and edit the Server/Database information in Notepad and then follow the same steps as above.

uncheck "always prompt for new location" after you've updated if you're going to chuck the DB out ot a few people then they can continue to refesh the tables in the event new columns etc are added.

I've tested both methods here and they appear to work as long as the tablenames are the same.

 

[Dave_cha]

Thanks. Will give that a go.