I have an Access 2003 database running from a mapped drive "Z:\database.mdb".
Three PC's have access to z:\database.mdb
The database has it's own tables as well as linking into some tables of SQL Server 2000.
I have setup System DSN to the SQL server on all the PC's.
Now the PC on which I created the database and linked all the tables can open the linked table just fine. But when I try to open the linked table on any of the other PC's I get the error ODBC --Connection to 'XXXXX' failed.
But here's the twist, if I refresh a linked table (call it table1) on another PC using 'Linked Table Manager' in Access. Then I can double click on table1 on that PC and open that table, but only on that pc. If I go back to the original PC where the table was first linked it can no longer open the table. The original PC can regain 'control' of the table by using 'Linked Table Manager' in Access and again refreshing the table.
So experts I believe that because all three are calling the same database.mdb the settings for the linked table are saved for one PC only. Thus if another PC comes along they get the ODBC --Connection to 'XXXXX' failed error.
Can any experts clarify this and provide a workaround.