How to speed up performance while updating records ?

[Jürgen Germonpré]

Dear all,

I have this Access table (up to 300.000 records). For each record I have to
generate an calculated number and update a field with it. So every single
record has to be updated with a unique number.

I wrote this procedure in VBA, which cycles al records in a recordset,
calculates and updates the record....
But this takes up a lot of time !!!

I can image there is a much better way to do this and I wondered if anybody
could help me ???

Hereby my original-super-slow code.

Public Sub Numbering()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim lMaxRec As Long
Dim bFirstRun As Boolean



Set db = CurrentDb()
Set rs = db.OpenRecordset(sTableName)


lMaxRec = rs.RecordCount
If lMaxRec > 0 Then
rs.MoveFirst
bFirstRun = True
While Not rs.EOF
rs.Edit
rs("MyNumberField") = GetMyNumber
rs.Update
rs.MoveNext
Wend
Else
MsgBox "No records in table: " & sTableName, vbCritical
End If
rs.Close
Set rs = Nothing

db.Close
Set db = Nothing

End Sub



Thanks for your help.

JG

 

[Duane Hookom]

You might be much better off using an update query. It's hard to tell since
you didn't share the code for "GetMyNumber".

 

[Jürgen Germonpré]

In fact GetMyNumber calculates a number starting from f.i. at 12000 and then
increments by 1, but only using numbers ending on (f.i. 1 to 6. could be 1
to 7, or whatever). So it should return: 12001, 12002, 12003, 12004, 12005,
12006, 12011, 120012, ....

this is the code i used initially.

If lMaxRec > 0 Then
rs.MoveFirst
bFirstRun = True
While Not rs.EOF
lControlDigit = lCountNormal Mod iStep
If Not bFirstRun And lControlDigit = 1 Then
lCountBase = lCountBase + 10
lCountNormal = 1
End If
bFirstRun = False
rs.Edit
rs(sFieldName) = lCountNormal + lCountBase
rs.Update
rs.MoveNext
lCountNormal = lCountNormal + 1
Wend


JG

 

[Duane Hookom]

I doubt an update query is possible without defining an ORDER BY. A sql guru
might be able to use a subquery or similar to create an update query.