Password Protected Databases?

[lilmikey1982]

I am attempting to create a database of log records. I need the database to be password-controlled, and I can get that far. However it needs to be much more than that. In needs to have the following requirments. ANY advice you could provide, examples, wise words of wisedom would be VERY helpful!

1) Access to the database muyst be USERID and password controlled.

2) The system must restrict access to authorized personnel.

3) It must provide an audit trail that maintains a record of database access to include, as a minimum, time, date, and USERID.

4) It must maintain a record of changes made to data in a field after the data's initial entry.

5) It must include a search feature that allows a search of records in 2 specific fields.

Any ideas? Anyone dealt with something this complex before? Thanks a ton for all your help! You guys are awesome!

-Mike
MShirts@cdc.gov

 

[shacket]

Assuming you're familiar with VBA, I actually don't think that what you need to do is complex as much as it is time consuming. Items 1 and 2 can be accomplished through Access' user-level security.

Item 3 can be accomplished when the user logs in through Autoexec. Have a vba function that records the username (CurrentUser), time, date, etc. into a table every time they log in.

Item number 4 is also a lot of coding. You could create a function that records the data into a table and call that function (with the data changed and the username as variables) every time a field changes. You would have to place the function call behind every field you want to monitor.

Item number 5 merely consists of creating a SQL statement with the two fields and the search criterion. I tend to put this info in a listbox. So...

Me.ListboxName.RecordSource = "Select * FROM TableName WHERE [Field1] Like 'criteria1' AND [Field2] Like 'criteria2'"

HTH