Thanks Isaac for your interest. The following is the entire code.
Code:
Private Sub cmdLink_Click()
Dim dbs As DAO.Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim strPath As String
Dim intCounter As Integer
Dim obj As AccessObject
Dim strfrm As String
Dim strPwd As String
strfrm = "frmParameterFileLocation"
strPwd = "1234"
DoCmd.OpenForm strfrm, , , , , acDialog
strPath = [Forms]![frmParameterFileLocation]![txtPath] 'select the location of the data file
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs
Call SetDBPassword(strPath & stDataFile, strPwd, "")
intCounter = 0
'Loop through the tables collection
For Each Tdf In Tdfs
If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table _
& will only include Linked tables
Tdf.Connect = ";DATABASE=" & strPath & stDataFile 'Set the new source
Tdf.RefreshLink 'Refresh the link
intCounter = intCounter + 1
End If
Next 'Goto next table
MsgBox intCounter & " tables have been linked from " & strPath & stDataFile, vbInformation, stTitle
DoCmd.Close acForm, strfrm
Call SetDBPassword(strPath & stDataFile, "", strPwd)
End Sub
Please note that:
- frmParameterFileLocation is a form specifically for the user to enter the path where the backend is stored
- stDataFile is a Public constant, hard coded in a module, and stands for the name of the backend datafile
- I have tested all variables and they pick up the correct values / data
- I have tested the line SetDBPassword(strPath & stDataFile, strPwd, "") and it successfully removes the password from the backend so that the linking can take place
- I have tested the line SetDBPassword(strPath & stDataFile, "", strPwd) and it successfully replaces the password to the backend. I have experimented with using a different password in this second line and checked that the backend subsequently used the alternative password. I have also run the code without this line and the backend subsequently can be opened without a password.
- I have moved the backend to different location and then noted that after running this, the tables were listed as being linked to the backend in the new location (ie, I am confident the linking part works). I have this code working in several other applications which do not have password-protected backends.
- searching on Error 3031 suggests the password needs to be shorter than 14 characters, which is irrelevant here.
The problem just seems that after running this code, the front-end still requires a password for the backend and I do not know how to supply it.
Any suggestions would be most appreciated as this is very frustrating. Thanks.
Sorry, I forgot to add the code for the Function SetDBPassword, it is:
Code:
Function SetDBPassword(strDBPath As String, _
strOldPwd As String, _
strNewPwd As String)
' This procedure sets a new password or changes an existing
' password.
Dim dbsDB As DAO.Database
Dim strOpenPwd As String
' Create connection string by using current password.
strOpenPwd = ";pwd=" & strOldPwd
' Open database for exclusive access by using current password. To get
' exclusive access, you must set the Options argument to True.
Set dbsDB = OpenDatabase(Name:=strDBPath, _
Options:=True, _
ReadOnly:=False, _
Connect:=strOpenPwd)
' Set or change password.
With dbsDB
.NewPassword strOldPwd, strNewPwd
.Close
End With
Set dbsDB = Nothing
End Function