Mystery of the Ancients

Today I had to unlock a 20-year-old, password-protected .mdb file.  It was a bit of an adventure.

Ancient MDB Files

When I first tried opening it with my 64-bit version of Access 365 (version 2108), I received the following error message:

The newest versions of Microsoft Access will open .mdb files created with the Access 2000 or later .mdb format.  This leads me to believe that the particular file I was dealing with was created with Access 97 or earlier.

Database Password Recovery

Luckily, I still had a virtual machine with a copy of Access 2007.  I fired that up and tried to open the database.  This is when I realized that the .mdb file had been password-protected:

The password for this database had been lost to the sands of time.  Early versions of Access security were notoriously weak, though.  Recovering the password was stupid easy using the Access Passview utility from NirSoft.  

Note: You should be crazy paranoid about using random password-cracking utilities, as they are more often than not thinly disguised malware.  That said, I have used NirSoft utilities for 10+ years without ever having any problems.

Using the Password to Open the MDB

After retrieving the password, I was able to open the mdb file.  Upon entering the password, I received the "Database Enhancement" dialog:

This database was created using a previous version of Microsoft Office Access.

In order to enable recent enhancements and provide you with the best experience possible, Access will upgrade your database. Once completed, you will not be able to open this database using earlier versions of Access.

Proceed with enhancement? (Recommended)

   If you choose No (Not recommended):
   • The database will not be upgraded and some enhancements will be unavailable.
   • You can open the database, view objects and modify records using this release of Access, but design changes will be disabled.
   • To make design changes you must use an earlier version of Access that supports the old database format.

If I clicked [Yes] (as recommended), I got the warning below:

You are trying to convert a password protected database. Remove the password, and then try again.

To protect the converted database, use the Encrypt with Password command.

When I clicked [OK] on that warning, the database failed to open and I was taken to the Getting Started screen.

Skipping the Version Upgrade (Temporarily)

I opened the database file again.  I entered the password when prompted.  However, this time when the Database Enhancement window appeared, I clicked [No] to skip the database upgrade.

The database opened this time.  Sure enough, this .MDB file was saved in the "Access 97 file format":

I was able to view the data in the tables.  I was not able to "Remove the password," as the most recent message box suggested I would be able to.

I found the command that I thought should do it in the Database Tools ribbon tab: "Unset Database Password."  Unfortunately, the button was grayed out:

I read somewhere online that it might help to open the file in exclusive mode.  That made no difference for me.

I had come so far.  I was able to read the data in the tables.  Worst-case scenario, I would be able to import the data into a new, unprotected MDB.  But that still seemed like more work than it needed to be.

There had to be another way to "unset the database password."

Stripping the Password from the MDB

A bit more Googling led me to a question on stackoverflow that gave me the solution I needed: use ADO to explicitly set the database password to Null.

I rewrote the code so it could run from a standard module.  I also used late binding so you do not need to set a reference to the ADO library:

Sub RemovePassword(fpDatabase As String, DbPwd As String)
    'Inspired by: https://stackoverflow.com/a/5597523/154439
    Const adModeShareExclusive As Long = 12
    
    Dim Conn As Object ' New ADODB.Connection
    Set Conn = CreateObject("ADODB.Connection")
    With Conn
        .Mode = adModeShareExclusive
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Jet OLEDB:Database Password") = DbPwd
        .Open "Data Source= " & fpDatabase & ";"
        .Execute "ALTER DATABASE PASSWORD NULL [" & DbPwd & "];"
    End With
    Conn.Close
    Set Conn = Nothing
    
End Sub

Note that the RemovePassword function needs to live in a different database than the one you are trying to unlock.  To use the function, simply pass it the full path to your locked .mdb file and the password used to lock the file.  You can run this from the Immediate window, like so:

Upgrade the Unlocked MDB to ACCDB

After you've removed the password, you can open your previously password-protected database file.  Once again, Access will show you the Database Enhancement dialog.  Now, though, when you click [Yes], you will be prompted to provide a destination .accdb filename.  Do that and your work here is done:

External references

Access PassView v1.12

NirSoft

unable to remove password from Microsoft Access 2007 (accdb)

I have a Microsoft Access 2007 (accdb) file. I set a password on it. When I open it up now, I am prompted for a password. I enter the correct password and I can gain access to it. However, I wa...

Stack Overflowpaul

Image by Dick Hoskins from Pixabay