alwayshouston
MIS
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
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