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

[King_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

 

[King_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

 

[King_kamikaze]

Nope, its just as slow, i guess it is gonna take a long time what ever, the tables i am using and hundred of thousands of records... oh well... a valiant effort all the same!!

Look like this is gonna have to become an overnight job !!!

Thanks
Tim

 

[reclusivemonkey]

Not sure what you are trying to do, but you could try a "Select Case" rather than If's. Also, can you specify the dblAcc by looking for all the values in the table and offering them in a query to speed things up?

 

[steevie_t]

The biggest speed speed issue might be using the Like operator to search for a double

instead of :
"a.acc_num_mst LIKE '%" & dblAcc & "%'"
just do
a.acc_num_mst = " & dblAcc

Using the like operator means the double in the database needs to be converted into a string, then each of those strings needs to be searched for the string equivalent of your dblAcc. If you use = instead then all sorts of optimisations can happen, for example indexes!

This doesn't answer your original question but I think it will bring the biggest speed increase for the least effort.

 

[steevie_t]

Another pointer that will make your code easier to read and maintain

rstG_AppFeePmt.Fields("fee_grp_typ_cod").Value

is equivalent to

rstG_AppFeePmt!fee_grp_typ_cod

(not much faster though)

 

[steevie_t]

Also take out the line:
If rstG_AppFeePmt.Fields("fee_grp_typ_cod").Value = "003" Then
and the related 'End If'
and change
rstG_AppFeePmt.Open "SELECT * FROM App_Fee_Pmt as a WHERE a.app_num LIKE '%" & dblAppNum & "%' ;", CurrentProject.Connection, adOpenStatic, adLockOptimistic
to
rstG_AppFeePmt.Open "SELECT * FROM App_Fee_Pmt as a WHERE (a.app_num =" & dblAppNum & ") AND (App_Fee_Pmt.fee_grp_typ_cod = '003') ;", CurrentProject.Connection, adOpenStatic, adLockOptimistic