mi****@execpc.com (almish) wrote in message news:<95**************************@posting.google. com>...
Go**********@FatBelly.com (PeteCresswell) wrote in message news:<74**************************@posting.google. com>... Access tends to take over my machine when I run long queries. The
queries in question are Append queries.
Some of the jobs we're talking about run 45 minutes to an hour...so
the loss in productivity is significant.
When I pop TaskManager, it looks like it's only running 10-20%
capacity - so I'm guessing the horsepower is there but other apps just
aren't getting their shots at the CPU.
Any suggestions for remedying the situation besides
Start|Settings|Control Panel|System|Advanced|Performance
Options|Optimize performance for Applications?
Re-write the query using VBA code where you have a little more control
over the processer. The append process may be slower (or maybe not). A
little experimentation is required....
Although I'm no expert, I strongly agree with this suggestion. I had a similar
situation with a SQL statement that tried to do too much. The query took 45
minutes to run. I changed the form so that it wrote basic information to a
temporary table instead. The code behind the form handled the logic that
required IIF statements in SQL. Then I changed the report to use the temporary
table as a Record Source. The new implementation took 45 seconds to run so no
new computers were needed :-). I wasn't running an append query, but you
can probably expect a significant speed increase by using VBA.
Pete, who seems to be quite proficient in VBA, should look into changing the
design of any query that takes 45 minutes. I remember when using Access 2.0
(has it been 10 years already?) I used to redesign any query that took longer than
two seconds to run.
James A. Fortune
