[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


QODBC: Updating QuickBooks doesn’t update Microsoft® Access

Published on
3,637 Points
2 Endorsements
Last Modified:
Annaliese Dell
Find Annaliese on Amazon, Barnes & Noble and VBQuick.com.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.

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. 


Featured Post

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Join & Write a Comment

Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function. See how to call a wrapper function from a query, and …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month