connection error - urgent, please

[chinedu]

If I use this code below to connect to an access database, the code works.
it works in that all records selected get displayed to the screen.


cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& server.MapPath("myDB.mdb")

Problem with the above code, though is we have migrated our database to sql server.

Now I try using the following:

cnnSearch.Open "Driver={SQL Server};" & _
"Server=dw2;" & _
"Address=dw2,1433;" & _
"Network=DBMSSOCN;" & _
"Database=myDB;" & _
"Uid=sa;" & _
"Pwd=pass"

It doesn't error out but I just get a message that says:

x number of records found. Displaying page 1 of 12

Then it gives column headers but no value for them.

I am using the above code for 2 reasons.

one, I am connecting to our sql server db remotely.

Second, dsn won't work for the code I am using.

I tried it before when I was implementing the code to be used for our access database.

Any urgent help would be greatly appreciated.

 

[chinedu]

Please ignore my post.
It is working now.

This method worked for me.

Provider=SQLOLEDB.1;Password=mypass;Persist Security Info=True;User ID=myUser;_
Initial Catalog=TrafficCountsPDF;Data Source=dw2