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

Connecting to SQL Server using SQL Server Authenitcation

P: 6
hi. I am creating a database using ms access as front end and MS SQL as my back end. I've already created an odbc and was able to access it. The problem is, every time MS Access is restarted, I have to enter the username and password first before I can connect to the server. Is there any way in which I can put this in code? the error says:

Connection failed:
SQL State: '28000'
SQL Server Error: 18452
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection.

Thanks a lot in advance.
Aug 10 '07 #1
Share this Question
Share on Google+
3 Replies


P: 37
hi. I am creating a database using ms access as front end and MS SQL as my back end. I've already created an odbc and was able to access it. The problem is, every time MS Access is restarted, I have to enter the username and password first before I can connect to the server. Is there any way in which I can put this in code? the error says:

Connection failed:
SQL State: '28000'
SQL Server Error: 18452
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection.

Thanks a lot in advance.
whats the code for the connection string? I would recommend using ado:

Expand|Select|Wrap|Line Numbers
  1. Dim rmcn As ADODB.Connection, rmrs As ADODB.Recordset
  2.  
  3. ' Create connection and open connection - remote
  4. Set rmcn = New ADODB.Connection
  5.  
  6. With rmcn
  7.     .Provider = "Microsoft.Access.OLEDB.10.0"
  8.     .Properties("Data Provider").Value = "SQLOLEDB"
  9.     .Properties("Data Source").Value = "servername"
  10.     .Properties("User ID").Value = "User"
  11.     .Properties("Password").Value = "pass"
  12.     .Open
  13. End With
  14.  
  15. ' Open recordset and run sql
  16. Set rmrs = New ADODB.Recordset
  17.  
  18. sql = "query"
  19.  
  20. With rmrs
  21.     Set .ActiveConnection = rmcn
  22.     .Source = sql
  23.     .LockType = adLockOptimistic
  24.     .CursorType = adOpenKeyset
  25.     .Open
  26.  
  27. End With
  28.  
Aug 10 '07 #2

P: 6
whats the code for the connection string? I would recommend using ado:

Expand|Select|Wrap|Line Numbers
  1. Dim rmcn As ADODB.Connection, rmrs As ADODB.Recordset
  2.  
  3. ' Create connection and open connection - remote
  4. Set rmcn = New ADODB.Connection
  5.  
  6. With rmcn
  7.     .Provider = "Microsoft.Access.OLEDB.10.0"
  8.     .Properties("Data Provider").Value = "SQLOLEDB"
  9.     .Properties("Data Source").Value = "servername"
  10.     .Properties("User ID").Value = "User"
  11.     .Properties("Password").Value = "pass"
  12.     .Open
  13. End With
  14.  
  15. ' Open recordset and run sql
  16. Set rmrs = New ADODB.Recordset
  17.  
  18. sql = "query"
  19.  
  20. With rmrs
  21.     Set .ActiveConnection = rmcn
  22.     .Source = sql
  23.     .LockType = adLockOptimistic
  24.     .CursorType = adOpenKeyset
  25.     .Open
  26.  
  27. End With
  28.  
Thanks lot. :)
That would do for now.
Thanks again :)
Aug 13 '07 #3

P: 37
if you have the SQLNative drivers installed on all computers, use:

.Properties("Data Provider").Value = "SQLNCLI"

MUCH QUICKER!
Aug 13 '07 #4

Post your reply

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