Introduction.
Who changed the data? This question comes up when something is found not in order with the information kept in the database and ended up with serious issues affecting many areas. Here, I didn't give out much to make it clearer to you, and wondering what this is all about. Let us look into a scenario to bring the story into a clear view.
Assume that there is a database on Credit Customers of the Company, which includes high profile Credit Parties from Ministries or Ministers themselves. All Credit Parties are grouped into different categories based on their status or other credibility criteria and assigned to different Category Codes. Periodically a Statement on their Payment Over Dues will print and forward to the Credit parties as a regular follow-up measure, to remind them to make regular payments or to check and confirm that the statement is in order.
Since high-profile parties are kept under different Category Codes, their Statements are printed separately for internal record purposes only, but never forwarded to the Parties, as per strict instructions from the top.
Several individuals in the Accounts Department are involved in updating the database. Then one day someone edited one of the VIP Account Category Codes by mistake and the Account landed in the common category and the monthly statement goes out to the VVIP party. As far as the common category is concerned, it is routine work to transport the statement to the concerned so that money can be recovered from them in time. But, the statements pertain to the VVIP categories, if delivered can put the Company into tight spots and can cause much embarrassment to the Management.
The investigating committee pointed their fingers at each one of those supposed to be responsible for the change, but nobody came forward hanging their head low. Finally, EDP Department has been called up and the poor Application developer has to find the answers quickly or his head is likely to roll. After all, he can change any data in his Application. But he was already prepared for such eventualities and had ready answers with him to save his skin. He caught the culprit red-handed having left his fingerprint on the record crystal clear, whether the change was intentional or not, is a different issue altogether.
If you are from the Accounts Department, I know you have so many questions to ask and you may even argue such a thing can never happen in your department. I already mentioned it as a story on mere assumptions, but you cannot rule out the possibility altogether.
User/Date/Time Stamps
Data Entry and Editing are two major functions that involve maintaining information up-to-date. Field-level validation checks are performed to maintain reasonable accuracy of the information fed into the System. Present-day inventions like Scanners, HHTs (Hand Held terminals), and other devices are also used. In these devices also the User IDs, Date and Time, etc., can be recorded as part of the information fed into computers.
When several Users are involved in using and updating information in MS-Access Databases, they should be installed in a Network, implementing strict MS-Access Security under Workgroups. Users must be organized into different Workgroups based on their activity with the database and allow them to Log-in with their own User ID and password limiting the activity within their own privileges.
We were discussing the fingerprinting (I coined this phrase here all by myself) of the data editing event on the record. When a new record is entered into a table or during the editing session we can record the date, time, and User ID into that record itself, which is very useful to sort the records or find the information entered or edited on a particular day or within a time period. Inadvertent change to the data can happen and the user may not remember which record, she has made the wrong change, even if she knows something went wrong somewhere. With the aid of the approximate time and date, the user herself can find the record involved and correct them, if she is given the facility to do that.
Add extra Time Stamp Fields to the Table.
While designing the Table add the following three fields at the end of the field list to record the Data Entry Date-Time, User ID, and record Edited Date-time:
| Field Name | Data Type | Size |
|---|---|---|
| DEDate | Date/Time | |
| EditedBy | Text | 20 |
| EditDate | Date/Time |
Click on the DEDate Field. Enter the expression =Now() in the Default Value property of the field at the bottom. The EditDate and Edited By fields will update every time a new record is added or when changes are made to it on the Form.
While designing the Data Entry/Edit Forms place the above fields also on the Form, at a convenient location. Display the Property Sheet of the fields and set the Locked property of the above fields to Yes so that the user will not change the values manually. Set the Tab Stop property to No so that during normal data entry or editing time the cursor will not move into these fields.
Even if you set the Locked property to Yes the user can click on this field and sort the data and find the records she has edited recently and correct the mistakes, if she knows about it, but doesn't know how to find it.
If you don't want the user to use this field anyway but let her see the contents, then set the Enabled property of the field to No and reset the Locked Property to No. If you plan to keep these fields hidden from the user then set the Visible property of the fields to No.
Now, by setting up a simple Before Update Event Procedure we can start recording these values on the table. While the Form is still in design view, display the VBA Module of the Form (View - - > Code), copy and paste the following code into the module, and save the Form:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!EditedBy = CurrentUser
Me!EditDate = Now()
End SubThe DEDate field will record the current date automatically when a new record is created and the above procedure will record the Current User ID and Current Date and Time as the final step of the edit action of each record.
To Avoid further Pitfalls
Even this method is not 100% fault-free. For example, if someone else edited that record by changing some other field after the error has happened, her name will be recorded overwriting the actual person who made the mistake. Then we will be catching an innocent person for somebody else wrongdoing. We may need to come up with more ideas, like adding more fields to update up to 5 editing events, if more editing then overwrites the oldest editing event, etc.
OR
Consider keeping a history of changes made to the records and each edited record must be copied to a history file with the Date, Time, and User ID.
Earlier Post Link References:
I dealt this issue by sacrificing the ID Key (unique key) field & added these fields: 'status', 'date_time', 'user'. Status = 1 (active) and 0 (no longer active). This way, I know who did what & when; plus I know how the data look like at certain time period. This is very important for auditing & troubleshooting. But as I said, I have to sacrifice uniqueness (and not to forget, the database size also increase).
ReplyDelete[...] am sorry about the Error, please find the correct link below: Who changed the Data __________________ http://www.msaccesstips.com (Learn MS-Access Tips & [...]
ReplyDelete