Making Record By record and Field By Field Calculations Faster

  • Thread starter Thread starter Preying Mentis
  • Start date Start date
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
 
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)
 
Back
Top