Access - 自动化编译代码、压缩/修复，以及制作 MDE

• 下载源代码 - 3.38 KB

引言

似乎很难在 Access 中自动化“编译”和“制作 MDE”功能。

不良实践：自动化制作 MDE 的唯一方法是通过 Access 的一个未公开的 SysCmd（太糟糕了）。

基本上，这意味着它适用于 Access 2003，但不要期望它在 2007 中工作。

文件

下载的项目将编译当前目录中的所有 MDB 文件。（使用 VS 2005 打开它。）

使用代码

代码有效

• 适用于 Access 2003
• 使用 .NET 2.0

需要添加的引用

• Microsoft.Office.Interop.Access (.NET 引用)
• Microsoft DAO 3.6 对象库 (COM 引用)

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;
        }
    }
}