Is there a way to execute VBA code when specific tables and fields in a Microsoft Access database are updated?

Question

I have a program that uses a Microsoft Access database for its internal server. I need some VBA code (which calls the web service) to be executed whenever programs have updated certain tables / fields. I see that this works as a trigger in SQL Server.

Can I track and change such changes in Access?

Update The program in question does not start inside Access (i.e., not a VBA application), it simply uses the MDB file as its internal storage. Unfortunately, I do not have access to the program code, since this is a closed third-party application.

Answer 1

Accessing a database vs GUI vs Jet GUI is a different thing.

If you use the Access database as a backend, it's just the JET functionality you can work with. Access to the graphical interface (which includes VBA) is performed on the client machine, and there is no way to trigger triggers automatically.

Answer 2

This question is old, but the answers are no longer correct. Access 2010 has added data macro events that can be triggered when data is inserted, updated, or deleted. The following events are available when using a table view of a table or a view of a table (events are attached directly to the table, and not through the form macro button):

• After removing macro definition
• After the "Insert Macro" event
• After updating macro definition
• Before changing the macro definition
• Before deleting a macro

Additional information is here:

• https://msdn.microsoft.com/en-us/library/office/dn124692.aspx
• https://support.office.com/en-us/article/Create-a-data-macro-b1b94bca-4f17-47ad-a66d-f296ef834200

Answer 3

If your program is the only program using an access file, then it should know when the table is updated and executes some code instead of a trigger.

Otherwise, you will need another application / service that runs all the time, checking the access file tables for updates (maybe you have a field like update_date in your tables?).

Answer 4

When a record is added to an Access database file, the date / time stamp changes. I suppose you could try using a file monitor to detect changes in the file, and then examine the file to see what has changed.

This will help if the Access database has LastModified date / time tables in the tables.

Answer 5

If you use Jet (i.e. data is stored at the end of the MDB file), then the only places where you can run the code will be the After Update event on the form. The problem here, of course, is that if the data is changed without using a form, the event will not fire.

If you are using MS Access 2003, you can download the Microsoft Office 2003 web services toolkit to start the web service. Click here to download

Answer 6

If you get stuck in VBA, it gets a little rude. One way is to have a form with a timer in it (you can open it invisibly. The timer can check the table, say, once a minute (or any suitable interval) to change the number of records and check the table still exists (code below)

But personally, I do not recommend doing this. Access is known as corruption. When used as a simple back, you are pretty safe most of the time, but to start the monitor means that the file is always open. This is basically a Russian roulette game with your database. At a minimum, I would contact your database from another Access file and track the related tables, so if your monitor fails, you do not bring a production database with you. Finally, make sure you don't request too often, as I would not want you to be the only reason for the website timeout :)

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