<

QODBC: Updating QuickBooks doesn’t update Microsoft® Access

Published on
3,326 Points
126 Views
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. 




2
Comment
0 Comments

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Join & Write a Comment

In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month