The MDE Database Format Revisited | Real World Microsoft Access Database Protection and Security

As I said in Chapter 1, the MDE format is the only gold standard security that Access provides, and it is dead easy to use. All you have to do is choose Tools ˜ Database Utilities ˜ Make MDE, and you are done. But what special things does the MDE format do for your database to make it so secure?

All forms, reports , and modules are permanently locked so that they cannot be opened in design mode. For once in Access, there are no exceptions or workarounds ”these objects are locked.
All the VBA code is compiled and saved in PCode format (an explanation follows ).
All the VBA code is then removed from the database.
There is no development environment, so the user cannot use the Project Explorer, the Intermediate window, or the Object Browser to find out more about the application.
The PCode, forms, and reports remain static and can never be changed.
The PCode is quite secure because software cannot translate it back to useful VBA Code.

But What Is PCode?

PCode is an interpretation of your beloved VBA code that has been transferred into a computer-independent and very machine-ready language of bytes, memory locations, pointers, and English text. Once it is in this machine format, the Access PCode interpreter will then send instructions to the Access application so that it interacts correctly with the Windows operating system. Access always requires PCode for the current VBA code that it is processing. If the current code module is uncompiled, Access will compile and create PCode for it. And finally, because the MDE format is always compiled, Access never wastes time recompiling code.

When an MDE Database is Applicable

If you are interested in the concept of the MDE format and are not sure whether it will work for you, my first recommendation is try it out on a database that you use regularly. When you change to the MDE format, you will encounter issues similar to those when you work with a programming language such as Visual Basic 6. You first write some code, you compile it, and then you deliver it for use. The user only ever sees the final product, and you maintain the code in a safe location.

But at what sort of database environments should you be aiming your MDE database?

The MDE file format is suited to front-end databases or database wizards.
The MDE format is relevant in situations where you distribute the Access application to many clients or where the database is available as a download from the Internet. I personally have been using the MDE format for shipping trial versions of my data mining shareware since 1997.

Note	The process of saving a database as an .MDE file compiles all modules and compacts the destination database, so there is no need to perform these steps before saving a database as an .MDE file.

Problems with the MDE Database Format

Though I am generally bullish about the security of the MDE format, there are a number of things to be aware of, such as:

The MDE format is unsuitable when a database is used as a back-end database or as a standalone database. In this case, upgrades of software require you to transfer all the data into a new database.
If you only have the *.MDE file and don't have the MDB database, then no one can restore your code for you. This issue can be a big problem for the IT manager if you thought you purchased source code; and if it happens to the developer, stick your head under a pillow and scream loudly.
The MDE format provides no protection for tables, queries, macros, relationships, database properties, and startup options.
If you have a bug in the MDE database, you will need good error handling code to work out the problem.
The MDB format can be too restrictive if your users or your VBA code needs to create new forms and reports in the database.
If you are using Access 2002/2003 to develop an Access 2000 database, you cannot create an Access 2000 MDE.
Backups and versions of software are more important when you're working with MDEs, because you do not have the dual live and development MDB situation anymore.
Password-cracking programs can still detect database passwords just as easily as before, and because the workgroup file remains the same, it is just as vulnerable. Only VBA code is secure because there is none to crack.
You cannot add or delete references to object libraries or databases by using the References command on the Tools menu.

Now we will look at how workgroup security interacts with MDE databases.

Workgroup Security

Workgroup security works the same for the MDE format as it does for the MDB format, except that the Administer and Modify Design permissions do not apply to forms and reports. One thing that you need to be aware of if you want to convert the database to the MDE format is that your workgroup user account will need the following permissions:

Open/Run and Open Exclusive permissions for the database itself.
Modify Design or Administer permission for any tables in the database, or you must be the owner of any tables in the database.
Read Design permission for all objects in the database.

To conclude this section, I will repeat myself by reminding you that workgroup security is the only way to secure individual tables in an MDE database.

Database Password

Database passwords work the same on MDE-format files as on MDB files. In both cases, you will need to open the database in exclusive mode to set or clear the password.

Hiding Queries in MDE Databases

Early in the chapter, I described how you could save queries as SQL text strings and store them in the RecordSource property of a form or report or in VBA code to make them more secure. Move your queries from the Database window and save them in the RecordSource property of a form or a report. This action is especially pertinent when you are using the MDE format, because it will do a very good job of protecting the query. Of course, if you can save the SQL of the query in SQL in the VBA code, it will be very secure.

Using Forms in MDE Databases

Access forms in MDE databases are very secure in most ways because you can never open them in design view and you can never get to the code. Nevertheless, a clever programmer can find out things like the RecordSource of a form or report, and maybe even the RecordSource of a data-aware control, such as a drop-down list. Of course, once someone discovers a query, that person can run it from an unprotected Database window. If you don't want this to occur, you should manipulate the properties and methods of the form by using code in another form or module and add the RecordSource to the form in this code. In this instance, the ( unbound ) form in design mode does not have a value in the RecordSource property until the software sets it. For those of you who are just looking for interesting code, you may enjoy the flexibility offered by using an Access form as a class module.

 Private Sub cmdOpenUnbound_Click() ' Open a form, manipulate some of the form's properties, and then ' add the (secret) RecordSource to the form. DoCmd.OpenForm "frmOrdersUnbound" With Form_frmOrdersUnbound    .AllowEdits = True    .RecordSelectors = False    .NavigationButtons = True    .Caption = "Real World Demonstration"    .RecordSource = "select top 5 * from Orders order by OrderDate, Freight desc" End With End Sub

