Troubleshooting - VehicleMileage table showing deleted records

Problem Description:

I am using QODBC for working with QuickBooks. I can delete the VehicleMileage record. 

After deleting a record from the VehicleMileage table in QuickBooks, the deleted record continues to appear when querying the table through QODBC.

For example:

SELECT * FROM VehicleMileage;

The deleted record is still returned in the query results.

However, the following UNOPTIMIZED query returns the correct data:

SELECT * FROM VehicleMileage UNOPTIMIZED;

Solution:

This behavior in the "VehicleMileage" table is due to a limitation in the QuickBooks SDK.

QODBC's Optimizer relies on the TxnDeleted information provided by the QuickBooks SDK to identify and remove deleted transaction records from its optimized cache. Unfortunately, the QuickBooks SDK does not report deleted VehicleMileage records through the TxnDeleted mechanism. This issue is limited to VehicleMileage table and does not affect other tables.

As a result, QODBC is unable to detect that a VehicleMileage record has been deleted, causing the Optimizer to continue returning the deleted record.

VehicleMileage table issue is a QuickBooks SDK issue and is outside the control of QODBC. The issue has been reported to Intuit for resolution.

Resolution:

To work around this QuickBooks SDK limitation, QODBC Version 18.0.0.326 introduced a change to the VehicleMileage table.

Beginning with QODBC v18.0.0.326:

• The VehicleMileage table bypasses the Optimizer.
• Data is retrieved directly from QuickBooks.
• Deleted VehicleMileage records are no longer returned in query results.

No changes to your SQL queries are required after upgrading to version 18.0.0.326 or later.

Workaround for Earlier Versions:

If you are using QODBC v18.0.0.325 or any earlier version, use the UNOPTIMIZED keyword when querying the VehicleMileage table to retrieve the latest data directly from QuickBooks.

Example

SELECT * FROM VehicleMileage UNOPTIMIZED;

Using the UNOPTIMIZED keyword bypasses the Optimizer and returns the current records from QuickBooks, including the correct deletion status.

Summary:

• Affected Versions: QODBC v18.0.0.325 and earlier
• Cause: QuickBooks SDK does not report deleted VehicleMileage records through the TxnDeleted API.
• Fixed In: QODBC v18.0.0.326 and later
• Workaround: Use the UNOPTIMIZED keyword or upgrade to QODBC v18.0.0.326 or later.