Macro Performance

[Hawksby]

I currently import 40,000+ records a day to my database and then seperate
these into business units by running approx 30 append and update queries (I
Append the records to a new table before updating a report field with the
business unit name).

I have put the import and the append / update queries into 1 macro which i
run everyday but this is taking up to 15 minutes. It would be quicker for me
to press the append and the corresponding update query (This would take about
5 minutes) Is there a way of improving my macro's performance?

 

[Jeff Boyce]

Is there something else happening in your macro?

I've found that running a series of append and update queries via a macro is
faster than running each one 'manually'.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Hawksby]

No not all, which is what's anoying me. It is literally 60 queries running
in sequence and it seems to slow down the performace of either my computer or
the macro. Not sure why

 

[Hawksby]

Can i just check that the macro should be running open queries to perform the
activities?

 

[Jeff Boyce]

The only way I'm familiar with for "checking" the macro is to open it in
design view and inspect each of the lines.

If it were my macro, the next thing I'd do is make a copy and throw out half
of the queries. Does the 'first-half' macro run quickly, but the
'second-half' bog down? I would try the same approach again, splitting the
offending half in half to try to zero in on whether one particular query is
slowing down the entire job.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Hawksby]

Thanks Jeff i'll try that

The only way I'm familiar with for "checking" the macro is to open it in
design view and inspect each of the lines.

If it were my macro, the next thing I'd do is make a copy and throw out half
of the queries. Does the 'first-half' macro run quickly, but the
'second-half' bog down? I would try the same approach again, splitting the
offending half in half to try to zero in on whether one particular query is
slowing down the entire job.

Regards

Jeff Boyce
Microsoft Office/Access MVP