Who changed the Data
Who changed the data? This question comes up when something found not in order with the information kept in the database and ended up with serious issues reaching out to too many. 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 brighter light.
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 with different Category Codes. Periodically a Statement on their Payment Over Dues will print and forward to the parties as a regular follow up measure, to remind them to make payments or to check and reconfirm 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 forward it to the Parties, as per strict instructions from the top.
Several individuals in Accounts Department are involved in updating the database. Then one day someone edited one of the VIP Account Category Code by mistake and the Account landed in the common category and the monthly statement goes out without knowledge of it, again by mistake. As far as the common category is concerned it is a routine work to transport it to the destination. The remaining story you can add here, if needed.
The investigating committee pointed their fingers on each one of the 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 going 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 finger print on the record crystal clear, whether the change was intentional or not, is a different issue altogether.
If you are from 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 is a story, but you cannot rule out the possibility altogether.
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 a MS-Access Database it 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 about the finger printing (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, which is very useful to sort 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 on which record she has made the wrong change, even if she is aware that something went wrong. With the aide of approximate time and date the user herself can find the record involved and correct them, if she is given the facility to do that.
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:
Click on the DEDate Field. Enter the expression =Now() in the Default Value property of the field at the bottom. The EditDate and EditedBy fields we will update every time a new record is added or when changes 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 don’t know how to find it.
If you don’t want the user to use this field any way 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, with 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 VB Module of the Form (View - > Code), copy and paste the following code into the module and save the Form:
The 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.
Even this method is not 100% fault free. For example, if someone else edited that record changing some other field, after the error has happened, her name will be recorded overwriting the actual person made the mistake. Then we will be catching an innocent person for somebody else wrong doing.
Then we have to consider keeping a history of changes made to the records and each edited record must be copied to a history file with Date, Time and User ID.
Dynamic Report
MS-Access & Mailmerge-3
MS-Access & Mailmerge-2
MS-Access & Mail-Merge
MS-Access Object Documenter
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 with different Category Codes. Periodically a Statement on their Payment Over Dues will print and forward to the parties as a regular follow up measure, to remind them to make payments or to check and reconfirm 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 forward it to the Parties, as per strict instructions from the top.
Several individuals in Accounts Department are involved in updating the database. Then one day someone edited one of the VIP Account Category Code by mistake and the Account landed in the common category and the monthly statement goes out without knowledge of it, again by mistake. As far as the common category is concerned it is a routine work to transport it to the destination. The remaining story you can add here, if needed.
The investigating committee pointed their fingers on each one of the 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 going 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 finger print on the record crystal clear, whether the change was intentional or not, is a different issue altogether.
If you are from 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 is a story, but you cannot rule out the possibility altogether.
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 a MS-Access Database it 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 about the finger printing (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, which is very useful to sort 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 on which record she has made the wrong change, even if she is aware that something went wrong. With the aide of approximate time and date the user herself can find the record involved and correct them, if she is given the facility to do that.
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 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 EditedBy fields we will update every time a new record is added or when changes 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 don’t know how to find it.
If you don’t want the user to use this field any way 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, with 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 VB 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 Sub
The 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.
Even this method is not 100% fault free. For example, if someone else edited that record changing some other field, after the error has happened, her name will be recorded overwriting the actual person made the mistake. Then we will be catching an innocent person for somebody else wrong doing.
Then we have to consider keeping a history of changes made to the records and each edited record must be copied to a history file with Date, Time and User ID.
Dynamic Report
MS-Access & Mailmerge-3
MS-Access & Mailmerge-2
MS-Access & Mail-Merge
MS-Access Object Documenter
Labels: msaccess tips
posted by a.p.r. pillai
at 2:41 PM
FEATURED LINKS
1 Comments:
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).
Post a Comment
Note:Comments subject to Review by Blog Author before displaying.
Links to this post:
Create a Link
<< Home