Access Caching ODBC connection settings

Question

My Company uses SAI heavily and in order to create reports from this system we use ODBC to connect to the DB. Are setup at the moment is regional and thus we have seperate environments for each region. When trying to access these environments using MS ACCESS I am finding that I cannot link to tables in different environments and retreive accurate data.

I can create the links to various environ ments using the specific DSN's. I then can access the data, however the first table that I open no matter the region it belongs to works fine. After that however all of the tables regardless to the region they belong to all use the dsn settings from the first table that was opened. If I shut Access down and restart I can then start with data from another environment which will then work, however the rest of the data will then reflect those dsn settings. Additionally when I look at the property sheet the DSN Settings are as they should be.

Let me also add that the tables for each region all have the same name. IE the sales table is sales in all environments, the production table is the production table in all regions. As a result as I link to the sales table from multiple environments the need to be renamed in MS ACCESS

Answer 1

One option you may have (though a bit long winded) would be to create an access database for each region which links to one regions tables. eg have the sales table and production table for region Europe in one access database, and put those from ASIA into another database.

Once you have this set up, you might find it much easier to then use a third access database to link to the two other access databases.

Hope this is useful.

Answer 2

I would have a look at this SO Question answered a last month.

It describes a couple of methods for forcing Access to re-link ODBC tables and, if all fails, provides you with a way to restart the database programmatically using a small function that you'll find on my blog.