MS Access Can't Link to Encrypted Backend Data

Question

I've got a quandry. I've developed an Access app and I'm getting ready to distribute it. I've just split the database. (I know, some say I should've developed it split from the start... I didn't) I've also just encrypted the backend database. In the frontend, I've linked to the backend and entered the correct password when prompted. The linked tables are now appearing in my fronend database. However, when I try to access one of the linked tables, I get a pop-up message that simply says "Not a valid password".

I've tried deleting the linked tables and relinking. I've tried updating the link. Nothing seems to work. Every search I've done assumes the links were created BEFORE the encryption happened and no password was entered. This is not the case here.

Can anyone please help?

Windows 7 - Access 2010

Multiguy

Answer 1

I came across this thread when I was having a similar problem with Access 2013. I'd encrypted the backend successfully. Each time I opened the backend, I could successfully open the backend using the password I'd used to encrypt the backend.

So, I opened the frontend, deleted the previously linked tables, and re-linked to the newly encrypted backend. As expected, during the link process, it asked me for the password to the backend. I entered the correct password (I know it was correct, because I pasted it instead of typing it) and everything seemed to work correctly. The tables all showed up in the objects list in the frontend. However, if I tried to open a linked table, I would get a message box stating "Not a valid password."

After visiting some other threads, I tried using a password on the backend that did not have any special characters and did not have any spaces. Then relinking those in the frontend. Presto! That solved the problem.

So, try the following if you are getting the "Not a valid password" in the frontend even if you can open the backend just fine if you manually load it yourself: Eliminate any spaces or punctuation from the backend password and then delete and re-link the linked tables in the frontend.

Answer 2

From comments

OOOOOPPPPPSSSS!!!! Ok, I found the problem. Access doesn't like the use of other characters. I had a set of parenthesis in my password. Removed that and all is well! :-)

– MultiGuy

Answer 3

I'd like to add to this; a password for a back end table connection can be updated in a VBA macro. Add the following lines and replace the name for the linked table & the password.

 Dim cdb As DAO.Database
 Set cdb = CurrentDb
 cdb.TableDefs("Projects").Connect = "MS Access;PWD=PaSsWoRd;DATABASE=C:\Users\bob.smith\Desktop\Split\Database_NEW_be.accdb"
 cdb.TableDefs("Projects").RefreshLink

I found this was useful after splitting a database into a front end and back end using the built in split functionality. I needed to encrypt the back end for security purposes and didn't want to have to recreate all the data connections - isn't that the point of using the split function?

Duplicate the above lines for each linked table and let the macro run through. The linked tables should resume working after this.

---

This answer solved an issue for me. So I am upvoting, and also I want to provide an enhanced version that you can run on all tables:

Public Sub RevisePasswordForLink()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Dim tdf As TableDef, colTdf As TableDefs, strConnect As String
    Set colTdf = cdb.TableDefs

    strConnect = "MS Access;PWD=paSsWoRd;" _
             "DATABASE=C:\Users\bob.smith\Desktop\Split\Database_NEW_be.accdb"

    For Each tdf In cdb.TableDefs

      ''I believe best to skip the hidden tables ("MSys*")
      If Not tdf.Name Like "MSys*" Then

          ''If your DB has any local tables, you can save yourself some errors
          ''by filtering them out (similar to hidden tables).

          cdb.TableDefs(tdf.Name).Connect = strConnect
          cdb.TableDefs(tdf.Name).RefreshLink

          Debug.Print "  " & tdf.Name

      End If
    Next tdf
    Set cdb = Nothing
    Debug.Print "FINISHED "
End Sub