ODBC error 3151 (Connection failed, Access <-> SQL Server)

ODBC error 3151 (Connection failed, Access <-> SQL Server)

Post by U3RlcGhhbk » Fri, 18 May 2007 03:51:00


Hi,

We are using SQL Server and Access for a database application. Database
access is via ODBC.
The application was functioning correctly since appr. 2 years - but suddenly
we get the >>error 3151 "ODBC--connection to <...> failed"<<

This error is only raised by one function in VBA (when archiving the
records): Everytime when copying records from one SQL-Server database
(active) to another SQL-Server database (archive) first everything works fine
for about 1 min 30 sec. Then there's a pause of about 20 secs. Then I get the
error described above. Both databases are hosted on a single SQL server. The
archiving process uses transactions.

After a while the ODBC connection seems to recover.

-- Software:
Access 2002 (I don't know the exact version right now)
SQL Server 2000 (8.00.760 - SP3)
ODBC Driver version 2000.86.1830.00 (25.03.2005)

-- Data Quantities:
- Archive table (appr. 100000 records + sub-tables. In total appr. 500000
items)
- Active table (appr. 500 records + sub-tables)

Any help would be appreciated.

Thanks,
Stephan
 
 
 

ODBC error 3151 (Connection failed, Access <-> SQL Server)

Post by SurferJo » Fri, 18 May 2007 06:23:31

Google it, this search string returned a bunch of articles;

ODBC Error 3151

 
 
 

ODBC error 3151 (Connection failed, Access <-> SQL Server)

Post by U3RlcGhhbk » Sat, 19 May 2007 18:29:00

Thanks for your answer, but the reason why I posted the problem here, was
that I was not very successful with google before. I found very few articles
on that specific problem. And these were just problem descriptions. No
solutions.

However, today I have luckily found one hint:
One posting suggests to use ADO recordsets instead of DAO recordsets,
because DAO seems to be buggy.
I will try to find more information on that. I hope this may be a solution...
 
 
 

ODBC error 3151 (Connection failed, Access <-> SQL Server)

Post by ZG9kd » Tue, 14 Aug 2007 17:52:02

Hi,

Has somebody found the problem?? I get the same error after 1 minute 15.

Dominique.
 
 
 

ODBC error 3151 (Connection failed, Access <-> SQL Server)

Post by U3RlcGhhbk » Wed, 15 Aug 2007 20:28:00

No solution yet. So I do a work-around. I have implemented a mechanism which
catches the error and does a retry for several times.
This is not very satisfying, I admit, but actually it works.

I have not tested ADO recordsets up to now.
 
 
 

ODBC error 3151 (Connection failed, Access <-> SQL Server)

Post by ZG9kd » Wed, 15 Aug 2007 20:34:00

Thanks for your answer. That is also what I did in my code.

The interesting thing is that it displays the error 3151 after exactly 1
minute. I tried to change ODBC connection timeout (default 60) of SQL server
ODBC driver, but without any success.
 
 
 

ODBC error 3151 (Connection failed, Access <-> SQL Server)

Post by U3RlcGhhbk » Fri, 24 Aug 2007 18:12:00

I would like to ask dodu a question:

What is the odbc driver version you are experiencing the problems with?
The same "SQL Server 2000.86.1830.00" that we use?
I wonder if it has something to do with that.
 
 
 

ODBC error 3151 (Connection failed, Access <-> SQL Server)

Post by ZG9kd » Fri, 24 Aug 2007 18:36:00

Hi Stephan,

The ODBC driver I am using is : SQL Server 2000.85.1117.00