Creating a tracking system: MS Access front-end with SQL Server back-end

Hey everyone,

I’m working on an app for my company. We have to use MS Access for the front-end and SQL Server for the back-end. I’ve got most of it planned out, but I’m stuck on how to set up a good tracking system.

Here’s the deal:

  • It’s only for internal use
  • No login system needed (we use Active Directory)
  • We can grab the AD user name automatically
  • The tracking table will have 3 columns: who, when, and what

I’m not sure what’s the best way to handle this. Should I use a trigger in SQL Server? But then how do I get the AD user info? Or should I just code it in Access? But that might miss some changes.

Has anyone done something like this before? Any tips or ideas would be super helpful!

Thanks in advance!

ooh, interesting project! have u thought about using SQL Server’s Change Data Capture? it’s pretty nifty for tracking changes without messin with triggers. but yeah, getting AD info might be tricky. maybe u could use a CLR function in SQL to grab the AD username? just brainstorming here. what other ideas have u considered?

hey haz, i’ve done similar stuff before. SQL triggers r good for consistency, but can be tricky with AD. maybe try a combo approach? use access VBA to grab AD info and pass it to a stored procedure in SQL. that way u catch all changes and keep AD stuff outta SQL. just my 2 cents!

For a robust tracking system in your MS Access and SQL Server setup, I recommend implementing it at the database level using SQL Server’s built-in auditing features. You can create an audit specification that captures INSERT, UPDATE, and DELETE operations on your tables. This approach ensures comprehensive tracking without relying on front-end code.

To incorporate AD usernames, create a function in SQL Server that retrieves the current user’s AD name. Then, use this function in your audit specification or in a custom trigger if you need more granular control. This method provides a centralized, reliable tracking mechanism that works regardless of how data is modified, whether through Access or direct database interactions.