Results 1 to 9 of 9
  1. #1
    ascii is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    5

    Question Linking to a password-proteced backend

    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:
    Code:
        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.

  2. #2
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    Please post code for
    SetDBPassword

  3. #3
    ascii is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    5
    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:
    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:
    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
    Last edited by ascii; 03-17-2014 at 02:54 PM. Reason: adding more information

  4. #4
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    The code I really wanted to see was the one for SetDBPassword

  5. #5
    ascii is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    5
    Thanks Isaac, I realised this and edited the previous post to include it - then I saw your reply.

  6. #6
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    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.

  7. #7
    ascii is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    5

    Smile

    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:

    Code:
    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.

  8. #8
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    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

  9. #9
    ascii is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    5
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Password Protecting the backend database
    By data808 in forum Security
    Replies: 7
    Last Post: 02-24-2014, 10:45 AM
  2. Error linking to an access backend on a different computer
    By amd711 in forum Import/Export Data
    Replies: 6
    Last Post: 02-25-2013, 09:48 AM
  3. linking table in a password protected database
    By bdaniel in forum Programming
    Replies: 1
    Last Post: 01-04-2012, 01:25 AM
  4. Replies: 1
    Last Post: 01-01-2012, 12:08 AM
  5. Replies: 3
    Last Post: 11-10-2011, 03:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums