Slow recordset loop

[certisoft]

I am struggling with a performance issue in my vba code.
I have read a lot in the forums and it seems that I should be using stored queries but have no idea how to perform this function without looping through records.

Table Bank Deposits T contains a record for each subscriber for each month
MonthlyFee, InstallFee, & LockboxFee are the current values for that subscriber table Subscriber T
Table Subscriber History Temp T only contains records for months in which there has been a change to one of these values
Need to update the values in Bank Deposits T if there is an entry for that month in Subscriber History Temp T
My code works but the section in red is very slow
Currently around 1400 records to loop through

CODE:

Public Sub depositsupdate()
Dim db As Database
Dim ssr As Recordset, ssw As Recordset, qd As Recordset
Dim strSQL As String
Dim strStartDate As String

Set db = CurrentDb()

Set ssw = db.OpenRecordset("Bank Deposits T", DB_OPEN_TABLE) ssw.MoveFirst
Do Until ssw.EOF

strSQL = "SELECT Max([Subscriber History Temp T].[MonthStart]) AS StartingMonth FROM (SELECT TOP 1 [Subscriber History T].MonthStart FROM [Subscriber History T] ORDER BY [Subscriber History T].MonthStart) AS Period, [Subscriber History Temp T] WHERE [Subscriber History Temp T].MonthStart <= #" & ssw!paydate & "# ;"

Set qd = db.OpenRecordset(strSQL)' Finds [Monthstart] as [StartingMonth] in [Bank Deposits Temp T] with [MonthStart] same date as current record in [Subscriber Statements T].[paydate] or if does not exist finds most recent previous record

If IsNull(qd!StartingMonth) Then GoTo Nextssw

strSQL = SELECT [Subscriber History Temp T].SubID, [Subscriber History Temp T].MonthStart, [Subscriber History Temp T].MonthlyFee, [Subscriber History Temp T].InstallFee, [Subscriber History Temp T].LockboxFee From [Subscriber History Temp T] WHERE [Subscriber History Temp T].SubID = " & ssw!SubID & " AND [Subscriber History Temp T].MonthStart = #" & qd!StartingMonth & "#;"

Set ssr = db.OpenRecordset(strSQL) ' Finds record, if exits, in [Bank Deposits Temp T] with [SubID] matching current [Bank Deposits T] record and [Monthstart] matching [StartingMonth]

If ssr.EOF Then ' Skip if no matching History record
GoTo Nextssw
End If

If ssw!MonthlyFee + ssw!InstallFee + ssw!LockboxFee = ssr!MonthlyFee + ssr!InstallFee + ssr!LockboxFee Then ' Skip if no updates required
GoTo Nextssw

Else ' Update [Subscriber Statements T]
ssw.edit
ssw!MonthlyFee = ssr!MonthlyFee
ssw!InstallFee = ssr!InstallFee
ssw!LockboxFee = ssr!LockboxFee
ssw.Update

End If

Nextssw:
ssw.MoveNext
Loop
End Sub

Thanks

George

 

[Minty]

You appear to be storing a running balance with this process, which is generally frowned on. You should always calculate this type of value from transactions in and out.

That way you don't need to try and work out if there has or hasn't been a transaction.

Also i would strongly suggest remove the spaces from your table and field names, and consider changing the way you are identifying tables by putting tbl or similar at the front of the the table name not at the end. It makes it lots easier to identify things that way around. Get yourself a Naming convention and stick with it https://access-programmers.co.uk/forums/showthread.php?t=225837

 

[certisoft]

Thanks Minty,

This code is used to get a balance for payment and also similar code used to create statements

Unfortunately I have recently taken over this program from someone else and am just trying to work with what I have without doing a compete rewrite

Same issue with naming conventions

I would like to rewrite the compete program at some point but just need to speed it up for now

Could you please expand on your statement; You should always calculate this type of value from transactions in and out.
Is it possible to implement this with an existing program with no in out history?

George

 

[MarkK]

It's a design flaw in your tables if you find yourself needing to move data from table to table as part of your business routine, or if you need to store the results of calculations, which is just another form of duplication.

Redesign your tables so that the same data is never stored in two locations, and don't store the results of calculations. Rather, if you need to do calculations, perform them in a query at retrieval time from the latest raw data.