Connecting to SQL Server using SQL Server Authenitcation

[jutz]

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.

[matthooper]

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:

Dim rmcn As ADODB.Connection, rmrs As ADODB.Recordset

' Create connection and open connection - remote
Set rmcn = New ADODB.Connection

With rmcn
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider").Value = "SQLOLEDB"
    .Properties("Data Source").Value = "servername"
    .Properties("User ID").Value = "User"
    .Properties("Password").Value = "pass"
    .Open
End With

' Open recordset and run sql
Set rmrs = New ADODB.Recordset

sql = "query"

With rmrs
    Set .ActiveConnection = rmcn
    .Source = sql
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
    
End With

[jutz]

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

Dim rmcn As ADODB.Connection, rmrs As ADODB.Recordset

' Create connection and open connection - remote
Set rmcn = New ADODB.Connection

With rmcn
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider").Value = "SQLOLEDB"
    .Properties("Data Source").Value = "servername"
    .Properties("User ID").Value = "User"
    .Properties("Password").Value = "pass"
    .Open
End With

' Open recordset and run sql
Set rmrs = New ADODB.Recordset

sql = "query"

With rmrs
    Set .ActiveConnection = rmcn
    .Source = sql
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
    
End With

Thanks lot. :)
That would do for now.
Thanks again :)

[matthooper]

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

.Properties("Da ta Provider").Valu e = "SQLNCLI"

MUCH QUICKER!