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 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.

Is it possible to monitor for and act upon changes like this in Access?

Update The 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.

Answer 1

Access the GUI environment vs Jet the database format are separate things.

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.

Answer 2

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.

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?).

Answer 3

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.

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

Answer 4

When an Access database file gets written to, it's date/time stamp changes. I suppose you could try using a file monitor to detect changes to the file, and then examine the file to see what has changed.

It would help if the Access database has LastModified date/time columns in the tables.

Answer 5

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)

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:)

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