vba 在 Access 中打开任何表单之前运行代码

Running code before any forms open in Access

提问by

So I have an Access database with a front and a back end. I will be distributing it to users soon, but I have no control over where exactly they will put the files on their computers. However, I think I can count on them putting front and back ends in the same folder.

所以我有一个带有前端和后端的 Access 数据库。我将很快将其分发给用户，但我无法控制他们将文件放在计算机上的确切位置。但是，我想我可以指望他们将前端和后端放在同一个文件夹中。

As such, when the front end opens, I want it to check that the linked tables are correctly connected to the back-end database. I have working code for this; however I don't know where to put it. When the front end opens, a menu form is automatically opened (configured through the start-up dialogue box). I have put the code in the OnOpenevent, which I thought occurred before any data is loaded, but when I test this out, I get a message telling me that the back-end cannot be found (it's looking in its old location).

因此，当前端打开时，我希望它检查链接表是否正确连接到后端数据库。我有这方面的工作代码；但是我不知道把它放在哪里。当前端打开时，会自动打开一个菜单窗体（通过启动对话框配置）。我已将代码放入OnOpen事件中，我认为这发生在加载任何数据之前，但是当我对此进行测试时，我收到一条消息，告诉我找不到后端（它正在查找其旧位置）。

Basically, is there an event I can use that runs before any forms have opened?

基本上，是否有我可以使用的在任何表单打开之前运行的事件？

回答by Matt

Create a Macro and name it "autoexec". For the macro action, select "RunCode" and then set the function name to the name of the function you use to check your linked tables.

创建一个宏并将其命名为“autoexec”。对于宏操作，选择“RunCode”，然后将函数名称设置为用于检查链接表的函数名称。

回答by Matt

As Matt said, create a macro, call it "autoexec", and select "RunCode" as the macro action. The Function Name argument should be the name of the function you wish to run (and not a sub), and if the function has no arguments, you should still put () at the end, or it won't work.

正如马特所说，创建一个宏，将其命名为“autoexec”，然后选择“RunCode”作为宏操作。Function Name 参数应该是你想要运行的函数的名称（而不是子函数），如果函数没有参数，你仍然应该把 () 放在最后，否则它不会工作。

回答by Fionnuala

I generally prefer to create a small form that runs a number of checks, such as finding the back-end and so forth, and set various options. The code for the open event of this form might be:

我通常更喜欢创建一个小的表单来运行许多检查，例如查找后端等，并设置各种选项。此表单的打开事件的代码可能是：

Me.Visible = False
'Determines if the database window is displayed
SetProp "StartupShowDBWindow", False, dbBoolean
'Hide hidden and system objects
SetOption "Show Hidden Objects", False
SetOption "Show System Objects", False

'Find back end
CheckLinkPath

I keep a table of tables to be linked in the front-end and if any are missing, this form can be used to report the error, or any other error for that matter.

我在前端保留了一张要链接的表格，如果缺少任何表格，可以使用此表格报告错误，或与此相关的任何其他错误。

Set RS = CurrentDb.OpenRecordset("Select TableName From sysTables " _
& "WHERE TableType = 'LINK'")

RS.MoveFirst
strConnect = db.TableDefs(RS!TableName).Connect
If Not FileExists(Mid(strConnect, InStr(strConnect, "DATABASE=") + 9)) Then
    'All is not well
    blnConnectError = True
Else
    Do Until RS.EOF()
        If db.TableDefs(RS!TableName).Connect <> strConnect Then
            blnConnectError = True
            Exit Do
        End If

        RS.MoveNext
    Loop
End If

If everything is ok, the small form calls the main menu or form and the user never sees the checking form. I would also use this form to open a password prompt, if required.

如果一切正常，小表单调用主菜单或表单，用户永远不会看到检查表单。如果需要，我还会使用此表单打开密码提示。

回答by Philippe Grondier

Before putting your front end and back end in the same folder, think about it. Isn't it worth having 2 folders? What about multiple users on the same computer accessing the same back-end database? What about multiple users accessing the same databse through a network? What is the necessity of having a front end-back end typology if your app is basically a single-user app?

