prefered ConnectionString using ADO?

[msc0tt]

I've seen many different ways to 'connect' to a SQL database. I'm using ADO and I need the 'best' DSN-less way. One site recommends the following:

With cn
.ConnectionString = "Driver={SQL Server};" & _
"Server=(erpserv);" & _
"Database=" & sDB & ";" & _
"Uid=" & sUser & ";" & _
"Pwd=" & sPass
.CursorLocation = adUseClient
.Open
End With

I've defined sDB, sUser, and sPass. 'erpserver' is the DNS name on our LAN of the SQL 2000 server.

After about a 15 second delay, I get the error "Client unable to establish connection".

-with thanks.

 

[NoCoolHandle]

Ok... my preference would be to use a true ado connection, rather than one that goes through odbc - just one more layer to slow things down.

However your fundimental problem is that you didn't specify a provider.. which in the case of your connection string would be [red]provider=msdasql;driver={sql server}[/red] etc.....

For your server I would tend to change things to make sure that you use a trusted_connection (ie use the NT login)
Choice 1
[blue]connectionstring="provider=sqloledb;server=erpserver;database=sDB;Integrated Security=sspi[/blue]

Choice 2 (using SQLSErver security)
[green] .ConnectionString = "Provider=msdasql;" & _
"Driver={SQL Server};" & _
"Server=(erpserv);" & _
"Database=" & sDB & ";" & _
"Uid=" & sUser & ";" & _
"Pwd=" & sPass
[/green]


HTH


Rob

 

[msc0tt]

Thanks Rob. Sadly, I continue to get the same error. I have confirmed the database, user, and password yet again. I have to use the SQLsecurity method for this app.

Could you elaborate on the comment about using a 'true ADO
connection'? Perhaps this is the direction to take.

I am assuming the Server= parameter refers to the DNS name of the server (i.e. I can open a CMD box and 'ping erpserv' successfully).
-thanks.