ODBC-Call Failed - Access

We have this fairly simple Access database with a link table to a view in an SQL Server. The Select Query in Access is a simple select from this view with a view criteria set. Nothing fancy. What is driving is moderately crazy is that this Select Query in Access seems to work fine for months and than all of a sudden (same query performed around the same time with about the same workload on the SQL-Server) it responses with the helpful "ODBC-Call Failed" message. Closing the Access database, taking a coffee, coming back againg and try the exact same Select Query normally works, no "ODBC-Call Failed" message.

It not happens always, mosty we're fine.

When it happens it happens for all subsequent queries tried. We have to close and reopen the Access database and pray to the good Lord that it works.

We would be very interested in someone with a solution to this problem.

What ODBC driver and version are you using and to what version of SQL Server are you connecting?

You might want to try out a different ODBC driver in your DSN to see if that resolves the problem.

Vaughn

|||We are having the same problem using Access 2003 and SQL Server 8.0. I'm using the standard SQL Server driver in the DSN. Any suggestions?|||

Reading a bit it sounds like the "ODBC-Call Failed" message is coming from Access. I found this KB article that describes a similar problem for DB2 and suggests updating Jet as the solution.

http://support.microsoft.com/kb/227413/

I see you're using Access 2003 so this isn't likely the cause. Have you tried ODBC tracing through the ODBC Data Source Administrator control panel? Correlating that log to when the failure occurs might provide more clues as to the cause.

Vaughn

|||The problem is that I don't know what is the cause of the error. Is it a timeout on the client (MSAccess) side? Is it a deadlock situation? Is it table lock that couldn not be caried out? etc. Neither SQL Server nor MSAccess seem to bother to explain what's wrong. If if happend all the time I would look at replacing drivers but now I feel this should be somewhere else. Btw ODBC tracing gives me a bunch data wich not really eplains what 's going on.|||

So, this might have been a timeout afterall. In design view I changed the properties of the view from Snapshot to "Dynaset (Inconsistent Updates)" (although MS fails to describe in the online help what that exactly means). I would go with Snapshot as there is no need to update the data, but due to the fairly large amount of records, MS recommends using Dynaset. Furthermore the Timeout of the view was changed to 900 seconds (15min), if our SQL Server is doing some real work this is probably not enough. It would be helpfull that instead of ODBC-Call failed, MS-Access would report something like "Timeout expired", but there probably is a deeper reason why this is not possible.

Leaving the question: Dynaset, Dynaset (Inconsistent Updates), Snapshot? As an experienced P/SQL and T/SQL I have no clue what MSAccess is talking about here. The documentation is pretty dodgy also. I fact I only need to read the data (it's for reporting).

|||

I found some information that may shed some light on the differences at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acproRecordsetType_HV05187937.asp

Basically, a Dynaset allows you to make changes in controls bound to a single table or tables with a one-to-one relationship. You can also edit the fields from a table on the "many" side of a one-to-many relationship.

A Dynaset (Inconsistent) allows you to update all data fields.