Making Record By record and Field By Field Calculations Faster

P

Preying Mentis

Heeeeeeeeelp!!

Using MS Access 2000.

I am writing a data mining application that gathers data from a third
party application.

Once this data is gathered, my program runs several sets of
calculations and comparisons on a record by record, and at times a
field by field, basis (A neccessity due to the format of the third
parties database).

The third parties database can grow to a good size of about 10,000
records or more in the main file being calculated on and our
calculations run two-year analysis. Which means about 20,000 records
being calculated on, with at least two fields being compared - at
least 40,000 calculations!

I have the procedure working correctly but, as you could very well
imagine, it takes a veeeeery long time.

I am using almost strictly all VBA code (SQL commands as frequently as
I can) with very little MS Access Queries doing the calculations.

The VBA code I use is the standard Recordset loop:

I open the recordset
Move to first record
Grab ID
Do Calculations for that record
Movenext

etc...

Does Anybody know any possible way I can increase the speed of running
this code?

Any VBA optimization tips would be most helpful.

Thank you for your time,

Preying Mentis
 
D

david epsom dot com dot au

1) Always create field variables outside loops

dim fld = field

set fld = rs.fields(1)

2) Use field index, not field name, when referring to a field:

v = rs.fields(12)


3) Use ForwardOnly ReadOnly recordsets where possible

4) Show us some code!

(david)
 
Top