Connecting to SQL Server using domain account

[mattheus0525]

Hi.

I am asked to connect to SQL server to retrieve a record from its table from MS Access directly.

Given the following lines of code, I am not able to connect to the SQL server because the login id used is a domain account not sql server login id .


Private Sub Command0_Click( )

Dim lobjCn As New ADODB.Connectio n

With lobjCn
.ConnectionStri ng = "Provider=sqlol edb;Server=LDNP CM05421V05A,105 01;Database=TMS ;uid=INTRANET\s ysemtmslive;pwd =test123;Truste d_Connection=no "
.Open
End With

End Sub

I would like to seek for your advise on how can I connect to the SQL server from MS Access using the domain login id and password I specified in the above-mentioned code. Can you provide me with a code to test.

Thanks and I am hoping that you could help me the soonest possible time.

Best Regards,
Matt

[MMcCarthy]

This should work. Make sure you have the ADO library enabled. In the VB Editor go to Tools - References and make sure that the Microsoft ADO library is clicked.

 
Private Sub Command0_Click()
Dim lobjCn As New ADODB.Connection
lobjCn.Open = "Provider=sqloledb;Server=LDNPCM05421V05A,10501;Database=TMS;" & _ "uid=INTRANET\sysemtmslive;pwd=test123;Trusted_Connection=no"
 
End Sub
 
 

What do you want to do once you've made the connection. The following example will show you the kind of things you could do:

 
Public Sub CompleteDatabaseExample() 
    Dim dbMain as New ADODB.Connection' Declaring the Connection 
    Dim rsCustomer as New ADODB.Recordset' Declaring the Recordset 
    Dim SQL as String' Declaring a simple variable 
 
    dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ ' Connecting to the Database 
        "Persist Security Info=False;" & _ 
        "Data Source=C:\WINDOWS\Desktop\training.mdb" 
 
    SQL = "SELECT * FROM Customer"' Setting up our SQL Command statement 
    rsCustomer.Open SQL, dbMain, adOpenDynamic, adLockOptimistic' Opening the Recordset (see Recordset Options) 
    Do While Not rsCustomer.EOF' Loop until we reach the End-Of-File marker 
        MsgBox "The Customer's Name is: " & rsCustomer("Name") 
        MsgBox "The Customer's City is: " & rsCustomer("City") 
        rsCustomer("Country") = "United States" 
        rsCustomer("Name") = rsCustomer("Name") & " Jr." 
        rsCustomer.Update' Updating the Records (see also Recordset (Working With)) 
        rsCustomer.MoveNext' Moving to the next record (necessary to reach the EOF) 
    Loop 
    rsCustomer.Close' Closing the recordset 
    dbMain.Close' Closing the database connection 
 
End Sub
 
 

[mattheus0525]

Hi.

I already selected the Microsoft ADO library but still fails. The login id I provided in the code is a domain account not the SQL server login id itself. I believe I cannot access the sql server using the domain login id. Do you have any idea of impersonating a sql server login id to get connected? I need to fetch the data from the sql server and insert them to the MS Access table.

Again, thanks for the prompt reply.

:)Matt

This should work. Make sure you have the ADO library enabled. In the VB Editor go to Tools - References and make sure that the Microsoft ADO library is clicked.

 
Private Sub Command0_Click()
Dim lobjCn As New ADODB.Connection
lobjCn.Open = "Provider=sqloledb;Server=LDNPCM05421V05A,10501;Database=TMS;" & _ "uid=INTRANET\sysemtmslive;pwd=test123;Trusted_Connection=no"
 
End Sub
 
 

What do you want to do once you've made the connection. The following example will show you the kind of things you could do:

 
Public Sub CompleteDatabaseExample() 
    Dim dbMain as New ADODB.Connection' Declaring the Connection 
    Dim rsCustomer as New ADODB.Recordset' Declaring the Recordset 
    Dim SQL as String' Declaring a simple variable 
 
    dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ ' Connecting to the Database 
        "Persist Security Info=False;" & _ 
        "Data Source=C:\WINDOWS\Desktop\training.mdb" 
 
    SQL = "SELECT * FROM Customer"' Setting up our SQL Command statement 
    rsCustomer.Open SQL, dbMain, adOpenDynamic, adLockOptimistic' Opening the Recordset (see Recordset Options) 
    Do While Not rsCustomer.EOF' Loop until we reach the End-Of-File marker 
        MsgBox "The Customer's Name is: " & rsCustomer("Name") 
        MsgBox "The Customer's City is: " & rsCustomer("City") 
        rsCustomer("Country") = "United States" 
        rsCustomer("Name") = rsCustomer("Name") & " Jr." 
        rsCustomer.Update' Updating the Records (see also Recordset (Working With)) 
        rsCustomer.MoveNext' Moving to the next record (necessary to reach the EOF) 
    Loop 
    rsCustomer.Close' Closing the recordset 
    dbMain.Close' Closing the database connection 
 
End Sub