Unanswered: ODBC Connection Failed UNTIL a linked table is opened
Here's a strange scenario. We have an Oracle backend and its tables are linked to Access 2007 (accdb) with ODBC.
When a user runs a query that contains linked tables to the Oracle backend they receive an error "ODBC--connection to 'ebsstudents' failed".
This seems to happen randomly - sometimes it works, other times it throws the error.
I have found that if a user opens one of the linked tables first, when they subsequently run the query it works.
Although I'm delighted to have found a workaround, I'm more interested in working out what the problem is.
I had that same error using ODBC to connect FoxPro files to Access. The problem was found to be that every once in a while I was trying to open a FoxPro file that was already opened by another user in Exclusive mode. When the default of the FoxPro file was changed (in FoxPro) to open it in Shared mode, the problem disappeared. Maybe that will work with Oracle as well.