Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Modules (VBA Coding) FAQ

How To

How to programmatically determine if your database is in MDE format by jrbarnett
Posted: 12 May 03

Microsoft Access is a widely used desktop database. Many developers supply commercial applications developed using it as MDE files, which offer a number of benefits to both end users and developers:

1) They stop the end user accessing and possibly editing the source code and form/report designs. In so, it helps keep the developer's intellectual property intact and force any upgrades to come from the developer rather than be done in house or elsewhere.
2) The format ensures the database is kept in a compiled state, resulting in improved performance over a non compiled database.
3) It stops program code opening in the debugger or forms/reports in design mode if the database crashes during use, thus avoiding startling end users and helping avoid potentially embarrassing incidences for the developer if the code doesn't work as it should.
4) The removal of all source code and form/report designs makes it pointless to anybody attempting to break Access security.

While it is obviously advantageous for end users to have the database in an MDE format, developers may want to have extra error handling code which opens form or report designs at problematic areas for debugging purposes when they are working with the original MDB. However, should this be triggered during MDE use, if not written properly it may fail.

An easy way to get around this, that I have not seen documented elsewhere, is to determine whether the database is an MDE or MDB (normal) database file within the error handler.  The easiest approach is simply to read the CurrentDB.Path variable, and look at the file extension, but this is not the most reliable way, for example if the Access database is an addin (.MDA extension) or has been renamed to a non standard extension, perhaps as part of a third party proprietary system.

As part of the MDB to MDE creation process, a property called "MDE" with a value of "T" is added to the database object. Checking this is the most reliable way to ascertain the format.

The following function will work in Access 97, 2000 and 2002 without modification.  However, In Access 2002(XP) you will need to go to Tools -> References and add the Microsoft DAO Object Library version 3.60, as this uses the Database and Property objects and Properties collection.  These are not available in ADO and I have yet to find an equivalent method that is as reliable.

Public Function IsMDE(db As Database) As Boolean
' It works on the fact that an MDE database has a property of "MDE" added with a value of "T"
' This is far more reliable and less risky than checking the file extension or attempting to access
' form or report design or VBA module code with an error handler.
' The use of the DAO Properties collection,
' For...Each...Next loop and On Error Resume Next handler
' gets around the problem of not having the property in an
' MDB database which otherwise causes run time errors.

Dim prp As Property

IsMDE = False ' Assume it is not an MDE file.

On Error Resume Next
For Each prp In db.Properties
    If prp.Name = "MDE" Then
        If prp.Value = "T" Then IsMDE = True
        Exit For
    End If
End Function

To use this function, the code is something like:

If IsMDE (CurrentDb) Then
    MsgBox "This database is in MDE format", vbOkOnly+vbInformation
    MsgBox "This database is not in MDE format", vbOkOnly+vbInformation
End If

This function is part of my code library and I have used it in a number of projects.

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close