We are in the process of upgrading from SQL Server 2005 to 2008 and I have found that the connection strings in my Access 2007 databases are no longer working. Here is the connection string:




Public Function set_connection()

set_connection = "Provider='sqloledb.1';Data Source='TSQLINST1/TSQLINST1';" & _
"Initial Catalog='Actg';Integrated Security='SSPI';"

End Function

This is contained in a module in each Access 2007 database and is referred to in the following manner in VBA code:


1 strCnxn = set_connection()
2 Set cnxn = New ADODB.Connection
3
4 cnxn.Open strCnxn
5 cnxn.CommandTimeout = 0
6 strSQL_Execute = "dbo.CEMS_Deletion"
7 cnxn.Execute strSQL_Execute, , adExecuteNoRecords

The code above is bombing out at the line 4 with the following error message:

Run-time error '-2147457259 (80004005)':

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

I have went to
http://www.connectionstrings.com/
and tried several different variations of the connection strings under the SQL Server 2008 area and all have yielded the same result. I can get to the ACTG database through SQL Server Management Studio with my windows signon so I know access is not the problem. My DBA has compared my permissions setup under 2005 and 2008 and they are the same. The system DSN is set up under SQL Server Native Client 10.0 so I know that is not the problem. Is there something that I need to activiate under Tools>References when I am in VBA that I don't know about? I am at my wits end with this and have Googled thist to death with no answers (actually came up with a lot of answers but none that worked). Any help would be appreciated on this matter. Thanks.

I am also running 32 bit windows XP.

Dave