ODBC connection failed in ACCESS 2007

[rum23]

This error occurs randomly but it appears quite frequently. Basically, this
happens if a series of queries are executed in MS access. Usually, the first
query executes fine and while executing the 2nd one, it bombs. Its sporadic.
Sometimes, the 1st and the 2nd queries execute just fine but bombs on the
3rd one. I did google on this and tried couple of resolutions but none of
them worked. Your help is appreciated!
MS ACCESS 2007 tables are linked tables to SQL Server 2005. ACCESS forms are
used as the front end and SQL server is the backend.

The error message is "ODBC--Connecction to 'myDSNName' failed". Error number
is 3151.

 

[boblarson]

Did you make sure to use the SQL Native Client driver and NOT the generic SQL
Server driver? (unless you are using Windows Vista)
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[rum23]

I wish I had found this forum a lot sooner. I was using the SQL server
driver all along and looks like that was the problem. After switching to SQL
Native client, things are working fine (atleast for now).

Can you tell me what is the difference between these 2 drivers? and explain
why I was getting this error?

Thanks a ton

 

[boblarson]

The reason why the other driver doesn't work completely with SQL 2005 is that
SQL Server 2005 has undergone several structural changes and therefore the
other driver couldn't know about those changes since it was something put
into place for SQL Server 2000 and prior. So, the reason why I also made
mention about Windows Vista is that if you are using that Operating System
then it has the revised SQL Server driver that now knows about all of the
2005 features, etc.

So, I hope that helps explain why you were having trouble with the generic
SQL Server driver as compared with the SQL Native Client driver.

--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[rum23]

OK. Thanks for explaning. I am using Win XP and after switching to SQL
native client, things are working just fine. Thanks a lot!

 

[rum23]

Unfortunately, this problem is back :-(. Do you have any other suggestions?
Please help.