mishej@execpc.com (almish) wrote in message news:<95d47401.0404151939.2427b624@posting.google. com>...[color=blue]
>
GoogleGroups@FatBelly.com (PeteCresswell) wrote in message news:<74d79443.0404150400.24d28d02@posting.google. com>...[color=green]
> > 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?[/color]
>
> 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....[/color]
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
