Week after week,  your optimizer somehow gets fouled up.  having to reload all data.  what works fine for a day stops working shortly thereafter. What I need is reliability.  Do I even need the optimizer to cache invoice lines and then pull in a header?  It would be great if I could sidestep your optimzer all together. 

Am I creating problems by moving the optimizer folder to C:\Program Files\QODBC Driver for QuickBooks\Optimizer so that anyone who logs on will access the same data file?  Or do I have to build an optimize table for each potential user separately?

Could I place the optimizer data file in C:\Documents and Settings\All Users\Optimizer ?

I still don't understand how I could have had it working one day,  and then none of them work a day or two later?  If it is that touchy then this product is of no use to us. Could you please answer all my questions?

First of all the QODBC Optimizer is single user only. You can't have two users (or even two applications for that matter) use the one optimizer file at the same time. That's why the default location of the optimized file now uses the %UserProfile% to make it unique for each user:-

So the location of the .opt tables is different for each user (or application by using different DSNs). Swap 'Tom' in the below with your login name:-

With large company files I never "Load" or "Reload" all the data as this simply isn't efficent and isn't required. The QODBC Optimizer will automatically optimizer only the tables you use and you can force the optimizer to even update after a insert if you need.

See: How do I setup the QODBC Optimizer? Where are the Optimizer options? and How do I create or configure a QODBC DSN? for more.

Lets try again.... First of all - on one client computer... how could I possibly have 2 users using the same optimizer file at the same time?  I am not trying to 'share' the opt file or otherwise locate it for more than one simultaneous user,  I was merely trying to have one file such that 'whoever' logged on to work at that station would be tapping into the one opt file.

The opt file takes 2 hours to build (if it doesnt hang up on the transaction file),  so If I have to set up each client for 5 users,  I have to spend 10 hours building all those opt files? then I have to repeat it on the other 2 computers (20 more hours).  I don't get this.

What do you mean it will only optimizer the tables that I use? Everyone is using the same application,  noone is using any tables outside of those which I set up.  Are you saying I should never execute the reload all data? I should never use those buttons?  the application only uses Customer,  ItemService, Invoice, InvoiceLine.

It (is supposed to) write to cache invoice lines, and then write the header and pull in the cached lines.  Its slow and leaves users in limbo long enough to cause problems.  You say some write 6000 invoice lines per day,  well I only want to do a few hundred, one at a time,  and it doesn't work very good, its slow.  Is it because I have violated the automatic optimization somehow?

How could I struggle with this for 6 months and you say its as simple as letting it put the opt's in the %user% and don't worry about loading the data? Would you please make this clearer.... The invoice writer is exactly as you say to do it,  simple as can be....  how do I set up the computers where it works reliably, consistently, smoothly.....

What is the difference between creating my own QODBC DSN versus using the default "Quickbooks Data'? 

I can have 4 users on one client (of course not at the same time - on different shifts).  this is why I wanted to centralize the opt file on each client,  so whoever last used it it would always be current, instead of having 4 opt files that are al the same on the one machine.  Are you saying this is not the proper thing to do?

I only need the Invoice, InvoiceLine tables for write operations.  The only other tables I use are Customer and ItemList and the get copied automatically into Access and they are fast.  It is the insert the invoicelines and header that slow.  

Should I be using Linked tables?  or should I open/close connections using ADO instead? What is the best way?

First of all, on a workstation users can login as another user and still keep the previous user logged in. The QODBC optimizer is not used in insert operations, so it makes no difference whether it's on or off. When you query a table, the QODBC optimizer will automatically mirror the data out of QuickBooks for that table to make it quicker the next time the table is used. When it comes to MS Access I always set up my optimizer like this:

So optimized tables are only updated when I first use them and not over and over again when I navigate up and down a datasheet or look up a customer or part to invoice. And I set optimize data after a insert to see the changes in MS Access (but only if I'm not using batches... as it's far better to force a optimizer update manually after a large batch insert instead).

To make invoicing faster I would use batches like this:

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) 

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

BatchInsert499
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

These allow you to start a batch for a given table and all insert/updates will be queued until the sp_batchupdate command is issued. This allows for fewer round trips to QuickBooks which increases performance when doing large transfers of records from external systems to QuickBooks.

The sp_lastinsertid stored procedure will return the ListID/TxnID plus an error message column for every row added to the batch.

Note: Each batch is limited to 500 transactions.

• sp_batchclear tablename – clears the current batch started with sp_batchstart.
• sp_batchstart tablename – starts a new batch. All inserts/update issued on this table will be batched until sp_batchupdate is issued.
• sp_batchupdate tablename – sends the batched transactions to QuickBooks. ListID/TxnIDs and error messages are available through sp_lastinsertid tablename.
  

See: Are there any special stored procedures that can assist me in programming? for more on batches.

You only need to setup different DSNs if there are going to be multiple applications running on the desktop. MS SQL Server users always need to setup a different DSN and optimizer location because if a desktop connection runs the optimized file will be corrupted.

Again use %UserProlfile% so your shift users don't corrupt the optimizer file for another shift user.

Tom,

I've seen this exact same issue -- 1 workstation, 2 different users who are never on at the same time (physically or even just logged on with "switch user"). 

Say I start with a clean optimizer directory.  User1 logs in and creates the .opt file just fine (though it may take almost an hour or so since we have over 14,000 customers.)  As they continue on, all works well with the optimizer caching information. 

Now, User1 logs off completely and User2 logs on for THEIR shift.  They attempt to use the same .opt file and they can't.  The QODBC errors indicate that the optimizer can't read the .opt file as it is corrupt or not an data file (exact wording escapes me right now).

So, is there coding in the .opt file indicating the user?  We've tried the default %userProfile%-based string and it invariably doesn't work -- creates NO .opt file and EVERY access to the QB file requires a full scan (of over an hour). 

Our only solution is to force each user to not share machines (which may mean extra QB and QODBC licenses)....,

Thanks,

-ed cetron

Tom,

Thanks for the quick reply -- I will update the one user to an Administrator on the shared workstation (not a domain administrator, just a local administrator) which is easy. 

I've also noticed since I just upgraded to V8 of QBES (and v 214 to v240), that the %UserProfile% functionality is working now -- but it goes to "c:/documents and settings/username/Application Data/QODBC .... " whereas the system environment variable is only "c:/documents and settings/username"  -- fascinating indeed.

However, one clarification to your response.  You said "The.opt file can't be shared. "  I understand it can't be shared AT THE SAME TIME. But can it be shared by two logins AT TWO DISTINCT TIMES (in single-user mode so to speak)?

Thanks,

-ed cetron
ecetron@tili.com