Good day

My company recently upgraded from sql server 2000 to 2005, WG edition. However after the upgrade my adp app started running very slow, for example when scrolling between reports. A strange thing that I noticed is that when I connect the same app to sql express it doesnt have this problem. Also when I run the query for the report directly on the sql server it returns the results at lightning speed.

From what Ive read thus far Im suppose to connect to the sql native client on 2005. Ive written code to close the current connection, 'Application.CurrentProject.CloseConnection', and then re-open the connection with the following connection string:
"Provider=SQLNCLI.1;Data Source=ourServer;Integrated Security=SSPI;Initial Catalog=dbname"
but then it returns the following err message,
2147467259; Method 'OpenConnection' of object _CurrentProject failed.

Why is it giving me this message? Is there another way to connect to the native client? Or should I be doing something else to fix this problem...

Any help is appreciated.

Check out these examples to connect to SQL 2005 using various methods.

http://www.connectionstrings.com/?carrier=sqlserver2005

And just a bit for extra knowledge.

If you are using anything other than Windows Vista, you want to use the

SQL Native Client

ODBC driver, but if you are on Windows Vista, you want to use the

SQL Server

ODBC Driver.

Thanks for your replies.

However I dont think the error is just with the connection string. Even if I try to re-connect with the connection string that the adp project initially uses when starting up the app it still gives me the error. So hence the code goes like this:

Application.CurrentProject.CloseConnection

strConnect = "Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=theServer; Integrated Security=SSPI;Initial Catolog=dbName; Data Provider=SQLOLEDB.1"

Application.CurrentProject.OpenConnection (strConnect)

If I however have a mistake in my connection string it will tell me "Invalid Connection String". Could it be that sql server 2005 is not allowing me to connect again for some reason?

jpchiu gave you the link for where to go to get the actual connection string:

http://www.connectionstrings.com/?carrier=sqlserver2005

The string you are using doesn't appear to be a valid SQL Server 2005 connection string.

I did have a look and even when I copied and pasted the exact connection string, made the server and db changes, it still gave me the error...
ie...
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase ;Trusted_Connection=yes;