vba 创建前端 MDE

Creating the Front End MDE

提问by Justin

I created a database for tracking metrics, with some automation tricks (email, .doc,.ppt presentations, etc) with a very large Main-table, and lots of forms/GUI. This is the first time I have ever I worried about an MDE/front-end for the thing. So if you would be so kind to answer a few questions, or offer any advice, it would be greatly appreciated (I would hate for all this work to not be utilized).

我创建了一个用于跟踪指标的数据库，其中包含一些自动化技巧（电子邮件、.doc、.ppt 演示文稿等）、一个非常大的主表和大量表单/GUI。这是我第一次为这件事担心 MDE/前端。因此，如果您愿意回答几个问题或提供任何建议，我们将不胜感激（我不希望所有这些工作都没有被利用）。

• What is the first thing I need to do? It the 2000 version that must be converted to 03 to create the MDE, but does that get done before I use the database splitter?

• Will the amount of objects in the database effect the ability to do this? I have something like 80 forms, 70 queries, 20+ macros, 12 tables, etc...but does the amount of objects prevent some of this from working well once the front end is there?

• when i split the database, can I continue to work/make changes and such on the "back end", and have those changes directly effect the front end?

• 我需要做的第一件事是什么？必须将 2000 版本转换为 03 才能创建 MDE，但是在我使用数据库拆分器之前是否已完成？

• 数据库中的对象数量会影响执行此操作的能力吗？我有 80 个表单、70 个查询、20 个以上的宏、12 个表等……但是一旦前端在那里，对象的数量是否会阻止其中一些工作正常？

• 当我拆分数据库时，我可以继续在“后端”工作/进行更改等，并使这些更改直接影响前端吗？

These may be some basic questions, but I don't know the answer so.....Thanks!

这些可能是一些基本问题，但我不知道答案所以.....谢谢！

回答by Seth Spearman

Here is my 2 ￠.

这是我的 2 ￠。

Question 1 - I have never used the database splitter as I feel I have more control doing it manually. If you do it manually you can do it to a version that does not have a database splitter. But if you do use the splitter then--yes--you will have to upgrade to a version that has a splitter before doing it.

问题 1 - 我从未使用过数据库拆分器，因为我觉得我可以更好地控制手动操作。如果您手动执行此操作，则可以对没有数据库拆分器的版本执行此操作。但是，如果您确实使用了拆分器，那么-是的-您必须先升级到具有拆分器的版本，然后再进行此操作。

To do it manually here are the steps.

要手动执行这里是步骤。

1. Backup everything.
2. Create a copy of your file into the same directory. So if you have an MyApp.MDB create a copy into the same directory with a new name, such as MyAppDATA.mdb.
3. Open the new DATA file (MyAppDATA.mdb) and delete all of the objects EXCEPT the TABLES.
4. Open the App file (MyApp.mdb) and delete all of the tables.
5. Also in MyApp.mdb...go to the File/Get External Data/Link Tables menu to link the tables in MyAppDATA.mdb to MyApp.mdb. Select All and create the links.

1. 备份一切。
2. 在同一目录中创建文件的副本。因此，如果您有 MyApp.MDB，请使用新名称（例如 MyAppDATA.mdb）将副本创建到同一目录中。
3. 打开新的 DATA 文件 (MyAppDATA.mdb) 并删除除 TABLES 之外的所有对象。
4. 打开 App 文件 (MyApp.mdb) 并删除所有表。
5. 同样在 MyApp.mdb 中...转到文件/获取外部数据/链接表菜单以将 MyAppDATA.mdb 中的表链接到 MyApp.mdb。全选并创建链接。

That should do it. And if you screw up you made a backup...right?

那应该这样做。如果你搞砸了，你做了一个备份......对吗？

A couple of tips and gotchas...be sure that you go to Tools/Options and that you are NOT showing System and Hidden tables. You just don't want to delete system tables from MyApp. Another way to do it is do NOT delete tables that start with MSys or USys.

一些提示和问题...确保您转到“工具/选项”并且您没有显示系统和隐藏表。您只是不想从 MyApp 中删除系统表。另一种方法是不要删除以 MSys 或 USys 开头的表。

Question 2 - Does not matter how many object you have. In fact you don't have that many objects anyway.

问题 2 - 你有多少对象无关紧要。事实上，无论如何你都没有那么多对象。

Question 3 - Yes...you will make backend changes in MyAppData.mdb and when you open MyApp.mdb those changes will auto-magically be there to see and query against etc. (In the query designer you may need to save/close/reopen to see new fields if you made the mod while in the query). The EXCEPTION to that is New Tables You will have to use the File/Get External Data/Link Tables option to create links to new tables.

