Unable to connect to SQL Server

[shantiP]

I am using the following code to connect to my server

Set db_sql1 = New ADODB.Connection
db_sql1.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & uname & ";password=" & pwd & ";Initial Catalog=CCFTEvent;Data Source=" & IPSqlServer
db_sql1.Open

but am getting the following error...
error [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Can anyone help please?
The best part is that this very code has become machine dependant. On some machines it works and on some it doesnt.

 

[Zekske]

It's possible that your ODBC-driver for SQL is too old on the machines where your code isn't working.
Check the SQL-driver version in the ODBC screens or install the latest MDAC version wich you can find on microsoft download...

 

[shantiP]

Are you trying to say that "Provider=SQLOLEDB.1" is finding different versions on different machines? OK let me check that.

 

[SQLBill]

I had a very similar problem with one of my users. Only difference was their error message indicated they were using Named Pipes (we use TCP/IP). I confirmed they were following my directions exactly and setting TCP/IP. We tested and the only way to duplicate the error message was by using Named Pipes.

We had the sysadmin load the latest version of MDAC on the user's computer and everything worked fine.

So, as Zekske said, install the latest MDAC on the computers that are having this trouble.

-SQLBill