By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,542 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Connecting to SQL Server using domain account

P: 2
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.Connection

With lobjCn
.ConnectionString = "Provider=sqloledb;Server=LDNPCM05421V05A,10501;Da tabase=TMS;uid=INTRANET\sysemtmslive;pwd=test123;T rusted_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
Oct 18 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command0_Click()
  3. Dim lobjCn As New ADODB.Connection
  4. lobjCn.Open = "Provider=sqloledb;Server=LDNPCM05421V05A,10501;Database=TMS;" & _ "uid=INTRANET\sysemtmslive;pwd=test123;Trusted_Connection=no"
  5.  
  6. End Sub
  7.  
  8.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub CompleteDatabaseExample() 
  3.     Dim dbMain as New ADODB.Connection' Declaring the Connection 
  4.     Dim rsCustomer as New ADODB.Recordset' Declaring the Recordset 
  5.     Dim SQL as String' Declaring a simple variable 
  6.  
  7.     dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ ' Connecting to the Database 
  8.         "Persist Security Info=False;" & _ 
  9.         "Data Source=C:\WINDOWS\Desktop\training.mdb" 
  10.  
  11.     SQL = "SELECT * FROM Customer"' Setting up our SQL Command statement 
  12.     rsCustomer.Open SQL, dbMain, adOpenDynamic, adLockOptimistic' Opening the Recordset (see Recordset Options) 
  13.     Do While Not rsCustomer.EOF' Loop until we reach the End-Of-File marker 
  14.         MsgBox "The Customer's Name is: " & rsCustomer("Name") 
  15.         MsgBox "The Customer's City is: " & rsCustomer("City") 
  16.         rsCustomer("Country") = "United States" 
  17.         rsCustomer("Name") = rsCustomer("Name") & " Jr." 
  18.         rsCustomer.Update' Updating the Records (see also Recordset (Working With)) 
  19.         rsCustomer.MoveNext' Moving to the next record (necessary to reach the EOF) 
  20.     Loop 
  21.     rsCustomer.Close' Closing the recordset 
  22.     dbMain.Close' Closing the database connection 
  23.  
  24. End Sub
  25.  
  26.  
Oct 18 '06 #2

P: 2
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command0_Click()
  3. Dim lobjCn As New ADODB.Connection
  4. lobjCn.Open = "Provider=sqloledb;Server=LDNPCM05421V05A,10501;Database=TMS;" & _ "uid=INTRANET\sysemtmslive;pwd=test123;Trusted_Connection=no"
  5.  
  6. End Sub
  7.  
  8.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub CompleteDatabaseExample() 
  3.     Dim dbMain as New ADODB.Connection' Declaring the Connection 
  4.     Dim rsCustomer as New ADODB.Recordset' Declaring the Recordset 
  5.     Dim SQL as String' Declaring a simple variable 
  6.  
  7.     dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ ' Connecting to the Database 
  8.         "Persist Security Info=False;" & _ 
  9.         "Data Source=C:\WINDOWS\Desktop\training.mdb" 
  10.  
  11.     SQL = "SELECT * FROM Customer"' Setting up our SQL Command statement 
  12.     rsCustomer.Open SQL, dbMain, adOpenDynamic, adLockOptimistic' Opening the Recordset (see Recordset Options) 
  13.     Do While Not rsCustomer.EOF' Loop until we reach the End-Of-File marker 
  14.         MsgBox "The Customer's Name is: " & rsCustomer("Name") 
  15.         MsgBox "The Customer's City is: " & rsCustomer("City") 
  16.         rsCustomer("Country") = "United States" 
  17.         rsCustomer("Name") = rsCustomer("Name") & " Jr." 
  18.         rsCustomer.Update' Updating the Records (see also Recordset (Working With)) 
  19.         rsCustomer.MoveNext' Moving to the next record (necessary to reach the EOF) 
  20.     Loop 
  21.     rsCustomer.Close' Closing the recordset 
  22.     dbMain.Close' Closing the database connection 
  23.  
  24. End Sub
  25.  
  26.  
Oct 18 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.