QODBC: Updating QuickBooks doesn’t update Microsoft® Access

To solve the problem, let’s recreate the issue.

The Problem

First, we’ll open the QODBC Setup screen and:

    1.Click the Optimizer tab
    2.Check the box next to Use Optimizer.
    3.Select the Radio Button next to: The start of every Query.
    4.Click Apply and OK and close the window.


Now let’s look at the QuickBooks Mileage report. 

The Reports shows three entries for truck #188:

Now let’s open VB Demo that comes with QODBC and type an SQL Statement to retrieve the entries from truck #188:

Select TxnID, VehicleRefFullName, TripStartDate, TripEndDate,OdometerStart, OdometerEnd from VehicleMileage where VehicleRefFullName like ‘188%’

VB Demo returns the same three entries as the QuickBooks report:

Now let’s add a new mileage record:

Next, we return to VB Demo and click the Query button again. The new record is retrieved and there are now four entries for truck #188 in both QuickBooks and VB Demo.

Now let’s delete the new entry:


The QuickBooks report now shows only the original three entries:

But what happens in VB Demo? Return to VB Demo and press the Query Button again:

What happened? The deleted entry is still listed even if you disconnect and reconnect to QuickBooks.

If you were using Microsoft® Access to retrieve this data, you might think the code is faulty but it’s not.

The Solution

Open the QODBC Setup Screen and:

    1.Click the Optimizer button.
    2.Uncheck the box next to: Use Optimizer.
    3.Click Apply, OK and close the window.

Return to VB Demo and Disconnect from QuickBooks but leave the SQL Statement intact.

Reconnect to QuickBooks and press the Query button again.

Now the deleted entry is gone because you’re no longer using the Optimizer. You are not storing anything in a local store. You are pulling data directly from QuickBooks.

Now let’s try using the optimizer but selecting the Radio Button next to: Optimize data after an Insert or Update:

Be sure to click Apply, OK and close the window.

Now return to VB Demo, disconnect and reconnect to QuickBooks.

Click the Query Button again. The deleted entry is still listed.

Using the Optimizer speeds up retrieval but if you’re using a database to frequently update and delete data, leave this option unchecked to ensure the database is as current as possible.

You can try this for yourself using the QuickBooks sample file. Never experiment with your company file.