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.

Comments

Anonymous said…
THANK YOU !!!
Searched forever on this bug.
Anonymous said…
A VERY useful piece of advice!
:)
PK
Anonymous said…
Your code worked perfectly. Thank you you saved me hours of work.
Anonymous said…
I ran into this same probably with a access 2007 database. I ended up making a blank DB and import all objects from the 2007 db into the blank DB I created. All forms/tables/queries seemed to import correctly.
Anonymous said…
Trying to use this, but getting an error when compiling or running the module at line 2-
Compile error:
User-defined type not defined.
Anonymous said…
Nevermind - worked like a charm. Forgot to set the references :P
Anonymous said…
Sir, you are officially my here.
3 weeks and hundreds of google searches later i am relieved!
Anonymous said…
I'm a newbie, but how do I set the references? I'm getting the compile error: User-defined type not define. Any help.. Thanks. rick
Anonymous said…
Did a little poking and found it. This worked great.
Thk. Rick
Anonymous said…
Thanks, worked perfectly for me as well!

For those who like me are newbies to Access and are still struggling to work out how to set the references...

In the Visual Basic window, click on Tools --> References, and then select the two references indicated in the main text above. Then run the code and should be good to go!
Anonymous said…
I hаvе to thank уou for the efforts уou haѵe ρut in penning this blog.
I rеally hοpe to see the same high-graԁe blog ρosts by
you later on aѕ well. In truth, yоur creаtivе wгiting abilitіes has motiѵаted me to get my verу oωn sіte now ;)

my web-ѕіte; massage workshop
Also visit my webpage water massage
Anonymous said…
i entered the code and changed the references it did nothing....I am a newbie any help...?
Anonymous said…

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, LOCK is the old password, and I am wanting to set the password to NULL
strAlterPassword = "ALTER DATABASE PASSWORD NULL [LOCK];"

' 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") = "LOCK"
'The following specifies the location of the database of which PW I'm trying to change.
.Open "Data Source= C:\Users\lowana.hernandez\Desktop\SCG 9-30-13 (2).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
Ludogerman said…
Hello,
I don't know a lot about "modules" in MS Access. But I need a solution to remove the password of my database.
After the 1st line of the code it appears "Type not defined". Can somebody help me?

Popular posts from this blog

The agony of victory....a.k.a. picking my jaw up off the floor

"The Axis of Evil and the Doctrine of Preemption Three Years On"