QODBC: Updating QuickBooks doesn’t update Microsoft® Access

Published on
3,533 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month