Removing a password that was created in a previous version from an Access 2007 database

Earlier this week I was banging my head against the wall trying to figure out how to remove a password from an Access 2007 database (file extension .accdb). The database was created in a previous version of access and subsequently "upgraded" to the Access 2007 format. I believe some type of a wizard was used to perform the "upgrade."

The security model is evidently different in Access 2007 than in previous versions. It doesn't appear that you can set/unset database passwords. Instead, in Access 2007, there is an option to Encrypt with Password. I'm not sure if this is supposed to be logically similar to the earlier version password architecture, but there was no way that I could find to easily remove the password that had been created in the earlier version when using the Access 2007 interface. Access security has always been confusing to me, and I need to take the time someday to understand how the security model is supposed to work in Access 2007.

Another problem with the "upgrade" of the database is that, once in the .accdb format, the database would no longer accept the Compact and Repair Database command. The database was set to Compact on Close, but upon the closing of the database, it would prompt for a password, and then not accept the accurate password that was used to open the database in the first place. Even a manual Compact and Repair Database command would not work. This series of misadventures led to the attempt to simply remove the password from the database, but then I ran into the roadblock described above. There simply didn't seem to be any option anywhere to Remove the database password, even after opening the database in Exclusive mode.

I searched message boards, etc., but was not able to find any good answers. Eventually, I came across some outdated code from a previous version of Access that explained how to remove the password programmatically using ADO and the SQL Command: ALTER DATABASE PASSWORD.

I'm not sure if my solution was the most elegant or efficient, but I eventually got the password removed! I ended up setting up a shell database with a single module to execute the following code:

Public Sub ChangeDBPassword()
Dim objConn As ADODB.Connection
Dim strAlterPassword As String

On Error GoTo ChangeDBPassword_Err

' Create the SQL string to change the database password.
' Here, KLAS is the old password, and I am wanting to set the password to NULL
strAlterPassword = "ALTER DATABASE PASSWORD NULL [KLAS];"

' Open the secured database.
Set objConn = New ADODB.Connection

With objConn
.Mode = adModeShareExclusive
'.Provider = "Microsoft.Jet.OLEDB.4.0"
'I had to update the Provider information for Access 2007, as the code was quite old.
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Jet OLEDB:Database Password") = "KLAS"
'The following specifies the location of the database of which PW I'm trying to change.
.Open "Data Source= C:\Documents and Settings\ev010409\Desktop\KLAS.accdb;"
' Execute the SQL statement to change the password.
.Execute (strAlterPassword)
End With

' Clean up objects.
objConn.Close
Set objConn = Nothing

ChangeDBPassword_Err:
MsgBox Err.Number & ":" & Err.Description
End Sub

I had to make several modifications to the code that I pulled from the Internet. It was quite outdated. For example, it had extremely old provider information, which I had to update for Access 2007. It was also listed as a function, and I changed it to a subprocedure. Furthermore, I had to set the following two references in my shell database:
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ADO Ext. 2.8 for DDL and Security

After tweaking a little bit, I finally got the code to execute; and, it worked! It removed the password from the target Access 2007 database. I was then able to Compact and Repair the database successfully. I find it interesting that the only way to remove a database from an Access 2007 database, when it was created in a previous version, is using ADO via VBA. There might be other techniques, but I didn't run across any in my research.