Monitor who does what

[greggue]

I have a 2 tier system where the access database tables reside on a server and users edit the data with the "interface" through a map drive.

I have not set any "real" security except that the users only have access to forms and report. Who ever knows where to find the data on the map drive can easily access them although they know they are not supose to.

Resently I noticed many records missing and even changes to the data.



Is ther to find out who did what via an ip address or a network address?
I know how to set security by user group but since the damage has alrteady been done, I would like to find out who deleted the records.

[thekruser]

As far as figuring out who did hwat in the past, I have no idea how you would do that. However, I have recently had the same issue. I implemented the info found in this post:

https://www.accessforums.net/access/audit-log-7527.html

And now I am able to see who did waht and when. Might not help you in the past, but will work in the future. Hope that helps at least a little.

[pkstormy]

When I create an application, there are a couple of things I do to ensure I know what happens to the data and who did what. First, I will design my forms so that users cannot get to the backend. I do this using techniques I've posted in the code repository. Second, I return the user's loginID and record any actions done to a record with the user's loginID. Again, this is the GetUser technique found in the code repository. Third, I record dates of any transactions with a 'DateEntered' field in the table. And fourth, I implement a permissions type system (again, in the code repository) which only allows admin designated users the ability to perform certain actions. As far as deleting records, I may or may not implement a log type table to record records deleted. This becomes crucial when records are deleted directly from the table and are not recorded in the delete log table (then I know someone is bypassing the code.) It's also helpful if a record needs to be restored.

So for example, if a user clicks the 'Delete Record' button on the form, I will record who deleted the record and when in my delete log table. Keep in mind that it also takes time to put in coding and develop a 'delete log' type of table but if data is important.....

BUT, it only takes me a few minutes to use the Getuser() function and add 2 key fields to all my data tables: 1. EnteredBy and 2. DateEntered. and then set the default values on the form (ie. set the default value of EnteredBy to =GetUser() and the DateEntered to =Now().)

I will also sometimes add a: 1. ModifiedBy and 2. DateModified field and then when the form's record is updated, I populate these fields.

And if a user by chance somehow get's behind the forms (which I protect via popup/modal type forms) and that user enters data into the tables directly, I can usually find out sooner or later by seeing if key fields are not updated in the table which should be updated via code.

If this fails to tell me who or when a record is modified/deleted, since I use SQL Server, I can look at the SQL Server logs to see exactly what's happened and by who. If your data is critical and it's important to know what's happened with your data, I can't stress SQL Server enough.

You may want to browse through the code repository. There are a lot of good techniques you can find there which may be helpful. Once you learn a few tricks, it becomes 2nd nature to implement them and doesn't take that much additional work. I personally treat the data as if it were my own and use every trick I can to track everything that's happened with it. Again though, you have to invest some time up-front and use the right tools (ie. SQL Server) and learn the right tricks. I spent a lot of time looking at how others do it and then used their techniques as well as developed my own.