You can find the code for this example in the frmOpenUnbound form in the demonstration database. Now I will show you how to create an MDE database using VBA code.

Creating an MDE Database Automatically

While writing this chapter, I had discussions with another Access developer about what he does before he sends a new version of his Anesthetics database. He explained that he creates an .MDE file, adds a version number to the database, clears a few startup options, and then packs the resulting file into a *.ZIP file.

Though it is beyond the scope of the book to explain all those steps, I thought it would be worthwhile to put an example together that automated the MDE creation process. You will find this example in the demonstration database in a form called frmConvertToMDE . The following code from that form illustrates how to automate the menu commands required to create an *.MDE file. In this example, I have added some additional checks to see whether the file exists and whether it is actually an MDE-format database.

Note	The frmConvertToMDE form in this example requires an unsecured Northwind database.

 Private Sub cmdDBtoMDE_Click() ' Compile the current database into MDE format. ' This form requires a reference to ' Microsoft DAO 3.6 or 3.51 library. Dim strMdeName As String Dim OKtoMake As Boolean Dim intMakeMDE As Integer Dim wrkJet As Workspace Dim dbs As Database Dim accApp As Access.Application Const MDBFILE = "C:\Data\Northwind.mdb" Const FILENOTFOUND = 3024 Const MAKEMDE = 603 On Error GoTo err_cmdDBtoMDE strMdeName = left(MDBFILE, Len(MDBFILE) - 4) & ".mde" ' Create a Jet workspace object by using the anonymous Admin user ' You will need to change the user name and password if the ' database is secured with a workgroup. Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet) On Error Resume Next Set dbs = wrkJet.OpenDatabase(strMdeName, True) If Err.Number = FILENOTFOUND Then    OKtoMake = True ElseIf Err.Number = 0 Then    On Error GoTo err_cmdDBtoMDE    ' Let's find whether the file is actually an MDE-format file.    If IsItMDE(dbs) = True Then       intMakeMDE = MsgBox("The MDE format database " & strMdeName & _                           " already exists" & vbCrLf & _                           "Would you like to overwrite it?", _                           vbYesNo + vbDefaultButton2, "Overwrite MDE database")       If intMakeMDE = vbYes Then          ' Confirmation means that the database will be deleted.          dbs.CLOSE          wrkJet.CLOSE          OKtoMake = True       End If    Else       MsgBox "This file is not an MDE database"    End If Else    ' Problem opening the database in exclusive mode.    MsgBox "Error No. " & Err.Number & " -> " & Err.Description, vbCritical, _           "Problem opening the database in exclusive mode"    GoTo exit_cmdDBtoMDE End If ' Now procede to making the MDE-format file by using ' a secret action of the SysCmd method. If OKtoMake Then    Set accApp = New Access.Application    ' Use an undocumented constant of the syscmd function.    accApp.SysCmd MAKEMDE, MDBFILE, strMdeName    If Len(Dir(strMdeName)) > 0 Then       MsgBox strMdeName & "file has been created"    Else       MsgBox "There seems to have been a problem creating the MDE file. " & _              "You should open your database and see that it compiles " & _              "and then try a manual build of the MDE database from the " & _              "Database Utilities Menu", vbCritical, "Problem creating MDE File"    End If Else    MsgBox "MDE file creation was canceled" End If exit_cmdDBtoMDE:    Set wrkJet = Nothing    Set dbs = Nothing    Set accApp = Nothing    Exit Sub err_cmdDBtoMDE:    Select Case Err.Number       Case Else          MsgBox "Error No. " & Err.Number & " -> " & Err.Description, vbCritical    End Select    Resume exit_cmdDBtoMDE End Sub

Just in case you missed it, the magical command that actually makes the .MDE file is the SysCmd function. In this case, it uses an undocumented number (603) that I have stored in the constant MAKEMDE . Now I will show you the VBA function that checks whether the database is actually an MDE database. To do this, I test the MDE property in the database and, if it is set to the string value of "T" , then the database is MDE.

 Function IsItMDE(dbs As Object) As Boolean    ' Software to test whether the database is in MDE format.    Dim strMDE As String    On Error Resume Next    strMDE = dbs.Properties("MDE")    If Err = 0 And strMDE = "T" Then       'This is an MDE database.       IsItMDE = True    Else       IsItMDE = False    End If End Function

To wrap up the discussions on MDE files, I have to mention that MDE format databases actually come in two formats, encrypted and normal. This information came as a bit of surprise to me because I never came across any literature on the topic.

Encrypting MDE Databases

The final word on MDE databases must be devoted to the subject of database encryption. As I mentioned in Chapter 9, it is possible to protect a database by encrypting it. This protection feature will definitely hide the contents of a database from someone who is keen enough to snoop around by using a text editor, but as I said, it is worthwhile to consider whether the benefits outweigh the penalties. Surprisingly, the same arguments for and against apply to an MDE database as to an MDB database, with the exception that the VBA code is not visible. Even in the MDE database, I have no trouble identifying the SQL of queries even if they are embedded in code. Once again, you have to decide whether this is really a concern because you will pay a price in terms of performance and the inability to compress the database.

If you decide that you would like an encrypted *.MDE file, your best course of action is to encrypt the MDB database, and then, when you make the conversion, the *.MDE file will also be encrypted.