Avatar of 1104d
1104dFlag for Australia asked on

Supply password when linking Access tables programmatically with VBA

I have some code I have used successfully for some years to link back-end tables programmatically to front end tables in an Access 2003 application.  The code is shown below.

The back end database now needs to be password protected.  How do I add a password parameter into the code below or tell the application the password?  I do not wish to start again with this code, just to add the appropriate parameter or code into the existing subroutine.

Thank you.
Private Sub b_ReattachTables_Click()
On Error GoTo Err_b_ReattachTables_Click
 
Dim strPrgFilePath As String
Dim strPrgFileName As String
Dim strPrgFileFolder As String
strPrgFilePath = Application.CurrentDb.Name
strPrgFileName = Dir(strPrgFilePath)
strPrgFileFolder = Left(strPrgFilePath, Len(strPrgFilePath) - Len(strPrgFileName))
 
Dim tdf As DAO.TableDef
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) & "BACKEND.mdb" 
    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 No 3."
    Resume Exit_b_ReattachTables_Click
    
End Sub

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

8/22/2022 - Mon
Gustav Brock

Delete the linked tables and relink them having the mark set for "Save password".

See also:
https://www.experts-exchange.com/Database/MySQL/Q_24078886.html

/gustav
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

It's late, but I found this - see if you can figure it out from this:

    Dim sDbName As String
    Dim sPwdOld As String
    Dim sPwdNew As String
    Dim db As DAO.Database

    sDbName = "C:\DataP3WithPwd.mdb"
    sPwdOld = InputBox("Old Pwd: ")
    sPwdNew = InputBox("New Pwd:")
    'sPwdOld = "***"
    'sPwdNew = "jam"
   
    Set db = DBEngine.Workspaces(0).OpenDatabase(sDbName, options:=True, readonly:=False, Connect:=";PWD=" & sPwdOld & ";")
   
    db.NewPassword sPwdOld, sPwdNew
ASKER
1104d

cactus_data and Database MX
Thanks for replying, but I am not asking how to do it manually, nor by a different solution.  Also, the DatabaseMX solution refers to changing a password, not to supplying a constant password programmatically.
My specific question is how to insert the password into the code that I provided; I believe it likely to be in the nature of a parameter added to the Connect Property.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
1104d

Many thanks Gustav, that worked perfectly!  

(I also have a related question, but will post that separately in a moment.)
Gustav Brock

You are welcome!

/gustav
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"I believe it likely to be in the nature of a parameter added to the Connect Property. "
That's what I was trying to show late last night, but I couldn't get the right syntax ... and I was tired.  Gustav came up with it.

mx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.