| Access returns 0 records |
| Author |
Message |
| Gary |
| |
| Group | : Members |
| Posts | : 30 |
| Joined | : 2006-04-08 |
|
| Profile |
|
| Posted : 2006-10-17 06:03:14 |
Just upgraded to Ver7 QB Enterprise. Before upgrade was able to retrieve SalesOrderLine info using access 2000 with no problem. Now I get 0 records from my query.
I deleted the opt files.
Ugraded to Ver .194 rebooted with optimizer turned off. Turned optimizer back on. Went to my Access query and ran it. Told me it was going to paste "0" rows into my Make Table query. Never told me it was optimizing tables even though I deleted them. |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-10-17 09:00:20 |
| The default location of the optimized tables has changed to your login user profile folder, so existing connections might be hard coded wrong. It now looks like this:
ODBC;DSN=QuickBooks Data;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer
MS Access will store the OptimizerDBFolder connection string when you first link tables or setup queries. Try using VB Demo to check that QODBC is returning rows for your SalesOrderLine table. Try also opening a new Access mdb and relinking the table. |
|
|
|
| Gary |
| |
| Group | : Members |
| Posts | : 30 |
| Joined | : 2006-04-08 |
|
| Profile |
|
| Posted : 2006-10-18 03:13:47 |
I ran vbdemo and yes it found the records. I first uninstalled qbodbc and reinstalled ver .194.
Then I created a new ACESS mdb and linked to the Salesorderline table and ran a query for all records > 05/01/2006. It started the Optimizing dialog box. I never saw it start to count records.
0 records were returned. |
|
|
|
| Gary |
| |
| Group | : Members |
| Posts | : 30 |
| Joined | : 2006-04-08 |
|
| Profile |
|
| Posted : 2006-10-18 05:37:32 |
| I also searched my computer for the opt file and none were found. Something is keeping it from writing the file? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-10-18 09:12:12 |
Check the Criteria in your query, it should look like this: >#1/1/2006#
The SQL View of your query should have a Date filter that looks like this:
SELECT InvoiceLine.CustomerRefListID, InvoiceLine.CustomerRefFullName,
InvoiceLine.TxnDate, InvoiceLine.RefNumber, InvoiceLine.InvoiceLineItemRefFullName,
InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity,
InvoiceLine.InvoiceLineRate, InvoiceLine.InvoiceLineAmount
FROM InvoiceLine
WHERE (((InvoiceLine.TxnDate)>#1/1/2006#)); |
|
|
|
| Gary |
| |
| Group | : Members |
| Posts | : 30 |
| Joined | : 2006-04-08 |
|
| Profile |
|
| Posted : 2006-10-19 00:12:31 |
I was able to get it working by doing the following:
Backed up my company files to my "C" drive and restored the company from there; then ran my query from the local computer. Optimization dialog appeared optimizing SalesOrderLine and finally finished. Ran query again and it optimized again on Invoiced linked transactions. Ran again when that finished and results were once again "fast". Then I opened the Company file on the server drive and again results were great. I also found the optimized table when I included hidden files and folders in the Search. Now if only I could copy this file for other users rather than wait for hours and hours for this optimization to complete. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-10-19 09:34:31 |
You can copy the .opt file to other user folders. QODBC v7 now uses the login user profile folder. This is because the QODBC Optimizer is not multi-user. For me [Tom], my opt tables are found in C:\Documents and Settings\Tom\Application Data\QODBC Driver for QuickBooks\Optimizer. Just make sure QODBC is not active at the time and copy it to each users folder on each of their workstations.
|
|
|
|
