QODBC query very slow in MS Access |
Author |
Message |
|
Posted : 2008-06-01 02:15:15 |
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.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-02 08:10:49 |
The answer is simple, stop doing SELECT * from Tablename and using MS Access datasheets! Get ONLY the data you need, for example, not every invoice that ever was entered in QuickBooks, see: How do I use prompted date ranges in MS Access? |
|
|
|
|
Posted : 2008-06-03 10:17:42 |
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.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-03 11:30:56 |
|
|
|
|
Posted : 2008-06-03 11:35:15 |
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.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-03 11:40:53 |
|
|
|
|
Posted : 2008-06-03 12:18:20 |
I will try to deal with the optimizer again. Do you think it might help to uninstall QODBC and reinstall? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-03 14:55:44 |
No, you need to reset the optimized image. Most likely you tried to use both VB Demo and MS Access at the same time or terminated them in the middle of building a optimized table. Optimized updates are single user only, you can't share a optimized image with other users on a network. |
|
|
|
|
Posted : 2008-06-05 07:23:24 |
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??? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-05 07:57:06 |
For MS Access 2003 or 2007 to work correctly you must first check that you are running QODBC v8.0.0.240 or higher (see: How can I get the latest version of QODBC? ) and then change your optimization settings to use the The start of every new connection (with "Load Updated Data" first) option and confirm that the Multi-Table Sync option has been disabled. If not, uncheck the Multi-Table Sync option as this is often defaulted on (checked) with QuickBooks Enterprise and the QODBC Enterprise Desktop Edition.
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? |
|
|
|
|
Posted : 2008-06-05 08:54:55 |
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. |
|
|
|
|
Posted : 2008-06-07 03:02:44 |
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.
|
|
|
|
|
Posted : 2008-06-10 00:17:15 |
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? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-10 08:38:29 |
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. |
|
|
|
|
Posted : 2008-06-10 10:50:17 |
I am wondering, once one workstation has been optimized, could you copy that .opt file to the user profile of another workstation? This would prevent having to wait for each workstation to optimize. That would be a big help. But I am suspecting that QODBC does not recommend that. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-10 11:28:00 |
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. |
|
|
|
|
Posted : 2008-06-10 12:52:13 |
when you say the same date and time, how close do you have to be on the time? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-10 14:18:32 |
It doesn't really mater as long as the last optimized time of the opt file is not ahead of the time of the new workstation. |
|
|
|
|
Posted : 2008-06-29 03:35:53 |
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.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-29 14:02:29 |
No, you should run the same query again after you first ran it to see if the query then runs optimized. The default settings I recommend will always update optimize on connection and then work on the optimized tables automatically afterwards. |
|
|
|
|
Posted : 2008-06-30 21:56:49 |
Tom, the only way I could get it to work was to delete the .opt file and then run the reload data option. It took a long time, but once it finished, I was able to get the Access queries to finish in a reasonable time. |
|
|
|