ODBC Linked tables keep on getting error 3151

[tmacdonald]

Here's my issue, I have a access 2003 database on a network drive with linked
tables to a SQL Server 2005 database. I have 4 users on 4 different PC's
that need to connect to the database. I have configured ODBC connections on
each of these 4 different PC's to the SQL database. However everytime a
different user connects to the access database it tells then that the ODBC
connection failed and then they have to go into the linked tables manager and
reconnect to the SQL tables.

I don't want them to have to reinitialize the connection every time a
different user connects to the database only when a change is made to the
tables.

Am I missing a setting when I am configuring there ODBC connection? Or is
there a better approach to take. Mind you there is already some data in the
database.

Any assistance would be greatly welcomed

 

[Tom van Stiphout]

On Tue, 26 Jan 2010 17:30:01 -0800, tmacdonald

Each user has a copy of the front-end on their own PC, right?

-Tom.
Microsoft Access MVP

 

[tmacdonald]

No, there is just one File with linked tables to SQL Server 2005. The file
that each user goes to is the same on the network. I take it this is a
mistake?

 

[Douglas J. Steele]

Yes, it's a mistake.

 

[tmacdonald]

I figured out the problem, for anyone else who runs into this issue, I was
using the default SQL Server ODBC driver instead of the new SQL Native
Client. It took care of my issue on all of the machines.

Douglas,

How would you suggest that I recreate the database then?

 

[Douglas J. Steele]

No recreation required. Simply give everyone his/her own copy of the
database.