ACCESS 2007 Link to Microsoft SQL 2005

Error:
... ODBC connection to SQL Server {IP} failed........

I make a code in vb6 using MS ACCESS 2007 with ADO as object in which data was imported or link to Microsoft SQL 2005 in order that I will not change any data from my SQL server. Please help me the correct procedures and connection string... I am just a beginner... thank you very much.

=====================================================

... the error change now to "runtime error 3709 - the connection cannot be used to perform this operation. It is either closed or invalid in this context" after re entering this code....

Private Sub Form_Load()

Dim MyConnObj As ADODB.Connection

Dim myRecSet As ADODB.Recordset

Dim sqlStr As String


Set MyConnObj = New ADODB.Connection
MyConnObj.CursorLocation = adUseClient
MyConnObj.ConnectionString = " PROVIDER=SQLOLEDB; SERVER=\\myServer; UID=myPass; PWD=myPass; DATABASE=myDatabase;"
        
Set myRecSet = New ADODB.Recordset

sqlStr = "SELECT dbo_CEN_EQUIPMENT.equipment, dbo_CEN_MODEL.model desc AS model_desc, dbo_CEN_EQPT_MASTER.jolocation, dbo_CEN_EQPT_STAT.Eqpt_Stat, dbo_CEN_EQUIPMENT.RISK FROM dbo_CEN_EQPT_STAT INNER JOIN dbo_CEN_EQUIPMENT INNER JOIN dbo_CEN_MODEL ON dbo_CEN_EQUIPMENT.eqpt id = dbo_CEN_MODEL.eqpt id INNER JOIN dbo_CEN_EQPT_MASTER ON dbo_CEN_MODEL.model id = dbo_CEN_EQPT_MASTER.model id ON dbo_CEN_EQPT_STAT.Stat_ID = dbo_CEN_EQPT_MASTER.EqptStatus WHERE dbo_CEN_EQUIPMENT.RISK=3 AND dbo_CEN_EQPT_STAT.Stat_ID<>3 And dbo_CEN_EQPT_STAT.Stat_ID<>4 And dbo_CEN_EQPT_STAT.Stat_ID<>8 And dbo_CEN_EQPT_STAT.Stat_ID<>9;"
 
myRecSet.Open sqlStr, MyConnObj

Set MyConnObj = Nothing

Set myRecSet = Nothing

MyConnObj.Close

myRecSet.Close

Please help... Thank you so much in Advance....

2 answers