Track Changes Made in an Access Database (User Log)

[murphy123]

Hi,

Is there a way to track when users make changes/or try to make changes to the design of an Access database? If yes, what are the steps? Do I have to code it into VB? How can I can get a database sample that can guide me through it?
Any help is appreciated.

Thanks.

 

[lynchg]

To the design or to the data?

You don't want users to have the ability to make design changes, and you should have security set up to prevent that.

If you are talking about data changes you can add audit fields to your tables to record when and by whom records were added or updated.

 

[murphy123]

The changes I want to be able to track are the changes to database design. Someone trying to change a table, create a query, or change a form in a database. Here is my issue... I need to give access to several users to enter data in some specific forms. However, someone with a small knowledge of access may be curious enough to get into the database window and try to play around. That's what I want to track. By saying that, I know that whenever I make changes to a table field, create report, query, etc. my user name should appear on this tracking log (report?). But if I see a different user name, I'll know who is getting into where they shouldn't be. I hope this clarifies.

Thanks.

 

[stix4t2]

hey mhoegg,

In tools/startup you can have access hide the db so they can't get in and play around with it. I have also seen where forms are created without borders and maximized to eliminate the menu. You could also hide the menu.

 

[murphy123]

I have my database already set up to allow only one tool bar, but if the person playing around with the database objects know how to allow the tools back on the database, they can easily add the menus and remove them. That's why I want to have the users tracked.


Thanks.

 

[stix4t2]

I would suggest using vba on open to record the state of the db if not present. Then test for changes as the program closes. You can loop through the objects and record or test modified date.

 

[murphy123]

Would you have any suggestions where I can get a database sample so I can get help coding this specific VB procedure? I'm not too familiar with VB, but I do have a fair understanding. If I have a database sample I would be able to incorporate the coding with my database.

Thanks.

 

[stix4t2]

I don't have an example, so if anyone else can help. Each of the tabs on the database have collections in the currentdb that allows you to go through each of the objects of the collection type.

For example
dim frm as form
dim db as database

set db = currentdb
for each frm in db.forms
...
next db

The same can be done for queries with querydefs. Take a look at the Access object model for a better idea of the names and parts.