I am trying to use an unbound combobox with afterupdate event to connect the front end to either a production or dev backend. I cannot seem to get this to work when the backend dbs are password protected. I have done lots of searcing and found various forum posts but none resolve for me.
Could you have a look at this current code and see if any syntax issues pop out to you?
The error i am getting is "Not a valid password" Error 3031.
The password contains special chars, numbers, UC/LC letters. The passwords are valid when opening the front and back end natively. I have tried hardcoding the passwords into the code just to test if the issue is inputbox but same error. I have also tried using password variable as variant as well as string. Same error.
Code:
Private Sub cmbBackendSelection_AfterUpdate() Dim selectedOption As String
selectedOption = Me.cmbBackendSelection.value
Dim backendFilePath As String
Dim backendPassword As String
Select Case selectedOption
Case "Production"
backendFilePath = "C:\Users\Documents\MICROSOFT ACCESS DBs\WM App\01_LIVE\WM_PRD_BACK.accdb"
Case "Development"
backendFilePath = "C:\Users\Documents\MICROSOFT ACCESS DBs\WM App\02_DEV\WM_DEV_BACK.accdb"
End Select
' Prompt the user to enter the password for the backend
backendPassword = InputBox("Enter the password for the backend database:", "Password Required")
If Len(backendPassword) = 0 Then
' The user clicked Cancel or entered an empty password, exit the sub
Exit Sub
End If
' Update the links for all tables
Dim db As DAO.Database
Set db = CurrentDb
Dim tdf As DAO.TableDef
For Each tdf In db.TableDefs
' Check if the table is a linked table
If tdf.Attributes And dbAttachedODBC Or tdf.Attributes And dbAttachedTable Then
' Update the link to the backend database with the password
tdf.Connect = ";DATABASE=" & backendFilePath & ";PWD=" & backendPassword
tdf.RefreshLink
End If
Next tdf
Set db = Nothing
End Sub