Who changed the Data

Introduction.

"Who changed the data?" — This question often arises when incorrect or inconsistent information is discovered in a database, especially when it leads to serious consequences affecting multiple areas of operation.

At first glance, the issue may not be obvious, and you might wonder what this is all about. To bring the story into focus, let’s consider a scenario that illustrates how such a situation can unfold.

Let’s assume there is a database that maintains records of the company’s credit customers, including high-profile clients such as Ministry officials or even Ministers themselves. These credit accounts are categorized based on status, credibility, or other internal criteria and are assigned specific Category Codes for easier classification.

Periodically, statements showing outstanding payments are generated and sent to these credit parties as part of a routine follow-up process. The purpose is to remind them to make timely payments or to verify the accuracy of the statement.

Since high-profile accounts are assigned special Category Codes, their statements are printed separately for internal records only, and never forwarded to the respective parties, as per strict directives from senior management.

Multiple individuals in the Accounts Department are authorized to update the database. One day, however, an employee accidentally altered the Category Code of a VIP account, inadvertently reclassifying it under the common category. As a result, the monthly statement was printed and dispatched to the VVIP party along with the regular batch.

For accounts under the common category, this is standard practice—statements are routinely sent to encourage timely payments. But when such a statement is delivered to a VVIP client, it can create serious complications. This misstep not only breached protocol but also exposed the company to potential embarrassment and reputational risk at the highest levels.

The investigating committee began pointing fingers at everyone who had access to the data, but no one stepped forward or admitted to the error. Tension mounted as the blame game escalated—until the EDP (Electronic Data Processing) Department was brought in. Now, all eyes turned to the poor application developer, who suddenly found himself in the hot seat. After all, he had full access to the system and the ability to modify any data. It was easy to assume he could be the one responsible.

Fortunately, he was prepared for such a situation. Anticipating that one day such a scenario might arise, he had already built mechanisms into the system to trace changes. Within minutes, he uncovered the truth—the real culprit had left behind a digital fingerprint in the record, unmistakable and undeniable. Whether the change had been made deliberately or by accident was a separate matter, but the evidence was clear.

If you're from the Accounts Department, you may have many questions and might even argue that such a mistake could never happen in your team. Fair enough—but remember, this is just a hypothetical story. Still, the possibility, however unlikely, cannot be ruled out entirely.

User/Date/Time Stamps.

Data Entry and Editing are two critical operations in maintaining an up-to-date and reliable database. To ensure data accuracy, field-level validation checks are implemented to prevent the entry of incorrect or inconsistent information. In modern systems, these functions are increasingly supported by advanced tools such as scanners, handheld terminals (HHTs), and other input devices. These technologies not only streamline data capture but also enhance traceability by automatically recording details such as User IDs, timestamps, and other metadata along with the entered data.

When multiple users are involved in accessing and updating data in Microsoft Access databases, it is essential to deploy the application on a secure network environment. This includes implementing Microsoft Access Workgroup Security, which allows the creation of user groups with specific roles and access levels. Each user should log in with a unique User ID and password, ensuring that their activity is restricted to the permissions assigned to their role.

In the context of maintaining data integrity, we earlier discussed what I like to call "data fingerprinting"—a method for tracking changes made to records. When a new record is added or an existing one is edited, the User ID, along with the date and time of the action, can be recorded directly within the record itself. This metadata serves multiple purposes: it helps in sorting records chronologically, tracking changes over time, and, most importantly, identifying who made a specific change and when.

Inadvertent edits are not uncommon, and the user responsible might not immediately recall the affected record. However, with time and date information captured, users can review recently modified records to locate and correct any errors, provided they have the necessary permissions to do so.

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 the record Edited Date-Time:

Field Name	Data Type	Size
DEDate	Date/Time
EditedBy	Text	20
EditDate	Date/Time

Setting Up Auto-Tracking for Data Entry and Edits

To automatically track when a record is added or modified, follow these steps:

1. Open the Table in Design View.

