ODBC Call Failed - Error 3151

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.

After two months of research, we found one solution, change from DAO to ADO. This is the answer MS gave me:

I’d like to make you aware that implementing the workaround (using ADO instead of DAO) could be less time consuming than trying to figure out why DAO doesn’t seem to work. Therefore it might be advisable using the workaround instead of trying to find the root cause of this issue, especially as it doesn’t seem to be reproducible.

However; here is what I’d like to you do next:

1. Please replace the file DSN by a system or User DSN.
2. Use the ‘normal’ ODBC driver for SQL- Server instead of the native driver.
3. After creating the user DSN re link all the tables.

4. In VBA code use explicit type declarations only instead of implicit declarations. So please replace use DAO. In front of any database type declarations in order to explicitly create DAO objects. For example replace the following:

   Dim dbs As Database
   Dim rstAppend As Recordset
   By
   Dim dbs As DAO.Database
   Dim rstAppend As DAO.Recordset

Switching from TCP/IP to Named Pipes in the Client Configuration for the SQL Server ODBC Driver did the trick for me.

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.

I was having this issue as well. What i did was added the DSN to the SQL database in the User tab. I noticed it was working on my dev system, and that's the only difference i could find. After creating the DSN on the users PC, it worked.