Access 2003 connecting to SQL Server 2005 - DSN settings with Windows 7

[glgcag1991]

I'm creating a file DSN to store on a network location for use with an Access 2003 mdb that has linked tables that are connected to a SQL Server 2005 database. (There are also local mdb tables which is why it's still an mdb.) Considering that I am having Access 2003 connect to SQL Server 2005, does anyone have a recommendation of how to setup the DSN? All the users have Windows 7 machines and Access 2003. There are three options: SQL Native Client (SQLNCLI.DLL), SQL Server (SQLSRV32.DLL) or SQL Native Client 11.0(SQLNCLI11.DLL). I'm using this DSN to link the tables and for pass-through queries. I would love some advice because I know things can go mysteriously wrong if I use the wrong settings and I want to be optimal.

One other note, when I make ADO connections to the SQL Server in VBA code the only connection string that will work looks like: "Provider=SQLOLEDB;Data Source=SQLServ1;Initial Catalog=DataDB;Integrated Security=SSPI;"

Any ideas why this is the only connection string I can get to work in code?

Thanks!

 

[PHV]

Have a look here:

http://www.connectionstrings.com/sql-server-2005

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[glgcag1991]

PHV, thank you! I tested all of the potential drivers and providers and they all work. But I'm a little concerned that if I elect to use SQL Server Native Client 11.0 that there might be problems being that I'm using Access 2003 and SQL Server 2005. Any down side to using it that you know of?