Write data into table with VB code

[Roxana]

Hello,
I am working with an Access 2007 application.
I have this code that writes records in a table. The code runs ok but it
takes too long. For one month of data it takes about 40 min but if I add the
second month it takes more than an hour.
I have a form with a button. When click on the button I want to read a table
with over 300,000 recs/month, join with 2 other tables (one of them with
about 100,000 recs), perform some calculations and write data into a history
table. For each month I would have about 100,000 recs to add to the history
table. I would like to keep at least 2 months of data in the history table.

My code in VB is something like this:
I have a routine that reads the fields from a Balance table, groups data by
cust_id and does some calculations

Private Sub cmdRead_Click()
Set MyDb = CurrentDb

SSQL = "SELECT * FROM tbl_Balances where CUST_ID > '0' order by CUST_ID"

Set myRS = MyDb.OpenRecordset(SSQL)

With myRS
..MoveFirst
While myRS.EOF = False
Calculations
Call Update_History(process_year, process_month, CustID, Amount)
MoveNext
Wend
EndWith

This is the function that writes the data in the History table:
Public Function Update_History(hyear As String, hmonth As String, cust_id As
String, balance As Double)
Dim urs As Recordset

Set urs = MyDb.OpenRecordset("Select * from TblHistory")
urs.AddNew
urs.Fields(0) = hyear
urs.Fields(1) = hmonth
urs.Fields(2) = cust_id
urs.Fields(3) = balance

urs.Update
urs.Close
End Function

Would there be a better way of doing this and speed things up ?
Any sugestion would be appreciated.
Thank you.

 

[Paolo]

Hi Roxana,

to speed up you can use a sql statement and avoid calling a function.
You can try the following
let's say the field names in TblHistory are
Fields(0) = h_year
Fields(1) = h_month
Fields(2) = h_cust_id
Fields(3) = h_balance

Private Sub cmdRead_Click()
Set MyDb = CurrentDb

SSQL = "SELECT * FROM tbl_Balances where CUST_ID > '0' order by CUST_ID"

Set myRS = MyDb.OpenRecordset(SSQL)

With myRS
.MoveFirst
While myRS.EOF = False
Calculations
currentdb.execute("INSERT INTO TblHistory ( h_year, h_month, h_cust_id, h_balance ) SELECT " & process_year & ", " & process_month & ", " & custid & ", " & Amount)

MoveNext
Wend
EndWith

This is air code so you have to test it but I think it could work and also
it could speed up your processing.

HTH Paolo

 

[Klatuu]

I don't know the nature of your calculations, but it seems to me this could
all be done with an update query to update your history table.

If you put your calculations in functions in a standard module, you can call
them from queries using calculated fields. This would be much faster than
the recordset processing you are using.

 

[Albert D. Kallal]

Open the 2nd reocrdset ***once*** and pass it along....


Set myRS = MyDb.OpenRecordset(SSQL)

dim urs as dao.RecordSet
set urs = MyDb.OpenReocrdSet("tblHistory")

Call Update_History(urs, process_year, process_month, CustID, Amount)


(remove your creating of the urs in the history sub. This should speed
things up by about 10 times.......).

Post back for history sake as to how much faster the above suggestion
worked.....

My guess was 10 times, but it might be in fact faster then that...

 

[Roxana]

Thank you all for great suggestions.
The solution that worked best for me was to use the "Insert into tblHistory
Values..."
It cut the run time from 2 hrs to 2 min !

I did try Albert's solution but using OpenRecordset didn't make as big of a
difference as the Insert into table option.

Roxana