data macro get username without vba

After much web surfing, I see data macros that reference VBA must have the VBA in the database that is making the data changes.  So if someone links to the database and makes data changes, we can't grab their user name unless they have the GetUserName function in their database.  

We think someone is changing data from another database but we can't tell who and we don't want to rely on everyone remembering to put the GetUserName function in every database they create or even make a reference to a database that has that function.

Has anyone come up with a solution that doesn't require VBA?
LVL 7
UniqueDataAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
That is not possible. If someone has been granted rights to the database file and has a tool to modify records it, it leaves no traces.
You'll have to monitor this at the server level via session and/or open files.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What version of Access? 2010 introduced the concept of data macros, which you may be able to use to log when the data was changed. As Gustav said, you can't really log who did it, but knowing when it changed might give you more information to determine who changed it.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Has anyone come up with a solution that doesn't require VBA? >>

As the others have said, with a JET/ACE db, there are none.  Tracking must be built-in.

However with something like SQL Server, you could use transaction logs to determine who is making changes.

The reason you can do that is that no client directly touches the database on its own, everything goes through one central process.

With JET/ACE, every client touches the database directly, so unless it's built into the app, there is no tracking.

Jim.
0
PatHartmanCommented:
You can control some things by making certain fields required and also by creating relationships and establishing RI.  In the BE, you need a table of authorized users.  In EVERY table, you need an UpdateBy column.  UpdateDT and CreateDT will also be useful.  Make the UpdateBy required and create a relationship with the Authorized users table and check the box to enforce RI.  That will force all Adds at least to log who made them and when.  I'm not sure what the capabilities of DataMacros are since I don't use them (I use SQL Server if I have requirements that must be implemented via triggers.) but you may be able to look at the pending update and determine that it has a userID destined for the UpdateBy field.  You don't actually have to validate it since the RI will handle that.  You just need to make sure it is present in the set of replacement data.
0
UniqueDataAuthor Commented:
I didn't think there were any workarounds when using a 2010 Data Macro but figured I would ask :)

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.