I have read many of the posts here regarding slow connections and optimizing QODBC.  I have tried all different configurations of optimizer including no optimization and deleting the .opt file.

When I try to open a table in VB demo, it will open one with less than 1000 records, but it takes 2 to 3 minutes.  The optimization does not seem to matter.  When I try to open a larger table, it sits there with the icon in the taskbar saying "Waiting on Quickbooks"  Record 0 of 0 and Quickbooks also freezes up

I am trying to use to connect to Quickbooks data using Microsoft Access.  But that won't work at all.  If I can't connect with VB demo, I know Access won't work.

This is the 3rd or 4th machine I have installed QODBC on and I have had good success with other applications and machines.  But this one is driving me nuts.  I spent several hours trying to get it to work.  The customer has invested in Microsoft Access and the QODBC driver.  If I can't get this to work, I would have to buy back all of that.

Please carefully read my original post.  I stated that VBDEMO will not open the table and locks up Quickbooks.

As for the suggestion to limit the recordset returned in Access, I am currently doing exactly as you suggested.  Here is the sql statment:

SELECT ReceivePaymentLine.CustomerRefFullName, ReceivePaymentLine.TxnDate, ReceivePaymentLine.TotalAmount, ReceivePaymentLine.AppliedToTxnPaymentAmount, ReceivePaymentLine.AppliedToTxnRefNumber, [totalamount]*0.07 AS commission, Invoice.SalesRepRefFullName
FROM ReceivePaymentLine INNER JOIN Invoice ON ReceivePaymentLine.AppliedToTxnRefNumber = Invoice.RefNumber
WHERE (((ReceivePaymentLine.TxnDate)>=[Forms]![SalesmanReport]![Start Date] And (ReceivePaymentLine.TxnDate)<=[Forms]![SalesmanReport]![End Date]) AND ((ReceivePaymentLine.TotalAmount)<>0));

So can the problem has to be something else.

Thank you for any help you might be able to provide.

The join is incorrect, it should be: ReceivePaymentLine.AppliedToTxnTxnID= Invoice.TxnID

I would also guess that you most likely have corrupted your optimized image. Try reseting it as per: How do I switch OFF or RESET the QODBC optimizer? and reset the recommended optimizer options as per: How do I setup the QODBC Optimizer? Where are the Optimizer options? 

I'm thinking that you have lot's of invoices and payments, try running a more targeted select or report statement using the examples shown in: Differentiate between a payment and a discount to pay commissions 

OK, I will review procedures I am using in Access, but can you help me understand why VBDemo locks up and also locks up Quickbooks when I try to open a table.  This is not a large company so why do you think it will not open the invoice or payments tables in VBDemo?

Thanks for replying.

See also: SLOW performance using MS Access and QuickBooks 2008 Enterprise Edition 

I will try to deal with the optimizer again.  Do you think it might help to uninstall QODBC and reinstall?

Unfortunately, I'm experiencing the same problem. I'm using Access 2002 with QB Enterprise v8 on Qodbc 8.240.

I'm scrubbing data that querys a linked table. As soon as I pull a set of Invoices from a specific date range, Access slows down and Quickbooks just chokes. Task Mgr shows QB as Not Responding. After about 10 minutes I figured QB has had enough and just end the process.

Just want to be clear on this. You must have Optimized updates and it MUST be in SINGLE USER mode for a connection to occur???

Once this has been done, unlink all the QODBC tables in your existing MS Access .mdb(s) and then relink them to use these new connection settings.

For the complete details of all the QODBC optimizer settings, see: How do I setup the QODBC Optimizer? Where are the Optimizer options? 

Tom, I've actually checked the Optimizer settings before posting to the site. It is a mirror image of what was above. I've done some different changes including creating new Linked Tables and reset the Optimizer, but not in the order that you specified.

Other steps I've tried:

1. Moved Quickbook DB to local drive for faster access (Connection timeout)

2. Created a blank DB with 1 customer, 1 invoice, 1 sales order (Connection timeout)

3. VB Demo using 8.0.0.242 to just view INVOICE and INVOICELINE tables (Connection timeout)

There seems to be an issue with the new QODBC v8 but I will try the steps to reset the Optimizer in the order you recommend.

Finally, all is well with my problem.  I made sure I had deleted the .opt file for this user.  Then, I turned optimizer on and double checked the settings to be sure that it was as recommended.  Then, I reconnected the files in Access.  When I tried to open the invoice table, it started to optimize.  It took about 3 hours to optimize the invoice, invoiceline, and invoicetxn tables.  There were over 13,000 invoices with approx. 5-10 lines of detail for each invoice.  I am running on a network and the Quickbooks files are on the server.  The Access program and Quickbooks are running from a workstation.

Once I optimized, I am careful not to try to open the invoice table without running a query to extract only a subset of the data.  The query pops up in about 30 seconds for one month of data.

I am so glad I got this resolved.  I am just starting with QODBC.  Next time, I will anticipate more setup time in order to get tables optimized before starting any development work.

Previous use of QODBC involved a brand new setup of Quickbooks, so there was little data to optimize.  This job was much different.

Thanks for the update DBPro,

I also got 1 workstation up after optimizing the data. This took about 4 hours.

I need another workstation to pull queries and my question is whether I need to reoptimize the data? I'm having the same problem with the 2nd workstation as a backup. The 2nd system appears to lockup but it may just be trying to optimize the data again.

So is the *.opt file some kind of index for each workstation that needs to pull data?

Basically the QODBC Optimizer mirrors the QuickBooks tables into a SQLite engine. This SQLite engine is single user only, so each user, each workstation must have its own .opt file. When QODBC is installed it will automatically optimizer each table as you use it. You must allow QODBC to finish this initial optimization process. After that, updates are much faster.........

See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more information.

Actually you can copy the opt file providing all of these conditions are meet:

1) The workstations must have the same date and time.
2) The company file must be the same company file on a networked server.
3) There are no active QODBC connections at the time.
4) The QODBC optimizer settings are the same.

As of my last post, everything was working well with QODBC and the optimizer.  But then the client's machine died and I had to reinstall QODBC.

The first thing I did was to reinstall QODBC, set the optimizer settings as suggested, and then open the query I need so that it could rebuild the optimizer.

Now, every time I try to open the query I am using, it seems like it is re-optimizing.

Should I delete the .opt file and make it rebuild again.  It takes many hours to rebuild and client is getting aggravated.

Thanks for any help.