I'm helping a company upgrade from an all Access database to an Access 2000 frontend/SQL 2000 backend solution. I've got a situation that I haven't seen before.
Everything was running pretty smoothly. Then a couple of days ago a user reported that one of her Access queries returned the following error:
"ODBC -- connection to SQL Server failed". Seems like a simple connection issue, however it doesn't happen all the time. I verified that when Access is first opened, the query will throw the error listed above. If I then open any other query or table, and then try the troubled query, it will work and stay working for the rest of the time I have Access open, only to throw the error again once Access is restarted.
The query in question is calling only one table, it's very basic.
Does anybody have any idea what could be causing the problem described above?