当 Microsoft Access 数据库中的特定表和字段更新时，是否可以执行 VBA 代码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/952196/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to execute VBA code when specific tables and fields in a Microsoft Access database are updated?
提问by joshuapoehls
I have a program that uses a Microsoft Access database for its back-end. I need to have some VBA code (that calls a web service) execute whenever specific tables/fields are updated by the program. I see this working just like a trigger in SQL Server.
我有一个使用 Microsoft Access 数据库作为后端的程序。每当程序更新特定表/字段时，我都需要执行一些 VBA 代码（调用 Web 服务）。我认为这就像 SQL Server 中的触发器一样工作。
Is it possible to monitor for and act upon changes like this in Access?
是否可以在 Access 中监视此类更改并对其采取行动？
UpdateThe program in question does not run inside of Access (i.e. not a VBA app), it simply uses an MDB file as its back-end storage. Unfortunately I don't have access to the program's code as it is a closed third party application.
更新有问题的程序不在 Access 内部运行（即不是 VBA 应用程序），它只是使用 MDB 文件作为其后端存储。不幸的是，我无权访问该程序的代码，因为它是一个封闭的第三方应用程序。
采纳答案by DJ.
Access the GUI environment vs Jet the database format are separate things.
访问 GUI 环境与 Jet 数据库格式是不同的事情。
If you are using an Access database as a backend - it's just the JET functionality you can work with. Access the GUI (which includes VBA) runs on the client machine and there is no automated trigger functionality.
如果您使用 Access 数据库作为后端 - 它只是您可以使用的 JET 功能。访问在客户端计算机上运行的 GUI（包括 VBA），并且没有自动触发功能。
回答by manotheshark
This question is old, but the answers are no longer correct. Access 2010 added data macro events that can be run when data is inserted, updated or deleted. The following events are available while using either the table datasheet view or table design view (events are attached directly to table and not through the form macro button):
这个问题很老了，但答案不再正确。Access 2010 添加了可在插入、更新或删除数据时运行的数据宏事件。使用表数据表视图或表设计视图时可以使用以下事件（事件直接附加到表，而不是通过表单宏按钮）：
- After Delete Macro Event
- After Insert Macro Event
- After Update Macro Event
- Before Change Macro Event
- Before Delete Macro Event
- 删除宏事件后
- 插入宏事件后
- 更新宏事件后
- 更改宏事件之前
- 删除宏事件之前
More information is located here:
更多信息位于此处：
回答by Robert Harvey
When an Access database file gets written to, it's date/time stamp changes. I suppose you could try using a file monitorto detect changes to the file, and then examine the file to see what has changed.
当一个 Access 数据库文件被写入时，它的日期/时间戳会发生变化。我想您可以尝试使用文件监视器来检测文件的更改，然后检查文件以查看更改的内容。
It would help if the Access database has LastModified date/time columns in the tables.
如果 Access 数据库在表中具有 LastModified 日期/时间列，这将有所帮助。
回答by JeffO
If your program is the only program using the Access file, then it should know when a table is being updated and execute some code in place of a trigger.
如果您的程序是唯一使用 Access 文件的程序，那么它应该知道何时更新表并执行一些代码来代替触发器。
Otherwise, you need another application/service running all the time that is checking the access file tables for updates (maybe you have some update_date type of field on your tables?).
否则，您需要另一个一直运行的应用程序/服务来检查访问文件表的更新（也许您的表上有一些 update_date 类型的字段？）。
回答by Mark3308
If you are using Jet (i.e. the data is stored in an MDB file back end) then the only places you can run code would be in the After Update Event in a Form. The problem here of course is if the data is changed without using the form then the event will not fire.
如果您使用 Jet（即数据存储在 MDB 文件后端），那么您可以运行代码的唯一位置将是表单中的更新后事件。当然，这里的问题是如果在不使用表单的情况下更改数据，则不会触发事件。
If you are using MS Access 2003 then to run a Web Service you can download the Microsoft Office 2003 Web Services Toolkit Click Here to download
如果您使用的是 MS Access 2003，那么要运行 Web 服务，您可以下载 Microsoft Office 2003 Web 服务工具包单击此处下载
回答by Oorang
If you are stuck in VBA it gets a little rough. One way to go would be to have a form with timer in it (you could have it open invisibly. The timer could check the table, say once a minute (or whatever interval seems suitable) for changes in record count, and verify the table still exists. (code below)
如果你被困在 VBA 中，它会变得有点粗糙。一种方法是有一个带有计时器的表单（您可以将其打开以隐形。计时器可以检查表格，每分钟说一次（或任何看起来合适的间隔）以记录计数的变化，并验证表格仍然存在。（下面的代码）
But personally this isn't what I would recommend that you do. Access is notorious for corruption. When used as a simple back end you are fairly safe most of the time, but to have it running a monitor, means the file is always open. This is basically playing Russian Roulette with your database. At minimum I would link to your database from another Access file and monitor the linked tables, that way if your monitor crashes, you don't take the production DB with you. Finally, make sure that you don't query too often, as I'd hate to see you be the sole cause of the website timing out:)
但就个人而言，这不是我建议你做的。Access 因腐败而臭名昭著。当用作简单的后端时，大多数情况下您是相当安全的，但是让它运行监视器，意味着文件始终处于打开状态。这基本上是用您的数据库玩俄罗斯轮盘赌。至少我会从另一个 Access 文件链接到您的数据库并监视链接表，这样如果您的监视器崩溃，您就不会随身携带生产数据库。最后，请确保您不要太频繁地查询，因为我不想看到您是网站超时的唯一原因:)
Option Explicit
Private m_lngLstRcrdCnt_c As Long
Private Sub Form_Open(Cancel As Integer)
Const lngOneMinute_c As Long = 60000
Me.TimerInterval = lngOneMinute_c
End Sub
Private Sub Form_Timer()
Const strTblName_c As String = "Foo"
Const strKey_c As String = "MyField1"
Dim rs As DAO.Recordset
Dim lngRcrdCnt As Long
If TableExists(strTblName_c) Then
Set rs = CurrentDb.OpenRecordset("SELECT Count(" & strKey_c & ") FROM " & strTblName_c & ";", dbOpenSnapshot)
If Not rs.EOF Then lngRcrdCnt = Nz(rs.Fields(0&).Value, 0&)
rs.Close
If lngRcrdCnt <> m_lngLstRcrdCnt_c Then
m_lngLstRcrdCnt_c = lngRcrdCnt
'Number of records changed, do something.
End If
Else
'Table is deleted, do something.
m_lngLstRcrdCnt_c = -1
End If
End Sub
Private Function TableExists(ByVal name As String) As Boolean
Dim tdf As DAO.TableDef
On Error Resume Next
Set tdf = CurrentDb.TableDefs(name)
If LenB(tdf.name) Then 'Cheap way to catch broken links.
Set SafeGetTable = tdf
End If
End Function
