Opening and closing the recordset, is there an easier way???

[Kamikaze]

Hello Guys & Gals,

I have function that gets called thousands of times throughout a process, the method i am currently using envolves opening and closing the recordset per interation. The reason i do this is because the criteria changes per time.

Here is the code, i am a self taught coder so some of my methods might not be up to scratch, please let me know if there is any confusion!!! the actual recordset is declared at module level as well!!!


Private Function IP(dblAcc As Double) As Integer
Dim dblAppNum As Double
Dim blnFound As Boolean

rstG_App.Open "SELECT * " & _
"FROM App as a " & _
"WHERE a.acc_num_mst LIKE '%" & dblAcc & "%'; ", _
CurrentProject.Connection, adOpenStatic, adLockOptimistic

If rstG_App.RecordCount > 0 Then
dblAppNum = rstG_App.Fields("app_num").Value

rstG_AppFeePmt.Open "SELECT * " & _
"FROM App_Fee_Pmt as a " & _
"WHERE a.app_num LIKE '%" & dblAppNum & "%' ;", _
CurrentProject.Connection, adOpenStatic, adLockOptimistic

If rstG_AppFeePmt.RecordCount > 0 Then
Do Until rstG_AppFeePmt.EOF Or blnFound = True
If rstG_AppFeePmt.Fields("fee_grp_typ_cod").Value = "003" Then
If 0 - rstG_AppFeePmt.Fields("pmt_clt_amt").Value = _
rstSAP.Fields("Amount").Value Then
IP = 4
blnFound = True
Else
If rstG_AppFeePmt.Fields("pmt_clt_amt").Value = _
rstSAP.Fields("Amount").Value Then
'****** Try to create a refund routine!!!******
IP = 7
blnFound = True
Else
IP = 5
blnFound = True
End If
End If
End If
rstG_AppFeePmt.MoveNext
Loop
Else
IP = 5
End If
rstG_AppFeePmt.Close
Else
IP = 6
End If

rstG_App.Close
End Function



So basically... i would love to streamline this function because it takes AGES to open and close the record set a few thousand times!!!

Many thanks
Tim

[Kamikaze]

I've just read about the filter property of the recordset, i think i should be able to save alot of time with this... i am just going to try!

Thanks
Tim

[Kamikaze]

Well its not quicker at all but then i guess its to be expected, the tables i am working with are hundreds of thousands of records

[XLGibbs]

Depending on the actual purpose of this, you may not need to cycle through the record set.....you might be able to apply the function to the entire recordset at once, but instead iterate throught the variables needed....

It may also speed up if you only select the fields necessary to perform the routine, that is...rather than Select *. select and indentify only the columns of interest to the function....less data occupying the temporary memory may accelerate the process slightly...

[XLGibbs]

Near as I can infer from the code...you are first trying to identify duplicates, and when there is a duplicate, perform the evaluation, and set a value based on certain conditions...

You can do all of this with a carefully constructed update statement using case selects on the columns of interest within the Set statement to determine the "IP" value...

For example if IP were a field in the rstG_AppFeePmt recordset you could do this were the dupe list was an alternate recordset..

Update rstG_AppFeePmt
Set IP = (Case
when 0 - a.[pmt_clt_amt] = b.[Amount] then 4
when a.[pmt_clt_amt] = b.[Amount] then 7
else 5 end)
From rstG_AppFeePmt a inner join rst_App b on
a.[dblAppNum] = b.[App_Num]
Where b.[App_Num] in (Select App_Num From [DupeList])


Again this is not intended to work exactly as written but to show the general syntax...the DupeList would be a recordset equivalent to something like the following to create a list of duplicated account numbers.


Select App_Num, Count(*) as Ct from rst_App Group By App_Num Having Count(*) > 1


Hope that gives you some food for thought. Without the actual recordsets et al, it is hard for me to write the exact statements needed, but wanted to throw it out there about an alternate procedure..

[Kamikaze]

Thanks for your suggestions there, i will definitely be limiting the fields selected for the recordsets, there are about 8 different recordsets each with about 10 - 15 fields, only a few of them are required!!!