Knowledgebase: Data & Table
[QODBC-Desktop] Two Connections
Posted by brad waddell on 12 March 2009 05:21 PM

Troubleshooting: Two Connections

Problem Description 1

     I will read from and write to QuickBooks data file from the VB6 application.

     So far, I have realized that QBSDK (QBFC) is not good enough to retrieve the data I want. It doesn't allow me to select only the fields I need and to add the criteria I want. But it is done pretty well with QBODBC. But I do not know how to enter new records into QuickBooks (I'm not talking about the INSERT statements). QuickBooks and QBSDK use their internal stuff to create ListIDs and some other values for many fields. They also make some relations. For instance, adding a new Customer from QBSDK doesn't require to do much work.

     I came up with the idea to use two approaches to work with QB data - SDK to write to the QB data file and QODBC to read from it. But to work that way, I need to have two connections - 1 for SDK and another for QODBC stuff. Creation of each link takes about 20 seconds on my local machine. 40 seconds together. Is there any way to use one link for both? It could decrease the time for connection.

Solutions 1

     Yes, use QODBC!!! QODBC has an SQLite backend server with many additional indexes to what the QuickBooks SDK supports, so data can be retrieved much faster than calling QuickBooks directly. Also, QODBC supports batch inserts so that you can add up to 500 transactions or inserts simultaneously.

Batch Statements

For example:

BatchStart
sp_batchstart InvoiceLine

BatchInsert1
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('AC0000-1197757899', 'Batch1', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 0)

.............................

BatchInsert500
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('AC0000-1197757899', 'Batch2', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 0)

BatchUpdate
sp_batchupdate InvoiceLine

     And QODBC will commit all the inserts when the sp_batchupdate stored procedure is executed.

(186 vote(s))
Helpful
Not helpful

Comments (1)
Marika
20 September 2012 05:17 PM
Our data file is 230MB and our .tlg log file grows 300MB per day causing QB's prcofemanre to lag across our company LAN. We sent in our data file to QB's support team for a rebuild hoping this would resolve the issue but no such luck. Manual backup's do not reset the .tlg log file. There are allot of support blogs with screen grabs of technical how to's although they are mostly bunk. A current solution we've adopted is to rename the .tlg file and cut/paste it to our desktop. Once QB starts it creates a new .tlg file, but unfortunately the newly created file becomes bloated within a short time period.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).