Relinking tables programmatically from password-protected database

I am looking for a way to pass a password ("XXX") programmatically from a password-protected back-end database (ClientData.mdb) to the front-end database (FrontEnd.mde) when linking tables programmatically.   Here is the situation.  

1.  I have a back-end database called ClientData.mdb; the database password is XXX.  The client does not know this password.
2.  I have a front-end database called FrontEnd.mde.
3.  When the client opens the front end they are prompted to relink the tables; clicking the button runs the code shown below, which relinks all the back-end tables into the front-end database.

The code below works perfectly (I have been using it for years) so long as the back end database is not password protected.  However, as soon as I password-protect the back end, the subroutine gets only as far as   tdf.RefreshLink  before it jumps to the "Err_b_ReattachTables_Click" part of the code.

According to the Microsoft Help page on the Connect property, I ought to be able to include a password as one of the paramaters in the Connect property - so I tried this by amending the line from
    tdf.Connect = ";DATABASE=" & Trim(strPrgFileFolder) & strPrgFileName
to
    tdf.Connect = ";DATABASE=" & Trim(strPrgFileFolder) & strPrgFileName & "PWD=XXX;"

However, the code still skips to the tdf.RefreshLink line, and then jumps to error code.

Note that if I did not put the "PWD=XXX;" in quotes the code turned red and kept giving me debug errors.

What am I doing wrong?  Is it just a problem of the syntax in the tdf.Connect line, or something else?

Please note that I am not a programmer by profession, so the answer needs to be specified in clear and simple terms!Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

Private Sub b_ReattachTables_Click()
On Error GoTo Err_b_ReattachTables_Click
 
Dim strPrgFilePath As String
Dim strPrgFileName As String
Dim strPrgFileFolder As String
Dim strPrgFileName As String
Dim tdf As DAO.TableDef
 
strPrgFilePath = Application.CurrentDb.Name
strPrgFileName = Dir(strPrgFilePath)
strPrgFileFolder = Left(strPrgFilePath, Len(strPrgFilePath) - Len(strPrgFileName))
strPrgFileName  = "ClientData.mdb"
 
For Each tdf In DBEngine(0)(0).TableDefs
If ((tdf.Attributes And dbSystemObject) = 0) And (tdf.Connect <> vbNullString) And Not (tdf.Name Like "~*") Then
    tdf.Connect = ";DATABASE=" & Trim(strPrgFileFolder) & strPrgFileName 
    tdf.RefreshLink
    Me!lblMsg.Caption = "Linking..." & tdf.Name
    DoEvents
End If
Next
 
Me!lblMsg.Caption = ""
MsgBox "Linked tables have been re-attached", 64, "Attach Tables"
 
Exit_b_ReattachTables_Click:
    
    Exit Sub
 
Err_b_ReattachTables_Click:
 
    MsgBox "Please add back-end files to the current directory!", vbCritical, "Error Message No 1."
    Resume Exit_b_ReattachTables_Click
    
End Sub