Hi,

I've been very successfult with QODBC over the last two years, but started having a problem Monday this week.  It appears that the optimizer is not operating.  When I try a select statement from the timetracking table from either Access or VB .net 2005, it will show the optimizer block, count to the max number of items, and then promptly return nothing.  If I let it sit (5+ hours yesterday!), it will ultimately return all records in the database.  I turned on the Verbose Optimizer Status Panel option and the Display Driver Status Panel option.  After running through the optimizer process, it appears to go back to the first record and then slowly (as if not at all optimized) count through the records.  I have also tried this using the demo app, and have gottent he same results.  I have tried the SQL strings "select * from timetracking" and "select * from timetracking NOSYNCH" and have the same result.  The most significant change is that I upgraded from version 6 to version 7 of QODBC, but only did that today.

Thanks in advance for any help offered.

Trevor

The optimizer structure and options have changed, you may need to relink the tables in MS Access. Try opening a new .mdb or .accdb and see if the problem is still happens. See: How do I setup the QODBC Optimizer? Where are the Optimizer options? 

You should also try running:

sp_optimizefullsync ALL

to fully resync ALL the QODBC tables again (but switch off the Verbose Optimizer Status Panel and the Display Driver Status Panel options first!!!)

I appreciate your feedback.  I created a new MDB file and linked anew, reset the options related to the display, created a make table query using the same syntax,  ran it and this time, at least, I did not get the optimizer dialog, but it is still pig slow, as if the optimizer is not working at all.  Any further ideas?  Thanks in advance for your assist.

I apologize if I wasn't clear.  I'm using an in-house timetracking app.  After all the activities in the timekeeping program, it uploads the timetracking data into QB.  To prevent inadvertent duplicate entries and improve performance, I populate a temporary table with QB's timetracking table data and query against that data.  I had been running the SQL command "Insert * into QB_Timetracking from timetracking"  When I launched this SQL, the optimizer ran and counted up to the number of records (15808 as of last try) and then the local QB_Timetracking table was created and populated.  This process took about five minutes.

As noted in my original post, this process slowed down dramatically a bit over a week ago.  The optimizer no longer launched and the creation of the QB_Timetracking table took about 5.5 hours.  The only change at that point was that the optimizer stopped (yes, truly with no user interaction).  I was unable to get the optimizer to operate, so I came here looking for solutions.  .The first thing I did was to delete the contents of the database folder and recreate the database by reloading.  Still, the optimizer would not run and my query was no faster.  Still searching for a solution, I downloaded 7.10.00.231 (had been using a version 6, unsure of what particular build).  Since downloading and installing, I have tried to vary the optimizer options, relocate the optimizer database, reload data, load updated data, various tables, and countless other actions that I did not carefully document.

Here is a sampling of my experiences:

·         I can change the path for the optimizer database and empty the optimizer folder, rerun the reload all data and there will be a file created.  If I go back to the control panel, the path for the optimizer file is set back to the original %UserProfile% location.

·         I can force the optimizer to run by using either SP_optimizefullsync timetracking or the SP_optimizeupdatesync timetracking.  Both appear to operate as anticipated.

·         Executing Queries like “select * from timetracking NOSYNC” take hours to complete even if executed immediately after SP_optimizefullsync timetracking.

·         I can force the optimizer to operate by including its command in the SQL command, such as “select * from timetracking VERIFY where txndate >= {d ‘2007-06-01’}” or “select * from timetracking VERIFY”.  These commands launch the optimizer, it does its thing and then the command executes as if it were not at all optimized.  These commands will run for hours.

·         The SQL command “select * from timetracking VERIFY WHERE Txndate >= {d '2007-06-01'}” ran the optimizer and then took one hour and twenty minutes to complete.

·         Each record in the QODBC messages file includes the statement “Optimizer Passthrough Disabled Unknown Reason”

·         I have tried these commands from the VB Demo program, from access using linked tables, from access using pass-through queries, and from code in VB .net with consistent results.

·         My problems may have been coincident with turning on the option to host multi-user access.

·         Despite multiple settings for the chunk size, the optimization still counts in 250 record increments

·         A query (select * from timetracking VERIFY where txndate >= {d ‘2007-06-01’}) returned 1253 records and took 25 minutes, after optimization and when queried using a jumpin field.  This query also had the optimize passthrough disabled line

·         I changed the location for the optimization database back to the default, deleted the entire tree the optimization database had been in, rebooted, and tried a full optimization from the control panel.  The directory tree I had just deleted and that was no longer even set to be the database location was recreated and populated with an optimization database.

I do not need to export the entire QB database, although I may at some point in the near future.  Right now, my sole concern is getting this operation to complete in a timely manner.

I hope this diatribe has adequately outlined my challenges.  However, if there is some specific information you are lacking, please give me a specific listing and I will get the info to you forthwith.

Thanks in advance for your assistance.  Given the number of posts you appear to handle, it cannot be easy to provide assistance to all the users.

Did you happen to remember to purchase a new CD Key for QODBC v7? As far as your optimizer issues are concerned, products like MS Access store the initial connection string including the "OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer" location, so if you change the optimizer folder later on, MS Access will still use the original OptimizerDBFolder and everything reappears when you open the mdb even when you have already deleted the entire optimizer tree.

Basically you are going around in circles here. Stop and take it one step at a time. %UserProfile% was introduced in QODBC v7 in an effort to keep a seperate optimizer files for each user (the QODBC optimizer is single user only) and when you installed 7.10.00.231 the installer reset all the defaults for QODBC.

You need to recheck your optimizer settings as per: How do I setup the QODBC Optimizer? Where are the Optimizer options?