QuickBooks ODBC (v2000)

Topic

New Reply

Hi Everyone,
I am using an ODBC driver to link Access to QuickBooks data. The problem is the performance of the linked tables and the vendor (QODBC) states it isn’t a problem with the driver but a problem with Access. The attached is from their web site and explains why the Access link is so slow.

>>>>>>>>>>
QODBC is a very fast ODBC driver. The QuickBooks interface SDK, which uses XML, is not the speediest file system available, but it does the job. The QODBC driver works with all front-end applications which can access ODBC, some of which are faster than others.

This question is almost never asked by our users using C as a front end language for ODBC, nor by report writer users with Crystal, or DBMS users with Delphi. These users will see immediately how fast the driver is because their applications are optimized for ODBC speed.

It is almost always asked by users of Microsoft Access, whose first operation is to open the largest file they have in their QuickBooks system in an Access Grid control and read it all into memory. They notice that the grid comes up quickly, but if they page down to the next screen-full of records, it takes a while. Why Access is slow compared to other systems can be easily explained.

Access is overloaded with multi-user functionality, meaning that features like updating all stations when a record changes on-line and other features are easily accomplished by the native Access file format.

When Access uses ODBC however, it attempts to do the same thing. For instance, when you open a multi-user grid on an ODBC data file, it must read the entire file into memory before continuing. This continues in the background, and when you press page-down, Access waits until the file has been completely read before beginning another query of the entire file, starting with the last record of the previous screen.

Other DBMS systems optimized for ODBC, such as Delphi, query only the number of records needed to fill the current screen with data, and does not continually refresh the database on the screen for no reason.
>>>>>>>>>>>

I have ideas on how to work around the performance issues (besides the ones they recommend) but does anyone else have experience with QODBC, does anyone have ideas to boost performance besides reducing the number of records the driver accesses at a time?
Cheers,
Dashiell

Reply | Quote

Replies

IMHO, that QuickBooks response is largely hocum. I’ve worked with ODBC extensively using SQL Server, and with tables containing 100K records or more, and the response is virtually identical with local tables. I should note that this is on a 100Mbit LAN with a fast dedicated SQL Server with lots of RAM.
[indent]

---

Other DBMS systems optimized for ODBC, such as Delphi, query only the number of records needed to fill the current screen with data, and does not continually refresh the database on the screen for no reason.

---

[/indent]
That’s all well and good if you are working on a single user system, but if someone else is updating data while you are looking at it, don’t you want to see the current data? I must admit I’m not a fan of QuickBooks, but putting this sort of stuff on a web site to defend a performance problem is poor form in my view. One thing you could tinker with the the update interval for doing refreshes on data – you’ll find that under Tools / Options.

Reply | Quote

IMHO, that QuickBooks response is largely hocum. I’ve worked with ODBC extensively using SQL Server, and with tables containing 100K records or more, and the response is virtually identical with local tables. I should note that this is on a 100Mbit LAN with a fast dedicated SQL Server with lots of RAM.
[indent]

---

Other DBMS systems optimized for ODBC, such as Delphi, query only the number of records needed to fill the current screen with data, and does not continually refresh the database on the screen for no reason.

---

[/indent]
That’s all well and good if you are working on a single user system, but if someone else is updating data while you are looking at it, don’t you want to see the current data? I must admit I’m not a fan of QuickBooks, but putting this sort of stuff on a web site to defend a performance problem is poor form in my view. One thing you could tinker with the the update interval for doing refreshes on data – you’ll find that under Tools / Options.

Reply | Quote

I have to agree with Wendell. Some of what they’re saying is just bizarre. What is a “multi-user grid”? Is this something peculiar to QuickBooks, because it isn’t some sort of native action in Access? Why would they be opening a large file in a grid control in the first place? Is there some kind of Access interface that QuickBooks provides? Otherwise that statement makes no sense, largely because Access doesn’t *have* a native grid control! If they are talking about a linked table, ODBC links are notoriously slow because of the overhead involved. How large is “large”, anyhow? Access displays large tables in chunks, depending on the sysytem resources available. If you’re talking hundreds of thousands of records, it will be slow regardless, even using a powerful machine.

The rest is laughable. C is a low level language. I would expect it to be fast, but programming a graphical interface in it is NOT fast or easy. Report writers don’t work the same way as a database, and Delphi is a server.

Reply | Quote

I finally had to toss in the towel. There is no Access app provided with the ODBC and I am working with less than 2,000 records and is why I was so shocked at the poor performance. I was looking for a seamless applcation that could read and write between QuickBooks but there is just no way. As a result, I instructed them to load certain fields in QB and others in the application. I run an update job nightly to sync the two which allows them to only enter data once.
Thanks to you and Wendell for the words of wisdom.

Reply | Quote

I finally had to toss in the towel. There is no Access app provided with the ODBC and I am working with less than 2,000 records and is why I was so shocked at the poor performance. I was looking for a seamless applcation that could read and write between QuickBooks but there is just no way. As a result, I instructed them to load certain fields in QB and others in the application. I run an update job nightly to sync the two which allows them to only enter data once.
Thanks to you and Wendell for the words of wisdom.

Reply | Quote

I have to agree with Wendell. Some of what they’re saying is just bizarre. What is a “multi-user grid”? Is this something peculiar to QuickBooks, because it isn’t some sort of native action in Access? Why would they be opening a large file in a grid control in the first place? Is there some kind of Access interface that QuickBooks provides? Otherwise that statement makes no sense, largely because Access doesn’t *have* a native grid control! If they are talking about a linked table, ODBC links are notoriously slow because of the overhead involved. How large is “large”, anyhow? Access displays large tables in chunks, depending on the sysytem resources available. If you’re talking hundreds of thousands of records, it will be slow regardless, even using a powerful machine.

The rest is laughable. C is a low level language. I would expect it to be fast, but programming a graphical interface in it is NOT fast or easy. Report writers don’t work the same way as a database, and Delphi is a server.

Reply | Quote