Best Way of updating a Table

[alwayshouston]

Hi All
I am trying to update a table(Table Name:AllFlatFile) that has 100,000 records via VBA Code below. The problem is that it takes too much time (45 minutes) to do the updating based on simple logic in the code. Keep in mind that AllFlatFile is stand alone table with no relationship. Is there any other alternative to update the table to speed up the process. I know that I could have easily run an Update SQL command, but I want to run this code because I want to incorporate more logic before the I update specific field.
Thanks!
My code:
Dim ConstDb As DAO.Database
Dim ConstrsDb As DAO.Recordset
Set ConstDb = CurrentDb()
Set ConstrsDb = ConstDb.OpenRecordset("AllFlatFile" , dbOpenDynaset)
vSetChar = "USD"
vDestFieldName="PriceType"
Do Until ConstrsDb.EOF
ConstrsDb.Edit
ConstrsDb.Fields(vDestFieldName) = vSetChar
ConstrsDb.Update
ConstrsDb.MoveNext
Loop
ConstrsDb.Close
Set ConstrsDb = Nothing
Set ConstDb = Nothing

 

[mpastore]

Remove any indexes you might have on the table and reapply them afterwards.

Mike Pastore

Hats off to (Roy) Harper

 

[SgtJarrow]

May not give you much, but unless you plan on changing the values for:

vSetChar = "USD"
vDestFieldName="PriceType"

remove the variables and just put the values in the .Fields line....

For every loop through, Access has to replace those variable with the values....if they never change, why make it do that extra work???

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[alwayshouston]

Thanks for your reply. I have incorporated your suggestions, but my code is still very slow. Any other suggestions???

 

[hopper]

I might be confusing DAO and ADO here, but would it be faster to do a connection.execute instead going through the Recordset?

something like:

Dim ConstDb As DAO.Database
Set ConstDb = CurrentDb()
ConstDb.Execute "UPDATE AllFlatFile SET PriceType = 'USD'"
Set ConstDb = nothing

Comments???

 

[alwayshouston]

Yea, I know that I could call Execute method, but I have to include several other logic to the code that I can't do it with a single Update SQL command. Are my locks incorrect or my recordset setting is incorrect? Perhaps, I could do the update without loading entire table into memory??