ADO - ADO Field OriginalValue and UnderlyingValue Properties for Tracking Data Changes

 
 

1. Introduction

In ActiveX Data Objects (ADO), the Field object represents an individual column within a database record. Each field contains information such as a customer's name, employee ID, salary, address, or product price. When users modify database records, it is important to understand the difference between the original data, the modified data, and the actual value stored in the underlying database.

ADO provides two important properties, OriginalValue and UnderlyingValue, to help developers track changes made to field values. These properties are particularly useful when working with disconnected Recordsets, where records are retrieved from a database, modified locally, and updated later.

The OriginalValue property represents the value of a field before the changes were made, while the UnderlyingValue property represents the value currently stored in the underlying data source as known to ADO. By comparing these values with the current field value, developers can identify modifications and understand whether database records have changed since they were retrieved.

2. Understanding the OriginalValue Property

The OriginalValue property returns the value of a field when the record was initially retrieved from the database or when the last successful update was performed.

It helps developers identify what information existed before a user modified a record. This is useful in applications where maintaining a history of changes is important.

For example, consider an employee database containing the following information:

  • Employee ID: 101

  • Employee Name: Rahul

  • Department: Sales

  • Salary: ₹30,000

Suppose an administrator changes the employee's salary from ₹30,000 to ₹35,000. The current field value becomes ₹35,000, but the OriginalValue property continues to represent the previous value of ₹30,000 until the update is successfully completed.

This allows developers to compare the old and new values and determine exactly what information has been modified.

Main uses of OriginalValue:

  • Identifying the previous value of a database field.

  • Tracking modifications made by users.

  • Maintaining audit information about record changes.

  • Supporting data validation before updating records.

  • Comparing original and modified information.

3. Understanding the UnderlyingValue Property

The UnderlyingValue property returns the current value of a field in the underlying database as known to the ADO Recordset.

This property is particularly useful when multiple users access and modify the same database records.

For example, imagine that two administrators are working with the same employee record. Both retrieve the employee's salary as ₹30,000.

The first administrator changes the salary to ₹35,000 and successfully updates the database. Meanwhile, the second administrator still has the older record in their application and attempts to change the salary to ₹32,000.

The UnderlyingValue property can help identify the database value that ADO knows about, allowing the application to compare it with the original and current values.

This is useful for detecting possible conflicts when multiple users modify the same information.

Main uses of UnderlyingValue:

  • Checking the underlying database value.

  • Identifying potential conflicts during record updates.

  • Supporting multi-user database applications.

  • Comparing locally modified data with database information.

  • Improving data consistency during updates.

It is important to understand that UnderlyingValue does not necessarily perform a fresh database query every time it is accessed. Its value depends on the Recordset provider and the concurrency information available to ADO.

4. Difference Between OriginalValue and UnderlyingValue

Although both properties provide information about field values, they serve different purposes.

Feature

OriginalValue

UnderlyingValue

Meaning

Value originally retrieved or last successfully updated

Value in the underlying data source as known to ADO

Main purpose

Tracking previous data

Identifying database-side changes

Usage

Comparing old and current values

Detecting possible update conflicts

Importance

Change tracking

Data consistency

Application

Monitoring user modifications

Multi-user database management

For example, consider the following situation:

  • Original database salary: ₹30,000

  • Salary updated by another user: ₹33,000

  • Salary modified locally: ₹35,000

In a suitable update-conflict scenario, these values can help the application identify that the original value, underlying value, and current value are different.

This information helps developers decide whether to accept the local modification, refresh the record, or ask the user to resolve the conflict.

5. Practical Example Using ADO

Consider a simple employee management application developed using Classic ADO.

The application retrieves employee information from a database and allows administrators to modify employee salaries.

The following VBScript example demonstrates how to access the OriginalValue and UnderlyingValue properties of a field.

Dim rs
Dim oldSalary
Dim currentSalary
Dim databaseSalary

Set rs = CreateObject("ADODB.Recordset")

rs.Open "SELECT * FROM Employees WHERE EmployeeID = 101", _
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Company.accdb;", _
        3, 3

If Not rs.EOF Then

    oldSalary = rs.Fields("Salary").OriginalValue

    databaseSalary = rs.Fields("Salary").UnderlyingValue

    rs.Fields("Salary").Value = 35000

    currentSalary = rs.Fields("Salary").Value

    WScript.Echo "Original Salary: " & oldSalary

    WScript.Echo "Underlying Salary: " & databaseSalary

    WScript.Echo "Current Salary: " & currentSalary

End If

rs.Close
Set rs = Nothing

Explanation of the example:

  1. The application creates an ADO Recordset object.

  2. It connects to the employee database and retrieves the record of employee ID 101.

  3. The OriginalValue property retrieves the original salary.

  4. The UnderlyingValue property retrieves the underlying database value known to ADO.

  5. The application changes the employee's salary to ₹35,000.

  6. The Value property returns the currently modified salary.

  7. The application displays all three values for comparison.

The example demonstrates how developers can examine different versions of a field value before completing a database update.

The exact behavior of these properties depends on the OLE DB provider, cursor type, locking mode, and whether the Recordset supports the required features.

6. Role in Optimistic Concurrency Control

One of the most important applications of OriginalValue and UnderlyingValue is optimistic concurrency control.

Optimistic concurrency control is a database technique that assumes multiple users can access the same records without frequently modifying them at the same time. Instead of locking records continuously, the system checks whether the data has changed before applying an update.

For example, consider an online employee management system used by multiple administrators.

An administrator retrieves an employee's salary as ₹40,000. Before the administrator saves a new salary of ₹45,000, another administrator changes the same salary to ₹42,000.

If the first administrator updates the record without checking for changes, the second administrator's modification may be overwritten.

By comparing the original, underlying, and current values, an application can identify potential conflicts and prevent unintended data loss.

In Classic ADO, developers can also handle update conflicts through the Recordset object's Update, Resync, and error-handling mechanisms.

7. Advantages of Using These Properties

The OriginalValue and UnderlyingValue properties provide several advantages in database applications.

Better Change Tracking: Developers can identify differences between previously retrieved information and modified field values.

Improved Data Accuracy: Comparing field values helps applications detect unexpected modifications and reduce accidental overwriting.

Multi-User Support: These properties are useful when multiple users access the same database records.

Simplified Error Handling: Developers can examine field values when an update conflict occurs and decide how to handle it.

Reliable Database Management: They support applications that require controlled updates and accurate record management.

8. Limitations and Important Considerations

Although these properties are useful, developers should understand their limitations.

  • Their availability and behavior depend on the ADO provider and Recordset configuration.

  • They are primarily associated with updateable Recordsets and change tracking.

  • UnderlyingValue should not be treated as a guaranteed real-time database value.

  • These properties do not automatically resolve update conflicts.

  • Applications must implement suitable error handling and conflict-resolution procedures.

  • Developers should use appropriate transaction management when updating important database records.

9. Conclusion

The OriginalValue and UnderlyingValue properties are important features of ActiveX Data Objects that help developers understand and manage changes made to database fields. The OriginalValue property provides information about the previous field value, while the UnderlyingValue property helps identify the corresponding value in the underlying data source as known to ADO.

These properties are especially useful in employee management systems, banking applications, inventory management software, and other multi-user database applications. By comparing original, underlying, and current field values, developers can identify potential conflicts, maintain data consistency, and reduce the risk of unintended updates.