Click here to Skip to main content
Click here to Skip to main content

Access - Automate Compile Code, Compact/Repair, and Make MDE

, 26 Aug 2009
Rate this:
Please Sign up or sign in to vote.
Automate Compile Code, Compact/Repair, and Make MDE for Access.

Introduction

It appears to be difficult to automate the "Compile" and "Make MDE" functions in Access.

Worst practices: The only automated way to Make an MDE is through an undocumented Access SysCmd (ewwww).

Basically, that means this will work for Access 2003, but don't expect it to work in 2007.

Files

The downloaded project will just compile all MDBs in the current directory. (Open it with VS 2005.)

Using the code

Code works:

  • For Access 2003
  • Using .NET 2.0

References to add:

  • Microsoft.Office.Interop.Access (.NET Reference)
  • Microsoft DAO 3.6 Object Library (COM Reference)
using System;
using System.IO;
using Microsoft.Office.Interop.Access;

namespace CompileAccessFiles {
    /// <summary>
    /// Allows for the following actions to be done on an access database:
    ///    Compact/Repair,
    ///    Compile VBA code,
    ///    Make MDE File.
    /// Note: Do not open the files before running these procedures.
    /// </summary>
    public static class AccessDB {
        /// <summary>
        /// Compiles the VBA, then compacts and repairs, then makes and returns the MDE file.
        /// Note: this uses an undocumented Access SysCmd 603 for making MDE files. 
        ///       This works on Access 2003, but any other version is suspect.
        /// </summary>
        /// <param name="mdbFile"></param>
        /// <returns>mde file</returns>
        public static FileInfo FullCompileToMDE(FileInfo mdbFile) {
            ApplicationClass app = OpenAccessApplication();

            CompileVBA(mdbFile, app);
            CompactAndRepair(mdbFile, app);
            FileInfo newfile = MakeMDE(mdbFile, app);

            CloseAccessApplication(ref app);

            return newfile;
        }
        /// <summary>
        /// Closes an access application
        /// </summary>
        /// <param name="app"></param>
        public static void CloseAccessApplication(ref ApplicationClass app) {
            app.Quit(AcQuitOption.acQuitSaveNone);
            app = null;
        }
        /// <summary>
        /// Makes an MDE file.
        /// Note: this uses an undocumented Access SysCmd 603 for making MDE files.
        ///        This works on Access 2003, but any other version is suspect.
        /// </summary>
        /// <param name="mdbFile"></param>
        /// <param name="app"></param>
        /// <returns>mde file</returns>
        public static FileInfo MakeMDE(FileInfo mdbFile, ApplicationClass app) {
            string newfilename = mdbFile.FullName;
            newfilename = newfilename.Remove(newfilename.Length - 1) + 
                                      "e";//switch from MDB to MDE
            //SysCmd is undocumented, and #603 is especially undocumented.
            app.SysCmd((AcSysCmdAction)603, mdbFile.FullName, newfilename);
            return new FileInfo(newfilename);
        }
        /// <summary>
        /// Compacts and repairs an Access database
        /// </summary>
        /// <param name="accessFile"></param>
        /// <param name="app"></param>
        public static void CompactAndRepair(FileInfo accessFile, ApplicationClass app) {
            string tempFile = Path.Combine(accessFile.Directory.FullName, 
                              Path.GetRandomFileName() + accessFile.Extension);
            
            app.CompactRepair(accessFile.FullName, tempFile, false);
            app.Visible = false;

            FileInfo temp = new FileInfo(tempFile);
            temp.CopyTo(accessFile.FullName, true);
            temp.Delete();
        }
        /// <summary>
        /// Opens an access application
        /// </summary>
        /// <returns></returns>
        public static ApplicationClass OpenAccessApplication() {
            ApplicationClass app = new ApplicationClass();
            app.Visible = false;
            return app;
        }
        /// <summary>
        /// Compiles the VBA for an Access database
        /// </summary>
        /// <param name="mdbFile"></param>
        /// <param name="app"></param>
        public static void CompileVBA(FileInfo mdbFile, ApplicationClass app) {
            app.OpenCurrentDatabase(mdbFile.FullName, true, "");
            if (!app.IsCompiled) {
                //c.DoCmd.OpenModule(c.CurrentDb().
                //   Containers["Modules"].Documents[0].Name, Type.Missing);
                app.RunCommand(AcCommand.acCmdCompileAndSaveAllModules);
            }
            app.CloseCurrentDatabase();
            app.Visible = false;
        }
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

ColinBashBash
Software Developer
United States United States
likes boardgames, computer games, and enjoys his .net programming job.

Comments and Discussions

 
GeneralCool! Pinmembergreenb7516-Dec-10 21:34 
You're code is very helpful! thanks!! Laugh | :laugh:

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 26 Aug 2009
Article Copyright 2009 by ColinBashBash
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid