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!