问题 3 - 是的...您将在 MyAppData.mdb 中进行后端更改，当您打开 MyApp.mdb 时，这些更改将自动神奇地出现在那里以查看和查询等（在查询设计器中，您可能需要保存/关闭/reopen 以查看新字段，如果您在查询中创建了 mod）。唯一的例外是新表您必须使用文件/获取外部数据/链接表选项来创建到新表的链接。

One thing to remember (and that I hope you already realize) is that the one downside of splitting the database is that when you deploy the front end file that usually the relative path to the data will vary from machine to machine and there is no automatic re-linking of tables in access. If your target clients have full access you can always use Tools/Database Utilities/Linked Table Manager to refresh the links to the right location. If you can't do that then you will have to do one of the following:
1. Write code that does the automatic re-linking for you. Basically it will check the links...if invalid it will prompt the user for the data location (or look it up in an INI file) and re-link the tables.
2. Always deploy your app to the same location on all machines. If you have commercial visions for your application this won't work...I mention it for academic reasons. It might be doable for a limited deployment where you have a lot of control over file placement on each machine.
3. Put the Data file (MyAppDATA.mdb) onto a network share and link the table across the network using a drive mapping or UNC (\myserver\mydata\ApplicationData\MyAppData.mdb). The latter is preferred but both of them run the same risks as number two.

要记住的一件事（我希望你已经意识到）是拆分数据库的一个缺点是，当你部署前端文件时，通常数据的相对路径会因机器而异，并且没有自动重新链接访问中的表。如果您的目标客户具有完全访问权限，您始终可以使用工具/数据库实用程序/链接表管理器将链接刷新到正确的位置。如果您不能这样做，那么您必须执行以下操作之一：
1. 编写自动重新链接的代码。基本上它会检查链接...如果无效，它会提示用户输入数据位置（或在 INI 文件中查找）并重新链接表。
2. 始终将您的应用程序部署到所有机器上的相同位置。如果您对您的应用程序有商业愿景，这将行不通……我出于学术原因提到它。对于有限的部署，您可以对每台机器上的文件放置进行大量控制，这可能是可行的。
3. 将数据文件 (MyAppDATA.mdb) 放到网络共享上，并使用驱动器映射或 UNC (\myserver\mydata\ApplicationData\MyAppData.mdb) 通过网络链接该表。后者是首选，但它们都具有与第二个相同的风险。

Seth

赛斯

PS This answer assumes Access 2003.
PPS If you have commercial visions for your application then the table linking has got to be REALLY robust. PPPS I agree with the commenter that you may want to take the plunge and do SQL if it is in your skill set.

PS 此答案假设 Access
2003。PPS 如果您对应用程序有商业愿景，那么表链接必须非常可靠。PPPS 我同意评论者的意见，如果您的技能范围内，您可能想要尝试并执行 SQL。

回答by David-W-Fenton

One thing that hasn't been discussed, and that's the issue of whether the compile to MDE could fail. Basically, if your code compiles in your front-end MDB, it will convert to an MDE. But I've noticed that lots of people never compile.

尚未讨论的一件事是编译到 MDE 是否会失败的问题。基本上，如果您的代码在前端 MDB 中编译，它将转换为 MDE。但我注意到很多人从不编译。

Some hints for keeping your VBA code in good shape:

保持 VBA 代码良好状态的一些提示：

1. in VBE options, turn off COMPILE ON DEMAND.

2. add the COMPILE button to your standard VBE toolbar and USE IT OFTEN.

3. periodically, backup your MDB and decompile/recompile it.

1. 在 VBE 选项中，关闭 COMPILE ON DEMAND。

2. 将编译按钮添加到您的标准 VBE 工具栏并经常使用它。

3. 定期备份您的 MDB 并反编译/重新编译它。

Also, remember that you must keep the MDB source, as the VBA code is not editable in an MDE and not recoverable by any good method.

此外，请记住您必须保留 MDB 源代码，因为 VBA 代码在 MDE 中不可编辑，也无法通过任何好的方法恢复。

EDIT:

编辑：

Steps for a decompile:

反编译步骤：

1. backup your MDB.

2. start an instance of Access with the /decompile commandline argument. For, instance, I have a shortcut on my deskstop that has this as the target:

   "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /decompile

3. having opened that instance of Access, open the MDB you want to decompile. You will see nothing happen. DO NOTHING FURTHER IN THIS INSTANCE OF ACCESS -- close this instance of Access (the reason for this is that Michael Kaplan, who knows a thing or two about this, recommended that you never do any work in an Access instance opened with the decompile switch because he said there was no guarantee that the Access application code executed under those circumstances in a way that was fully safe for all kinds of Access work).

