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'm going to read from and write to QuickBooks data file from VB6 application.

     So far I realized that QBSDK (QBFC) is not good enough to retrieve data I want. It doesn't allow to select only fields I need and to add criteria I want. But it is done pretty good with QBODBC. But I do not know how to enter new records into QuickBooks (I'm not talking about INSERT statement). QuickBooks and QBSDK use their internal stuff to create ListIDs and some other values for many fields. They also create 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 2 approaches to work with QB data - SDK to write to QB data file and QODBC to read from it. But in order to work that way I need to have 2 connections - 1 for SDK, another one for QODBC stuff. Creation of each connection takes about 20 seconds on my local machine. 40 seconds together. Is there any way to use one connection for both? It could decrease the time for connection.

Solutions 1

     Yes, just 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 you can add up to 500 transactions or inserts at the one time.

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).