Need my loop code to run faster

[kpal29]

I have a block of code that imports data from an xls file. Opens the recordset and does some field updates and calculations. I think the If statements within my loop are really slowing down the processing.

Pls take a look and advise how I can make this code more efficient. Thanks

Sub UpdateImportData()
Dim db As DAO.Database
Set db = CurrentDb
Dim FileType As String
Dim ImpDate As Date
Dim ETAYear As Integer
Dim Datatype As String
Dim NewImport As String

FileType = Forms!FileImport.FileType
ImpDate = Forms!FileImport.[Last File Imported Date]
ETAYear = Forms!FileImport.ETAYear
ETAMonth = Forms!FileImport.ETAMonth
Datatype = "Financials"

NewImport = "SELECT PRMFinancials.[Client Code], PRMFinancials.PCCode, PRMFinancials.Value, PRMFinancials.FileCode, " & _
"PRMFinancials.CientGroup , PRMFinancials.[ETA Month], PRMFinancials.[ETA Year], PRMFinancials.Type, PRMFinancials.ImportDate " & _
"FROM PRMFinancials WHERE (((PRMFinancials.FileCode) Is Null));"

Dim rstPRMFinancials As DAO.Recordset 'acts like a table or query object
Set rstPRMFinancials = db.OpenRecordset(NewImport) 'Opens the table

rstPRMFinancials.MoveFirst

'______________________________________________________________________________
    
Do Until rstPRMFinancials.EOF

Dim Amt As Double
If FileType = "Reversal Last Month Accruals" Or FileType = "Reverse Download next month" Or FileType = "Actuals" Or FileType = "Reverse Manual Adj" Then
Amt = (rstPRMFinancials![Value] * -1)
Else: Amt = rstPRMFinancials![Value]
End If

rstPRMFinancials.Edit
    rstPRMFinancials![FileCode] = FileType
    rstPRMFinancials![ImportDate] = ImpDate
    rstPRMFinancials![Value] = Amt
    rstPRMFinancials![ETA Year] = ETAYear
    rstPRMFinancials![ETA Month] = ETAMonth
    rstPRMFinancials![Type] = Datatype
    rstPRMFinancials.Update
    rstPRMFinancials.MoveNext

Loop
rstPRMFinancials.Close
'________________________________________________________________________________

'Update the Client Group field based on the lookup table
Dim clientgroup As String
clientgroup = "UPDATE PRMFinancials INNER JOIN PRMClientList ON PRMFinancials.[Client Code] = " & _
"PRMClientList.[Client Code] SET PRMFinancials.CientGroup = [PRMClientLIst].[client group]" & _
"WHERE (((PRMFinancials.CientGroup) Is Null));"
db.Execute clientgroup
[code]

 

[jimlee]

i had a similar problem a couple of months ago with a reasonably large recordset. I was told it was because of my slow network. I overcame this by doing as much updating as i could in excel first, then importing the data into access and only doing what was absolutely neccessary there. it took a fraction of the time.

even though this worked, it wasn't a satisfactory solution in my eyes so if anyone has any better ideas....

jimlad

 

[GummowN]

If you are working with a large recordset that follows a set rule that you can apply to the whole source have you thought about constructing a query to do this.

From the looks of it you are only hitting 1 table - so no problems with an update query. This runs a lot quicker then looping through a recordset.

Why not?

Dbs.execute "Update_PRMFinancials"
Dbs.execute "Update_ClientGroup"

This is where the 2 updates are queries.

That was me pointing you in a direction without giving you the full answer!

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[kpal29]

I thought code was faster than queries - especially updates. I will give this a try as long as my iif statements can work.

 

[RoyVidar]

Recordset approaches will almost always be among the slowest possible alternative (next in line, would be typing the changes ;-) ). Whenever you can execute a query in stead, do so!

Roy-Vidar