在将前端和后端放在同一个文件夹之前，请考虑一下。不值得拥有 2 个文件夹吗？同一台计算机上的多个用户访问同一个后端数据库会怎样？多个用户通过网络访问同一个数据库怎么样？如果您的应用程序基本上是单用户应用程序，那么拥有前端后端类型学的必要性是什么？

Why don't you add a dialog box to your app, in case your connectivity is lost? You could create a fileDialog object in your code, allowing the user to browse for a *mdb file anywhere on his computer/network. It is then possible to control that the selected mdb file contains all requested tables and open the corresponding links (I guess you are using the transferDatabase command).

如果您的连接丢失，为什么不向您的应用程序添加一个对话框？您可以在代码中创建一个 fileDialog 对象，允许用户在其计算机/网络上的任何位置浏览 *mdb 文件。然后就可以控制选定的 mdb 文件包含所有请求的表并打开相应的链接（我猜你正在使用 transferDatabase 命令）。

And what about additional tools/references you'll need for your app to run when you'll distribute it to your final users? By default, MS Access records the 3 basic ones:

当您将应用程序分发给最终用户时，您的应用程序运行所需的其他工具/参考又如何呢？默认情况下，MS Access 记录 3 个基本的：

• Visual Basic For Application
• Microsoft Access Library
• Microsoft DAO Library

• Visual Basic 应用程序
• 微软访问库
• 微软 DAO 库

If your app needs anything else, such as ADO or Office objects (ADODB.recordset or Office commandbars for example), you will have to add the references manually for each installation, as the final user won't be able to open the VBA window and access the tools/references menu.

如果您的应用程序需要其他任何内容，例如 ADO 或 Office 对象（例如 ADODB.recordset 或 Office 命令栏），则您必须为每次安装手动添加引用，因为最终用户将无法打开 VBA 窗口并访问工具/参考菜单。

So, if you need to deploy your app on multiple computers, I strongly advise you to use a deployment tool such as this free one. You'll need a few hours to be able to use it properly, but the result is worth it. You'll be able to give your clients a real installer module. It will create folders, add requested shortcuts, and manage references in the computer's registry. This will make your deployment definitely painless!

因此，如果您需要在多台计算机上部署您的应用程序，我强烈建议您使用诸如这个免费的部署工具。您需要几个小时才能正确使用它，但结果是值得的。您将能够为您的客户提供一个真正的安装程序模块。它将创建文件夹、添加请求的快捷方式并管理计算机注册表中的引用。这将使您的部署绝对轻松！

EDIT: the autoexec macro is definitely the right solution for calling code before any event.

编辑： autoexec 宏绝对是在任何事件之前调用代码的正确解决方案。

EDIT: don't forget that your final users can make profit of the runtime version of Access, which is free!

编辑：不要忘记您的最终用户可以从 Access 的运行时版本中获利，它是免费的！

回答by Tim Lara

As others have suggested, I'd use the AutoExec macro in this case. If your code that checks the linked tables is currently a sub, change it to a function that returns TRUE if it succeeds. You can then use the "conditions" column in the AutoExec macro to exit the application with a user-friendly error dialog if the link table code fails. Your AutoExec macro could be something like:

正如其他人所建议的，在这种情况下我会使用 AutoExec 宏。如果您检查链接表的代码当前是子代码，请将其更改为成功时返回 TRUE 的函数。然后，如果链接表代码失败，您可以使用 AutoExec 宏中的“条件”列退出应用程序并显示用户友好的错误对话框。您的 AutoExec 宏可能类似于：

1. Call your LinkTables() function, set condition to terminate startup if it fails.
2. Call your "Main" startup menu form.

1. 调用您的 LinkTables() 函数，设置条件以在失败时终止启动。
2. 调用您的“主”启动菜单表单。

回答by Meri

You could send them a *.BAT file that copies the databases to c:\temp (or whatever folder you choose. Setup the linked to this folder before creating the BAT file. Zip it up and email it to them. then you won't have to worry with the extra needed code.

您可以向他们发送一个 *.BAT 文件，该文件将数据库复制到 c:\temp（或您选择的任何文件夹。在创建 BAT 文件之前设置到此文件夹的链接。将其压缩并通过电子邮件发送给他们。然后您就可以了”不必担心额外需要的代码。