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

Data and Databases
1

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!

2

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?

3

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!

4

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.