QODBC: Updating QuickBooks doesn’t update Microsoft® Access

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

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Join & Write a Comment

Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month