Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Using MS-Access 2019 as front end, with databases and tables linked to SQL Express (and using SSMS 18). When I link tables using SQL Authentication, from a remote computer, it asks for the login password the first time, which is ok. However, each time I close and then reopen the Access engine, it asks for the passwords again. Since I have 15 tables linked to the Access engine, it doesn't make sense to have to re-enter the passwords again and again..

What I have tried:

I already googled that, but to no avail.
Posted
Updated 24-Nov-22 21:22pm
v2
Comments
Member 15627495 20-Nov-22 9:40am    
do you put in your 'connection string' the password required ?
Jean-Claude Elias 21-Nov-22 1:01am    
Yes I do, but it still asks for it when I run the code. Once password is entered, manually, the connection works alright. Is there any syntax or structure error in my connecting string? Here is the entire code.

=======================================================================
Dim strConnectionString as string
Dim strDBName as string
Dim strMyUsername as string
Dim strMyPassword as string
Dim strTblNameInSQLServer as string
Dim strTblNameInAccess as string

strDBName = “AllMembers”
strMyUsername = “claude”
strMyPassword = “123” ' just for testing purpose
strTblNameInSQLServer= “tblMembers”
strTblNameInAccess = “dbo_tblMembers”

strConnectionString = "ODBC;DRIVER=SQL Server Native Client 11.0; " & _
"SERVER=JCRE\SQLEXPRESS;DATABASE=" & strDBName & ";User Id=" & strMyUsername & ";Password=" & strMyPassword

DoCmd.TransferDatabase acLink, "ODBC Database", _
strConnectionString, acTable, strTblNameInSQLServer, strTblNameInAccess

Member 15627495 21-Nov-22 2:54am    
https://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/
Jean-Claude Elias 21-Nov-22 5:02am    
Thank you very, very much. The URL/webpage you provided helped me correct the syntax of my connection string. Now my code works as it should, without prompting me for the password. Thanks again - much appreciated.

I know this thread is resolved, but this information may help anyone else attempting a similar set up.
I'm linking to a SQL Server database from Access 365 and found this article Create DSN-less connection to SQL for linked table - Office | Microsoft Learn[^]
I've reproduced my interpretation of that code here
VB
Public Sub Reconnect()
    Dim errs As Integer: errs = 0
    ReDim errTables(errs) As String
    
    Dim tables(4) As String
    tables(0) = "table1"
    tables(1) = "table2"
    tables(2) = "table3"
    tables(3) = "table4"
    
    Dim i As Integer
    For i = 0 To UBound(tables)
        Debug.Print "Linking " & tables(i)
        If Not AttachDSNLessTable(removeSchemaFromTable(tables(i)), tables(i), dbServer, dbName, dbLogin, dbPass) Then
            logTableInError errs, errTables(), tables(i)
        End If
    Next

    If errs > 0 Then
        Dim s As String
        s = ""
        For i = 0 To UBound(errTables)
            If Len(errTables(i)) > 0 Then
                s = s & errTables(i) & vbCrLf
            End If
        Next
        If Len(LTrim$(RTrim$(s))) > 0 Then
            MsgBox "Unable to link the following tables: " & vbCrLf & s
        End If
    Else
        MsgBox "All tables relinked"
    End If

End Sub
Private Function removeSchemaFromTable(tablename As String) As String
    Dim i As Integer
    i = InStr(tablename, ".")
    If i = 0 Then
        removeSchemaFromTable = tablename
    Else
        removeSchemaFromTable = Mid$(tablename, i + 1)
    End If
End Function
Private Sub logTableInError(ByRef errs As Integer, ByRef errTables() As String, ByVal tablename As String)
    ReDim Preserve errTables(errs)
    errTables(errs) = tablename
    errs = errs + 1
End Sub

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
'// https://support.microsoft.com/en-gb/help/892490/how-to-create-a-dsn-less-connection-to-sql-server-for-linked-tables-in
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    'MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function
Public Function KeepConn()
'By having a form bound to a table we should be able to keep the connection open preventing timeouts
'But we don't want to see the form! Call this from autoexec
    On Error GoTo 0
    Form_KeepConnectionOpen.Visible = False
End Function
The variables dbServer, dbName, and dbLogin are stored as Consts at the head of the module and dbPass is stored on a table with a Password mask on it's only field. Not completely secure but better than nothing. The VBA project is also locked for viewing.

One interesting feature is the form that is kept open in the background, not visible, to stop the connections from timing out.
 
Share this answer
 
Thank you CHill60, very interesting and useful. Always willing to try improved solutions. Will most likely adopt this one.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900