Implementing an audit trail for an internal app using Access and SQL Server

Data and Databases
1

Hey everyone! I’m working on a project for my company. We’re making an internal app using MS Access as the front end and SQL Server as the backend. I’ve got most of it figured out, but I’m stuck on how to set up a good audit system.

We’re not using logins since it’s only for internal use. The app automatically grabs the Active Directory user when they’re on our network. What I want to do is track who does what and when in the database.

I’m thinking of using a table with columns for the user, timestamp, and the change made. But I’m not sure about the best way to implement this. Should I use triggers in SQL Server? Or should I handle it in the Access code?

I know triggers would be more foolproof, but I’m not sure how to get the AD user that way. Doing it in Access seems easier, but then it might miss changes made outside the app.

Anyone have experience with this kind of setup? What would you recommend? Thanks for any tips!

2

hey swiftcoder, I’ve dealt with similar setups. triggers in SQL Server are generally more robust, but you’re right about the AD user challenge. for internal apps, i usually go with Access-side auditing. it’s simpler and covers most scenarios. just make sure to lock down direct DB access to prevent bypassing the audit trail. good luck with ur project!

3

hm, interesting project SwiftCoder! have u considered a hybrid approach? maybe implement basic auditing in Access for most operations, but use SQL triggers for critical tables? that way u get the best of both worlds. also, culd u share more about the specific actions u want to audit? might help brainstorm some creative solutions!