4. open the just-decompiled MDB holding down the shift key (you want to be sure that startup routines don't run because that would likely recompile the product before you've finished your cleanup) and compact the MDB (holding down the shift key again).

5. open the code editor and compile the project (DEBUG -> COMPILE [db name] for those who haven't step #2 in my original compiling instructions at the top of the post before the edit).

6. compact the MDB (doesn't matter if you bypass startup, since it's already fully compiled).

1. 备份您的 MDB。

2. 使用 /decompile 命令行参数启动 Access 实例。例如，我的桌面上有一个快捷方式，将其作为目标：

   "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /反编译

3. 打开 Access 实例后，打开要反编译的 MDB。你会看到什么都没有发生。在这个 ACCESS 实例中什么都不做——关闭这个 Access 实例（这样做的原因是 Michael Kaplan，他知道一两件事，建议你永远不要在用反编译开关打开的 Access 实例中做任何工作因为他说不能保证 Access 应用程序代码在这些情况下以对所有类型的 Access 工作完全安全的方式执行）。

4. 按住 shift 键打开刚刚反编译的 MDB（您要确保启动例程不会运行，因为这可能会在您完成清理之前重新编译产品）并压缩 MDB（再次按住 shift 键） ）。

5. 打开代码编辑器并编译项目（DEBUG -> COMPILE [db name] 对于那些在编辑之前没有在我的帖子顶部的原始编译说明中执行第 2 步的人）。

6. 压缩 MDB（是否绕过启动无关紧要，因为它已经完全编译）。

Why so many steps?

为什么要这么多步？

Because the purpose of the decompile is to get rid of the compiled p-code in order to start afresh from the canonical VBA code. Following the steps above insures that you have completely cleared the data pages storing the compiled code before you recompile. The reason for this is that without the compact step after the decompile, under some very rare circumstances, the code can behave strangely. I can't imagine that the old discarded p-code is being used again, but there's something about the pointers between the canonical code and the compiled code that apparently doesn't get completely flushed by a decompile without a compact.

因为反编译的目的是摆脱编译后的 p 代码，以便从规范的 VBA 代码重新开始。遵循上述步骤可确保在重新编译之前已完全清除存储已编译代码的数据页。这样做的原因是没有反编译后的压缩步骤，在一些非常罕见的情况下，代码会表现得很奇怪。我无法想象旧的被丢弃的 p 代码会再次被使用，但是规范代码和编译代码之间的指针显然不会被没有压缩的反编译完全刷新。

回答by KevenDenen

This would be a comment to Seth's answer, but my rep isn't high enough to comment yet.

这将是对赛斯回答的评论，但我的代表还不够高，无法发表评论。

Seth did a great job answering your questions, I just wanted to add a bit more to part #1 about using the Database Splitter. The Database Splitter in the Tools menu works fine. Doing it manually is alright too, but it's a whole lot faster and easier to use the Database Splitter. I've used it a dozen times and never encountered any issues after using it.

Seth 很好地回答了您的问题，我只是想在第 1 部分中添加更多关于使用数据库拆分器的内容。工具菜单中的数据库拆分器工作正常。手动执行也可以，但使用数据库拆分器要快得多，也更容易。我已经使用了十几次，使用后从未遇到任何问题。

http://www.databasedev.co.uk/split_a_database.htmlhas a decent page about some of the pros, cons of splitting your database.

http://www.databasedev.co.uk/split_a_database.html有一个不错的页面，介绍了拆分数据库的一些优点和缺点。

http://www.accessmvp.com/TWickerath/articles/multiuser.htmalso has some good info when dealing with a split database in a multi-user environment.

http://www.accessmvp.com/TWickerath/articles/multiuser.htm在多用户环境中处理拆分数据库时也有一些很好的信息。

回答by Tony Toews

Seth gave you a very good answer. But I'll add a few comments.

赛斯给了你很好的答案。但我会添加一些评论。

The number of objects only becomes relevant when you get close to about 1000 forms, reports and modules which have code. There's a limit about there. If you do get that message when trying to make an MDE then you almost certainly have a code error and need to compile to find the error

只有当您接近大约 1000 个具有代码的表单、报表和模块时，对象的数量才会变得相关。那里有一个限制。如果您在尝试创建 MDE 时确实收到该消息，那么您几乎肯定有代码错误，需要编译以找到错误

Another resource is "Splitting your app into a front end and back end Tips"

另一个资源是“将您的应用程序拆分为前端和后端提示”

See the Auto FE Updater downloadspage to make the process of distributing new FEs relatively painless.. The utility also supports Terminal Server/Citrix quite nicely.

请参阅Auto FE Updater 下载页面，以使分发新 FE 的过程相对轻松。该实用程序还非常好地支持终端服务器/Citrix。