| Best way to optimize QODBC for Acess 2002 and invoice bill import into QB Enterprise 8 |
| Author |
Message |
| Mari |
| |
| Group | : Members |
| Posts | : 13 |
| Joined | : 2007-07-02 |
|
| Profile |
|
| Posted : 2009-02-17 09:13:28 |
Our client is using QB Enterprise 8 and QODBC 8 (the version ending with 244). Access 2002 application is used to import invoices and bills into QuickBooks. On the workstation where Access application is installed Quick Book Enterprise performance is very slow when access application is not running at all as far as generating and printing QB reports and other functions. On other workstations where QODBC is not installed it is considerably faster.
We checked and verified that there are no problems on the workstation where QODBC is installed, no problems with network access. QB Enterprise is also setup correctly as verified by QB support on this workstation and on the server. The only difference on this workstaion is that we have QODBC setup and it is slow even if access application which utilizes QODBC is not running at all. QB support think QODBC driver may be responsible for this slow performance.
What are the best settings to optimize QODBC in this case? Originally we tried to run it without any optimization and although it was not too fast this was not an issue. It became an issue only because QB itself is running slow on this machine. We tried to change the settings of QODBC to use optimization and set it up to The start of every new connection (with "Load Updated Data" first) option. We also checked Optimize data after an Insert or Update. But it run for about 8 hours to import 8 invoices and 8 bills. After checking the other posts in the forum, we unchecked Optimize data after an Insert or Update option and it ran faster then when optimization was not used.
Is this the best way to optimize importing invoices and bills into QuickBooks? Is there anything else that will make a difference in QuickBook performance itself? It seems that the performance of QB was improved a little bit after optimization was implemented although the access application was not in use.
Mari
RDIC
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-02-17 10:51:16 |
QODBC optimization has only to do with the reading of records. INSERTs create records in QuickBooks and the QODBC optimizer can be set to auto-update after the insert or not. When using INSERT batches, it's best to have the Optimize data after an Insert or Update option unchecked.
Reboot the computer, don't use QODBC and see what your QuickBooks performance is. The only reason why QuickBooks runs reports slow is if the the company file is in multi-user mode, a company file in single-user mode runs reports much faster. |
|
|
|
| Mari |
| |
| Group | : Members |
| Posts | : 13 |
| Joined | : 2007-07-02 |
|
| Profile |
|
| Posted : 2009-02-17 11:35:42 |
The access application that uses qodbc runs a few reports to show any missing customers, vendors, items by comparing with access data that needs to be imported and the only other function is to import invoice line items in batch, less then 100 invoices each time and less then 100 bill line items as a batch. Also after running the append query to add invoice line items, we run update query on imported invoices.
As I understand from your response using to optimization will not do much to make this faster. But it seems to me it may complicate the situation. Should we use optimization at all? It seems to me if for some reason the access application hangs the optimized database can get corrupt and cause problems. If you think we will gain from optimization which optimize settings you will recommend including advance settings for best performance
Quick Books is running in multiuser mode and it performs better on the workstations which don't have qodbc installed. The only workstaion that the client complains about slow QB is the one where QODBC is installed.
One other thing that I want to check is this. We tested QODBC 8 with QB Enterprise 8. Before going live with this access application they decided to upgrade to QB Enterprise 9 and QODBC didn't work. The client didn't want to buy QODBC 9 at this point, so they decided to go with QB Enterprise 8 release 5. They reinstalled this version and QODBC link worked as in the testing step. To troubleshoot the slow QB performance they reinstalled it again later. Is it possible that something after this install, reinstalls is messed up as far as qodbc is concerned? We also tried to upgrade to qodbc 8 (version 244) from (version 242) as recommended by your sales people.
One other thing that may be important in understanding what may the issue be is that in quickbooks it shows two connections setup for qodbc instead of one. Can you advise us if there's a need to reinstall qodbc completly or any other step that may help to restore QB performance to what it was before qodbc was installed on that machine.
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-02-17 12:41:39 |
Reboot the computer, don't use QODBC or MS ACCESS and see what your QuickBooks performance is!!!
You should also be using QODBC v9.00.00.253. See: How can I get the latest version of QODBC?
When using MS Access you will always see at least two QODBC Integrated Applications listed within QuickBooks. |
|
|
|
| Mari |
| |
| Group | : Members |
| Posts | : 13 |
| Joined | : 2007-07-02 |
|
| Profile |
|
| Posted : 2009-02-17 15:09:42 |
Tom, the client is not ready to spend extra money on the qodbc 9, they started using qodbc 8 only a few months although they bought it in may of last year. They will do this once they will need to upgrade to QB enterprise 9.
Can you please let us know which settings will be best to use with current version considering what are we doing with the access application. And should we use optimization at all?
Thank you ,
Mari |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-02-17 17:37:38 |
|
|
|
| Mari |
| |
| Group | : Members |
| Posts | : 13 |
| Joined | : 2007-07-02 |
|
| Profile |
|
| Posted : 2009-02-17 18:08:01 |
So you are saying that I can download qodbc 9 and install over existing qodbc 8 install without uninstalling it first it should work with QB Enterprise 2008 version. Or should we uninstall qodbc 8 compleltely then use the key we purchased on May 2008 to activate qodbc 9.?
Tom , what about the right settings? Those are the same in all versions as I understand. Should we use the optimizer in our case and if yes what settinga will be the optimal choice in this specific application of qodbc for importing invoice and bill batches from access 2002 into QB? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-02-17 18:11:09 |
|
|
|
