Question ODBC Call Failed - Error 3151

[rodpedja]

We have an Access database migrated from Access 97 to Acces 2007 with some linked tables to an SQL Server 2008 database. We are using a File DSN in the ODBC Administrator. Connection tests work fine there. In fact, we have a main form in the Access 2007 application that shows the data always correctly. The problem appears when we execute a task that makes the following call:
Dim dbs As Database
Dim rstAppend As Recordset

Set dbs = CurrentDb
Set rstAppend = dbs.OpenRecordset(strAccessTable, dbOpenDynaset, dbSeeChanges)

OpenRecordset throws the error number 3151 and occurs randomly but it appears quite frequently. It's sporadic. When we don't get the error, the task works fine.

1. It not happens always, mosty we're fine.
2. When it happens it happens for all subsequent tasks tried. We have to close and reopen the Access database and pray to the good Lord that it works.
3. Sometimes, refreshing the tables in the Linked Table Manager solves the problem, but others don't.
4. We receive sometimes an error "ODBC--Call Failed" when refreshing the Linked Table Manager. Deleting the File DSN and creating a new one solves the problem.
5. We have tried with two different drivers (SQL Server and SQL Server Native Client 10) and in both cases the problem remains.
6. Also we have tried to put the Access File in the same machine than the SQL Server and the problem remains.
7. We have increased the ODBC Timeout for all the querys from 60 to 180 seconds,but the problem still remains.

We would be very happy if someone could help us to find a solution to this problem.

 

[namliam]

I cant be sure, but the spradicness of this makes me think of a timeout problem...

Try playing with your timeout settings in the properties of the connection/linked tables see if that helps

 

[rodpedja]

I cant be sure, but the spradicness of this makes me think of a timeout problem...

Try playing with your timeout settings in the properties of the connection/linked tables see if that helps

Hi namliam,

thank you for your answer. I have increased the ODBC Timeout for all the querys from 60 to 180 seconds, but the problem still remains :-(

 

[namliam]

Does it look like the 3 minutes is actually the problem, i.e. are you waiting 3 mins before you get the error?? Or are you getting the error (much) faster?

 

[rodpedja]

No, because I get the error much faster, in less than one second. Any idea?? I think that the problem is in the ODBC File DSN..

 

[namliam]

Does it happen upon first connect or does it happen when you have been using the db/connection for a bit?

I.e. It could be the target db is closing the connection for being idle?

 

[rodpedja]

When it happens, it happens upon first connect.