Linking to a password-proteced backend

[ascii]

Hi All
I am trying to use VBA code in a database developed in Access 2007, to link to a password-protected backend which is an old mdb file (for users with a runtime version of Access). The code I am using works fine if there is no password. The essential code is this:

    Call SetDBPassword(strPath & stDataFile, strPwd, "")
        For Each Tdf In Tdfs
           Tdf.Connect = ";DATABASE=" & strPath & stDataFile            'Set the new source
           Tdf.RefreshLink 'Refresh the link
 
        End If
    Next 'Goto next table
    Call SetDBPassword(strPath & stDataFile, "", strPwd)

It works in that the tables get linked, but when I try to open a form or do anything based on the underlying tables, I
get the message: "[QUOTE]Not a valid password[/QUOTE]" error 3031, with no opportunity to enter a password.


I have looked at the backend after this process and it opens with the password
just as before. I have tested the linking code to change the password of the
backend and this too can be done. I've searched on error 3031 but it mainly suggests the password shouldn't be longer than 14 characters, which it isn't. Does anyone have any ideas, please?
Thanks very much.

[ipisors]

Please post code for
SetDBPassword

[ascii]

Thanks Isaac for your interest. The following is the entire 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:

1. frmParameterFileLocation is a form specifically for the user to enter the path where the backend is stored
2. stDataFile is a Public constant, hard coded in a module, and stands for the name of the backend datafile
3. I have tested all variables and they pick up the correct values / data
4. 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
5. 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.
6. 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.
7. 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:

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

[ipisors]

The code I really wanted to see was the one for SetDBPassword

[ascii]

Thanks Isaac, I realised this and edited the previous post to include it - then I saw your reply.

[ipisors]

OK ... after researching this a while, I simply don't believe you can do this. I mean, the following concept:

1. remove password to back end
2. link FE to BE table links
3. put password back on back end

.... And expect the table links to work.

Microsoft suggests that when you encrypt a back end of a split database, the links will need the password. I think the answer must be in the linking of the tables themselves. (meaning there really is no value in removing and resetting the BE password to begin wtih - just leave it on and specify it in the Tabledef's .Connect property).

If you manually link one table to a password-protected back end, then check out its .Connect property using the Immediate window, you will get the correct connection string to use in your code including the password I believe. You can then use your .Connect code accordingly.

(Yes, that will probably expose the password in the connect string, but you've probably already accepted that level of risk by wanting to have a front end whose table links would link to a PW protected BE, yet not ask for the password when opened). I'm assuming you at least have shift bypass key disabled on your FE.

[ascii]

Thank you!! This worked perfectly.

Following your suggestion I have

1. deleted the lines to remove and reset the password and
2. replaced the tdf.connect line with:

Tdf.Connect = "MS Access;PWD=" & strPwd & ";DATABASE=" & strPath & stDataFile

It now works perfectly. I really appreciate your help.

Now all I need to do is find out how to mark this thread as solved.

[ipisors]

I'm very glad to hear it worked. Like I mentioned, just remember that the password is probably exposed in the tabledef's .connect property, so that means a person with a certain level of access development knowledge would be able to get it without necessarily even "hacking" into it, i.e. without hacking a password to get to that point, just by using VBA code to enable shift-bypass, open the database remotely, and go through the tabledefs collection, checking out tables' .connect properties.

As far as Solved .... Well, coming from UtterAccess, I've developed a strong preference for having no-such-thing as 'Solved' - i.e., anyone who wants to add any thoughts to this thread is welcome to for the betterment of everyone's reading, even if you do feel it is solved. Just my opinion.

Glad it's working - have a great week.

Isaac

[ascii]

Thanks Isaac, I agree it is great to be able to share with others.

The users of this database don't have Access on their PCs, just a run-time version, hence the necessity to link using code. I will make an accde file and rename it accdr to put on their local PCs. This should deliver a reasonable level of security. Thanks again