Access97 to SQL Server ADO connection string probl

[bnath001]

Hello,
I am trying to connect Access97 to SQL server 7.0 using the
following connection string. It gives an error saying "Connection could not be establshed to the specified SQL Server".
Am I missing anything in this connection string.

Public adoCN As ADODB.Connection
Public Const ADOConnect As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=pubs"

Private Sub CmdInvoice_Click()
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim varNumber As Variant

Set adoCN = New ADODB.Connection ' Normally on initial open of application
adoCN.Open (ADOConnect)

End sub

thankx

 

[culshaja]

The only thing that could be wrong is that the SQL Server network software doesn't know where pubs is. Try using Query analyzer or the MMC to connect to this SQL Server. if you can't then you need to make sure that the Clent Network utility is configured correctly.
More basic though is check that the user does have access rights into the database as you are using Integrated Security so the SQL server needs to have that user registered with it.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[bnath001]

I am able to use SQL server query analyzer without a problem.

Could you pls check the connection string again. I am doubting if that is correct.

thankx