2. Click on the DEDate field (or the equivalent field meant to store the data entry timestamp).

3. In the Default Value property (bottom pane), enter the following expression:

   mathematica
   =Now()
   

   This ensures that the current date and time are recorded automatically when a new record is added.

The EditDate and EditedBy Fields will be used to track the timestamp and user information every time a record is edited. These values will be updated through a VBA event procedure.

---

Form Design Tips for Tracking Fields

When designing your Data Entry/Edit Forms, place the fields DEDate, EditDate, and EditedBy on the Form as well. Then:

• Set the Locked property of each of these fields to Yes, so that users cannot change the values manually.

• Set the Tab Stop property to No, preventing the cursor from navigating into these fields during normal data entry or editing.

Optional Display Settings:

• If you'd like users to see but not interact with the field (e.g., to sort or review their own edits), set:

  • Enabled = No

  • Locked = No

• If you prefer to hide these fields from users altogether:

  • Set the Visible property to No

These settings provide flexibility depending on whether users need visibility into their own change history.

---

Automating the Audit Trail with VBA

To record and edit information automatically, we’ll use the BeforeUpdate event of the form. This code captures the edit timestamp and the user ID each time a record is modified:

Steps:

1. While the Form is still in Design View, go to:

   • View > Code (to open the Form’s VBA module).

2. Copy and paste the following VBA code into the module:

   vba
   Private Sub Form_BeforeUpdate(Cancel As Integer)
       If Me.NewRecord = False Then
           Me![EditDate] = Now()
           Me![EditedBy] = CurrentUser()
       End If
   End Sub
   

3. Save the Form.

---

With this setup, any new or modified record will automatically capture the relevant audit information. This method is especially useful in multi-user environments where tracking accountability is essential.

To Avoid Further Pitfalls.

Limitations of the Simple Audit Trail

The BeforeUpdate() Event helps track who edited what and when. It's important to understand that this approach is not foolproof.

For instance, suppose a user unintentionally makes a critical error in a record. If someone else later edits a different field in that same record—perhaps as part of routine work—the original edit details will be overwritten by the second user's information. As a result, the actual source of the mistake is lost, and blame might fall on an innocent person.

This could lead to confusion—or worse, disciplinary action against the wrong individual.

---

Enhancing the Audit Trail: Suggested Approaches

To improve reliability and accountability, consider these two advanced solutions:

1. Multi-Level Edit Tracking

Add additional fields to the table to track multiple editing events—for example:

• EditDate1, EditedBy1

• EditDate2, EditedBy2

• … up to EditDate5, EditedBy5

Each new edit shifts the previous entries down by one level. When the maximum number of tracked edits is reached (say, 5), the oldest one is overwritten.

This approach allows administrators to trace back a limited history of changes and is helpful for short-term auditing.

2. Full Edit History Table

A more comprehensive and professional solution is to maintain a separate change history table. Every time a record is edited, the original version of the record is copied to this history table, along with:

• Primary Key ID

• Date and Time of Edit

• User ID

• Changed Field Names

• Original and New Values (optional but ideal)

This method enables a complete reconstruction of a record’s change history, offering transparency and accountability, particularly in sensitive environments like finance, legal, or healthcare systems.

---

Implementation Notes

• The history table can be populated using the Form_BeforeUpdate or Form_AfterUpdate events, depending on whether you want to capture data before or after the change.

• Use VBA code to compare the current values with the existing values in the record to detect changes.

• The history table can be indexed on PrimaryKey, EditDate, or EditedBy for quick lookup.

• Consider implementing user permissions that prevent unauthorized access to this history log.

Earlier Post Link References:

• Reminder PopUps
• MS-Access Graph Charts
• MS-Access Graph Charts-2
• Report Page Border
• Highlighting Reports

• Dynamic Report
• MS-Access & Mailmerge-3
• MS-Access & Mailmerge-2
• MS-Access & Mail-Merge
• MS-Access Object Documenter