ODBC Call Failed - Error 3151

Question

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, mostly 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.
8. We don't have to wait to see the error, it appears in less than one second after executing the task.

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

Answer 1

One thing to look at is the DNS setup (DNS not DSN!) in the network configuration for the workstations.

I had a client a couple of years ago who was encountering sporadic ODBC disconnects like this with some users, and it turned out that the primary DNS was set to point to the Internet provider's DNS. That worked fine for Internet, but the ISP knew nothing about the client's SQL Server's internal IP address. Changing the primary DNS to point to the local domain controller (which was acting as local DNS) resolved the issue permanently.

It may not be the cause of your problem, but it's worth a look.