Connection String mysterious Trim

[Alibaba]

The function below is written using VBA
The purpose of the function is to open an external database
see the line Set dB = OpenDatabase
Then it changes its connection string for the tables and queries in that database
the connection string is dsn-less string; see the line
strNewConnectString = "ODBC;Driver={SQL Server};Server=IPAddress;DATABASE=dBName;UID=dBSa;PWD=dBPassword;APP=Microsoft Office;"

I have validated the connection string by using the linked table manager and it is working fine

However when I run the code, the connection string is trimmed to:
Driver={SQL Server};Server=IPAddress;DATABASE=dBName;APP=Microsoft Office;
notice the username and password have disappeared

This create a problem when I convert the program to accde and deploy for the users; they get the msg as in the attached file.
They are required to provide SQL login information

Any Help is appreciated?

Thanks!

Function HardCodedChangeConnectionString() As Boolean



    
    On Error GoTo Err_Change_ACCDB_External_Connection_String

    
    Dim dB As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strNewConnectString As String
  
    
    Dim errMsg As String

Dim strPassTru As String
Dim strStep As String



strStep = "1. Declarations"

    
    
  
    Set dB = OpenDatabase("D:\Administration_Developers_Folders\PDS_PROGRAMS_INSTALLATIONS\Workshop\cp2025PDS.accdb")
    
    
    
    'Debug.Print "aiwan " & strACCDBFileFullPath
    
    strStep = "2. Set Target Database"
    strNewConnectString = "ODBC;Driver={SQL Server};Server=IPAddress;DATABASE=dBName;UID=dBSa;PWD=dBPassword;APP=Microsoft Office;"  ' Example for Access database"
                        
    'Debug.Print strNewConnectString
    strStep = "3. Set New  Connection String"
    
    ' Loop through all TableDefs
    For Each tdf In dB.TableDefs
        ' Check if it's a linked table(tdf is empty)
        If tdf.Connect <> "" Then
            ' Update the connection string
            tdf.Connect = strNewConnectString
            ' Refresh the link
            tdf.RefreshLink
           ' Debug.Print "Updated linked table: " & tdf.NAME 'change this to log this in the event log
        End If
    Next tdf
    strStep = "4. loop through external linked tables"
    
    
   ' MsgBox "Linked table paths updated!", vbInformation, "Installation App Alerts!" 'change this to log this in the event log
    strStep = "4. loop through external linked tables"
    
    
    HardCodedChangeConnectionString = True
    strStep = "5. Operation is successful"
    
CloseConnections:

Exit_Change_ACCDB_External_Connection_String:

Set dB = Nothing

  
    Set appAccess = Nothing
    
    Exit Function

Err_Change_ACCDB_External_Connection_String:
' 2024.02.27 11:15:15 AM Tareq Azmi
 ' Custom Error Trapper Msg 2024.03.29 05:47 20 AM Tareq Azmi
         MsgBox "Sorry! There Is an Unexpected Issue " & Chr(10) & "Linking Tables; 365; ACCDB update Connection String " & Chr(10) & "After Step: " & strStep & Chr(10) & "Code#: " & Err.Number & Chr(10) & Err.Description & Chr(10) & "Please contact admin." & Chr(10) & "Thank You!", vbExclamation, "Installation App 365 Alerts!"
         HardCodedChangeConnectionString = False

     Resume Exit_Change_ACCDB_External_Connection_String
    
  
' connection string is being trimmed Driver={SQL Server};Server=IPAddress;DATABASE=dBName;APP=Microsoft Office;
    
    
End Function

 

[DHookom]

You may need to set an attribute to save the password. Check out Doug Steele's function for the "Addendum 1".

Addendum 1: I received e-mail about this from Bryan Beissel. Bryan indicated that he didn't want to use Trusted Connection. However, Access wouldn't save the information. Bryan came across an article that indicated that you needed to set tdfCurrent.Attributes to DB_ATTACHSAVEPWD in order to have Access save the user id and password information for each table.

That implies changing the line of code

tdfCurrent.Attributes = typNewTables(intLoop).Attributes

to
tdfCurrent.Attributes = typNewTables(intLoop).Attributes Or DB_ATTACHSAVEPWD
I haven't tried this myself (as I always use trusted connection). On first glance, it would seem to be a questionable idea, as you'd end up store the User ID and password in plain text in the Connect property. However, if that doesn't bother you, go for it!

 

[cheekybuddha]

However when I run the code, the connection string is trimmed to:
Driver={SQL Server};Server=IPAddress;DATABASE=dBName;APP=Microsoft Office;
notice the username and password have disappeared

When you query the connection string (eg Debug.Print tdf.Connect) VBA will not reveal the user and password to you, but it will have cached the values and apply them when you use the connection. This is for security, so it is more difficult for users to discover the connection credentials.

Which ODBC connector are you using?

 

[cheekybuddha]

Try again, with the following adjustment:

' ...
    strStep = "2. Set Target Database"
    strNewConnectString = "ODBC;Provider=MSDASQL;Driver={SQL Server};Server=IPAddress;DATABASE=dBName;UID=dBSa;PWD=dBPassword;APP=Microsoft Office;"  ' Example for Access database"
'                               ^^^^^^^^^^^^^^^^^    add this
' ...