[AccessD] Changing DSN Connection strings

[Gustav Brock]

Hi David

We use DSN-less connections, which I've found are easier to control:

<code>
' Top level call example:
IsAttached = AttachSqlServer(Me!Hostname.Value, Me!Database.Value, Me!Username.Value, Me!Password.Value)


Public Function AttachSqlServer( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal Password As String) _
    As Boolean

' Attach all tables linked via ODBC to SQL Server or Azure SQL.
' 2016-04-24. Cactus Data ApS, CPH.

    Const cstrDbType    As String = "ODBC"
    Const cstrAcPrefix  As String = "dbo_"

    Dim dbs             As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim qdf             As DAO.QueryDef
    
    Dim strConnect      As String
    Dim strName         As String
    
    On Error GoTo Err_AttachSqlServer
    
    Set dbs = CurrentDb
    strConnect = ConnectionString(Hostname, Database, Username, Password)
    
    For Each tdf In dbs.TableDefs
        strName = tdf.Name
        If Asc(strName) <> Asc("~") Then
            If InStr(tdf.Connect, cstrDbType) = 1 Then
                If Left(strName, Len(cstrAcPrefix)) = cstrAcPrefix Then
                    tdf.Name = Mid(strName, Len(cstrAcPrefix) + 1)
                End If
                tdf.Connect = strConnect
                tdf.RefreshLink
                Debug.Print Timer, tdf.Name, tdf.SourceTableName, tdf.Connect
                DoEvents
            End If
        End If
    Next
    
    For Each qdf In dbs.QueryDefs
        If qdf.Connect <> "" Then
            Debug.Print Timer, qdf.Name, qdf.Type, qdf.Connect
            qdf.Connect = strConnect
        End If
    Next
    Debug.Print "Done!"
    
    AttachSqlServer = True
    
Exit_AttachSqlServer:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Function
    
Err_AttachSqlServer:
    Call ErrorMox
    Resume Exit_AttachSqlServer
    
End Function


Public Function ConnectionString( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal Password As String, _
    Optional ByVal AdoStyle As Boolean) _
    As String

' Create ODBC or ADO connection string from its variable elements.
' 2021-06-15. Cactus Data ApS, CPH.

    Const AzureDomain   As String = ".windows.net"
    Const OdbcPrefix    As String = "ODBC;"
    Const OdbcConnect   As String = _
        "DRIVER=SQL Server Native Client 17.0;" & _
        "Description=Your Application Name;" & _
        "APP=Microsoft® Access;" & _
        "SERVER={0};" & _
        "DATABASE={1};" & _
        "UID={2};" & _
        "PWD={3};" & _
        "Trusted_Connection={4};"
        
    Dim FullConnect     As String
    
    If Right(Hostname, Len(AzureDomain)) = AzureDomain Then
        ' Azure SQL connection.
        ' Append servername to username.
        Username = Username & "@" & Split(Hostname)(0)
    End If
    If Not AdoStyle Then
        FullConnect = OdbcPrefix
    End If
    FullConnect = FullConnect & OdbcConnect
    FullConnect = Replace(FullConnect, "{0}", Hostname)
    FullConnect = Replace(FullConnect, "{1}", Database)
    FullConnect = Replace(FullConnect, "{2}", Username)
    FullConnect = Replace(FullConnect, "{3}", Password)
    FullConnect = Replace(FullConnect, "{4}", IIf(Username & Password = "", "Yes", "No"))
    
    ConnectionString = FullConnect

End Function
</code>

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På vegne af David Emerson
Sendt: 8. december 2021 01:09
Til: AccessD <accessd at databaseadvisors.com>
Emne: [AccessD] Changing DSN Connection strings

Hi Listers,

I have inherited a data base which has code which updates the connection string of the tables between two different databases.  The user selects which database to link to and the code updates the table's connect property:

tdf.Connect = strConnect_NEW

strConnect_NEW is in this format (with the actual password in place of
{Pwrd}):

ODBC:DSN= StockListSQL_Data_Archive;Description=
StockListSQL_Data_Archive;APP=Microsoft
Office;DATABASE=SL_DataSQL;User_Id=SQL_User;Password={Pwrd]

The only difference between the two connection strings in the name of the Database

<snip>
The Database could initially be connected to either of the SQL databases.
The first time the database connection is changed with this code:

                    tdf.Connect = strConnect_NEW
                    'Refresh the table link.
                    tdf.RefreshLink                     'Refresh the Link.

an SQL Server Login dialog box opens showing the Data Source, and Login ID, and asking for the password.  Once the Password is entered then the user can switch between the database tables without having to reenter the password.
However, if the Access program is closed and reopened then the first time the database connection is changed the password is requested again.

Why is the Access program asking for the password when the password is entered in the Connection string?  Is there some other connection that needs the password saved in it?

When the DSN is set up for each user the "Connect to SQL Server to obtain default settings for the additional configuration options" box is ticked and the Login ID and Password is entered.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand