Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to programmatically determine if your database is in MDE format

How To

How to programmatically determine if your database is in MDE format

by  jrbarnett  Posted    (Edited  )
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.

Code: 
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
Next
End Function

To use this function, the code is something like:

Code: 
If IsMDE (CurrentDb) Then
	MsgBox "This database is in MDE format", vbOkOnly+vbInformation
Else
	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.

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top