How to make design changes to an Access MDE file

 

Please Note: This tutorial is for individuals or companies that own an MDE Access database, but have lost the original MDB file and wish to make design changes to the MDE file.  This is an educational document only, provided 'as-is' and is not intended for illegal purposes. 

If you are not the legal owner of the database you should not proceed in following this tutorial.

Requirements

MDE Unlocker Utility v3.00: mdeunlocker.htm (DEMO available)

v3.00 MAJOR UPDATE: Access 97 MDE files are now fully supported.  Includes many minor bug fixes & internal improvements

Further details can be obtained from the MDE Unlocker Frequently Asked Questions page.

Introduction

A common question in Access database newsgroups and forums is ‘How can I convert my Access database from MDE format into an MDB file?’ If your file contains VBA code then the correct answer is that you can’t since the MDE file is locked and protected.  However, the protection isn’t the main obstacle in trying to convert the MDE file, the main issue is that all VBA modules are saved in a compiled state with any original source-code being stripped out.

For many years users requiring MDE to MDB conversions have been advised to ‘Import’ tables, queries, forms, reports etc into a new blank database and then re-write the VBA code from scratch.  This in itself is tricky as forms and reports are protected and you need a special tool to ‘extract’ the form/reports into the new database. (see References for further details)

Since the original VBA code is not available, you have only a few options:

1. Re-write the VBA code yourself
2. Ask an Access developer to recreate the functionality of the VBA code for you
3. Use the MDE Unlocker by EverythingAccess.com to make changes to form/reports functionality (without having to re-write any VBA code)
4. Use the MDE to MDB Conversion service at EverythingAccess.com to recover your original source code (including variable names etc., but excludes VBA comments). **

** Please Note:  This service is only available to users that can prove lawful ownership of the MDE file.  Follow the hyperlink for further information.

This tutorial is for use with the MDE Unlocker utility v3.00 - it will help you in using the software and making minor changes to forms/reports and/or adding functionality to your MDE database.  You will not need to re-write any VBA code, however you are limited in the changes that you are allowed to make. 

By following this tutorial you will learn how to make changes to the MDE database, but please note that this method DOES NOT retrieve the original VBA code – it simply allows you to make cosmetic and functional changes to the database without the need for VBA project changes.

Due to the technicalities that will be outlined in this tutorial, you will become aware that this new method is not usually suitable for large-scale changes due to the restrictions of having a non accessible VBA project.  However, for cosmetic changes and/or expanding current functionality this method can be used effectively. 

    What this tutorial can help you achieve with an MDE file:

·        Make cosmetic changes to existing forms/reports (add, delete, edit & move controls)

·        Make functionality changes using Macros

·        Add new forms/reports (non-VBA only - all functionality by Macros)

    What this tutorial won’t help you achieve with an MDE file:

• It won’t give you direct access to the VBA project.  (If you need this, click Here)

STAGE 1:       Unlock the MDE file.

The first stage in preparing the MDE file for design changes is to ‘unlock’ the file.

Unlocking the file is a procedure that can be achieved using a program called MDE Unlocker by EverythingAccess.com;

mdeunlocker.htm *** Software Updated 22nd August 2006 (see note at top) ***

A DEMO is available at the above web-address which is fully functional, however some Caption properties for all form/report controls are reset to '(demo)'.  The full software which removes this limitation costs only GBP £39.99 (US $75/EU €65) and is available at the same web-address above.

The MDE Unlocker tool works for all versions of Access from 2000 upwards and also works even if the database has been encrypted using the built-in Access encryption.  Also works on files with Access User-level Security (ULS) applied.

Instructions

Important: To ensure best performance you should run the 'Compact and Repair' option from within Microsoft Access before using the MDE Unlocker!

Download and run the MDE Unlocker utility.  Select the MDE file that you need to unlock, then press the 'Proceed to next stage' button.  The file will now be analysed.  Once analysis is complete, you will be given the option to 'Convert' or 'Unlock' your MDE file. 

Choose 'Unlock' for the purpose of this tutorial.  Now press "Proceed to next stage".

Once the file has been unlocked you will receive a confirmation message.  You may now proceed to Stage 2 - Editing your MDE file in Microsoft Access.

STAGE 2:       Edit your MDE file using Microsoft Access.

Now that you have an unlocked MDE file, open the database in Access (tip: hold down the shift key to bypass any startup actions if necessary) and you will find that you now have access to the ‘Design’ and ‘New’ actions within all design sections including forms, reports, macros.  Please note that all VBA class modules are disabled at this point - if you open the forms/reports in non-design mode, no VBA code will run until the file is re-locked later.

Please read the notes below before rushing in making changes already!

1. Ensure you use the correct version of Access to edit/update your MDE file:

   Access 97 MDE files

   You can use only Access 97 to edit the unlocked Access 97 MDE file.

   Access 2000 MDE files

   You can use either Access 2000, 2002(XP) or 2003 to edit the unlocked Access 2000 MDE file.

   Access 2002(XP) and 2003 MDE files

   You may use either Access 2002 or 2003 to edit these files.

2. VBA Project is NOT accessible

   If at any point you try to view a VBA module or any other VBA project component, you will receive an error such as ‘Project is unviewable’ (Access 2000) or 'Unexpected Error' (Access 97).  In Access 2000 or above the VBA IDE will open, but you are unable to make any changes or view any VBA code. 

3. How do I make changes to the design of an existing form or report?

   You can modify form and report designs freely under most circumstances.  Please see the limitations below;

   Limitations
   
   If you delete a control from a form or report which has a VBA class module associated with it (i.e. HasModule=True) then after re-locking the MDE file (see Stage 3) the VBA code will fail.  Instead of deleting controls, you should set the 'Visible' property of the control to False.

4. How do I make functionality changes to an existing form or report?

   Although you cannot add or append any VBA code to your database, you can create Macros freely.  Macros allow you to make complex changes to the functionality of your database.

   Click here for an article by Microsoft about creating and using Macros in Access. 

5. How do I add a new form/report?

   You are free to create new forms/reports, however they will have the HasModule=False property set by default, and if you try to change this setting you will receive an error.  

   You can only use Macros or existing compiled VBA public functions (see next bullet) in the new form/report.

6. Can I make use of existing VBA functions in my new forms/report?

   Although you cannot add or append new VBA functions/modules you CAN make use of the ones that are already compiled in your database!  However, you need to identify what functions are available to you before unlocking the file - this is beyond the scope of this article.

STAGE 3:       Re-lock the MDE file.

After you have made design changes, you must remember to re-lock the MDE file otherwise any VBA module code will not work.

To re-lock the MDE file, use the MDE Unlocker utility and open the MDE file again.  This time you will be taken to the "Re-Lock" option and the software will guide you through this process.

Conclusion

Fairly large alterations can be made to an MDE file by using this method - you just need to have a reasonable knowledge of Microsoft Access and Macro functions. 

*Microsoft Access is a trademark of Microsoft Corporation in the United States and other countries*

IMPORTANT: This document may not be reproduced in part or whole without prior consent from the author.

www.everythingaccess.com