How to log data changes in Access at the table level?

Question

I support an Access database for a non-profit organization. We have some issues with data mysteriously changing, and there been some, um, friendly debate over whether this is caused by users who are not the most computer-literate or by some of my background processing.

I've added some checks at the UI level for these conditions, but I'd like to put some checks at the database table level to log when certain fields are changed. This way I'll know about the change whether it was initiated by a user or just from an update query running in the background. Normally I would use an on update trigger, but to my knowledge Access doesn't support triggers. Is there any other logging or trigger-like functionality available in Access?

Answer 1

Unfortunately, Microsoft Access doesn't have triggers, or anything even resembling triggers. Off the top of my head, you have a couple of unappealing choices:

• Create DateModified and UserModified columns, and update your code to include these values. The downside is that you'll only know the last person who updated a record, and won't have a history to look at..
• Keep your Access front end, and port your tables over to a SQL Server Express back end. This way, you'll be able to use triggers (as well as back-up jobs and other nice things).

Answer 2

In addition to LittleBobbyTables method, there's a third option, which involves making an _audit table for each regular table and writing a whole lotta logic to support them, so that when things get changed, you insert a record into those tables to do so. (you already know when you're saving/updating/deleting the records, yes?)

Granted, this is how "bigger databases" do it, the downside here is the lots of logic you have to do manually to simulate trigger-type work on a server.

Answer 3

Note that e.g. SQL Server Express is free and could potentially be a drop-in replacement for Access. This would give you the auditing functionality that you need. I don't think the "audit table" approach is viable; it assumes that anyone who connects to the database does so via the "official" client that has that logic in. With triggers there's no way to get at the data without them firing from any random client.

A third option is to Wireshark or similar to actually watch the traffic between the client and the database (I assume that this will actually be not TDS but file sharing protocol?) then you can see exactly who does what and when and from what machine. The downside to this is that it requires a lot of storage, some scripting to do the analysis, and may impact on performance.