Link to home
Start Free TrialLog in
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

Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

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

See also:
https://www.experts-exchange.com/questions/24078886/ODBC-Connections.html

/gustav
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
Avatar of 1104d

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 1104d

ASKER

Many thanks Gustav, that worked perfectly!  

(I also have a related question, but will post that separately in a moment.)
You are welcome!

/gustav
"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