Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time) 1

[03Explorer]

Simple explanation of my project. I have nested loops that each are based on a linked table query (source = SQL Server tables).

Loop1 start
| Loop2 start
| | Loop3 start
| | | Loop4 start
| | | Loop4 end
| | | Loop5 start
| | | Loop5 end
| | Loop3 end
| | Loop6 start
| | | Loop7 start
| | | Loop7 end
| | | Loop8 start
| | | Loop8 end
| | Loop6 end
| Loop2 end
Loop1 end



Loops 5 & 8 are my destination points that populate a table based on the criteria generated from the outer loops.

I am experiencing two things that surprise me. The time to execute this VBA (using same criteria that define the outer loops) has such a varying run time. I have two groupings of test runs, one on the LAN and one via VPN (Off-site). The location is outside this discussion. If I run the script from LAN the execution time can vary anywhere from a few minutes to over 10 minutes. Even when executing back to back times. Very inconsistent from time to time or even day to day.

The second experience is this execution takes anywhere from 4 minutes up to 30 minutes. The information is in reference tables housed in SQL Server. I am expecting the output to be no more than 4 minutes based on queries execution time, when done manually, is instantaneous.

1. Where is the bottleneck in the design that causes this to take long to execute?
2. Why is the execution time so random for times for running?

Here is my function that contains the loops

    Dim db As DAO.Database
    Dim rs, rsPARENT, rsCHILD, rsServiceType, rsSkills, rsConsultingMatrix, rsSurveyGenerated As Recordset
    Dim SQL, SQL2, SQL3, SQL4 As Variant
    Dim origPractice As Integer
    Dim newPractice As Integer
    Dim Client As Variant
    Dim Practice As Integer
    Dim ShowPractice As Integer
    Dim StartTime As Date
    Dim n As Integer
    Dim s As String
    
    n = FreeFile()
    Open "C:\temp\Synergy3" & "_" & SurveyNumber & ".txt" For Output As #n
    
    Set db = CurrentDb
    
    s = "starting Matrix: " & Format(Now, "h:nn:ss AM/PM")
    Debug.Print s
    Print #n, s
    
    'get information pre looping
    'original Practice
    SQL = "SELECT PracticeID FROM tblProjectDetails WHERE aID = " & SurveyNumber & " ;"
    Set rs = CurrentDb.OpenRecordset(SQL)
    origPractice = rs!PracticeID 'practice will be used as base for what was selected and showpractice is for calculating
    'ClientID
    SQL = "SELECT ClientID FROM tblProjectDetails WHERE aID = " & SurveyNumber & " ;"
    Set rs = CurrentDb.OpenRecordset(SQL)
    Client = rs!ClientID
    
    'rs.Close
    'Set rs = Nothing
    
            
    
    'Parent Loop rsPARENT : [URL unfurl="true"]https://stackoverflow.com/questions/5864160/code-to-loop-through-all-records-in-ms-access[/URL]
        SQL = "SELECT RoleID, AssociateID FROM tblProjectsAssignedTo WHERE SurveyNumberID = " & SurveyNumber & " ORDER BY 1 ASC;"
        Set rsPARENT = CurrentDb.OpenRecordset(SQL)
        'Check to see if the recordset actually contains rows
        If Not (rsPARENT.EOF And rsPARENT.BOF) Then
            rsPARENT.MoveFirst 'Unnecessary in this case, but still a good habit
            Do Until rsPARENT.EOF = True
    '        Debug.Print "Call Parent SQL " & Format(Now, "h:nn:ss AM/PM")
    'Child Loop  rsCHILD
                'Debug.Print "Parent: " & rsPARENT!AssociateID & ", Role: " & rsPARENT!RoleID
                SQL2 = "SELECT RoleID, AssociateID FROM tblProjectsAssignedTo WHERE SurveyNumberID = " & SurveyNumber & " ORDER BY 1 ASC;"
                Set rsCHILD = CurrentDb.OpenRecordset(SQL2)
                'Check to see if the recordset actually contains rows
                If Not (rsCHILD.EOF And rsCHILD.BOF) Then
                    rsCHILD.MoveFirst 'Unnecessary in this case, but still a good habit
                    Do Until rsCHILD.EOF = True
                    'set parent / Child elapse timer
    StartTime = Now
                    
                    s = "Parent Role = " & rsPARENT!RoleID & " Child Role = " & rsCHILD!RoleID & " Time: " & Format(Now, "h:nn:ss AM/PM")
                    Debug.Print s
''                    Print #n, s
    
                        'Debug.Print "Parent: " & rsPARENT!AssociateID & ", Role: " & rsPARENT!RoleID & " Child: " & rsCHILD!AssociateID & ", Role: " & rsCHILD!RoleID
    'ServiceType Practice Loop rsServiceType (Technical; but NOT Behavioral skills)
                        s = "Technical Begins: " & Format(Now, "h:nn:ss AM/PM")
                        Debug.Print s
''                        Print #n, s
                        SQL3 = "SELECT ServiceTypeID FROM tblProjectServiceTypes WHERE ProjectQueueForLcID = (SELECT Max(aID) FROM tblProjectQueueForLCs WHERE SurveyNumberID = " & SurveyNumber & ") ORDER BY 1 ASC;"
                        Set rsServiceType = CurrentDb.OpenRecordset(SQL3)
                        'Debug.Print "Call ServiceType SQL " & Format(Now, "h:nn:ss AM/PM")
                        'Check to see if the recordset actually contains rows
                        If Not (rsServiceType.EOF And rsServiceType.BOF) Then
                            rsServiceType.MoveFirst 'Unnecessary in this case, but still a good habit
                            Do Until rsServiceType.EOF = True
                                'Debug.Print "ServiceTypeID: " & rsServiceType!ServiceTypeID
                                
                                'Consulting Matrix Part 1: Technical Skills
                                SQL4 = "SELECT Technical FROM tblConsultingMatrix WHERE ParentRole = " & rsPARENT!RoleID & " AND ChildRole = " & rsCHILD!RoleID & " ;"
                                Set rsConsultingMatrix = CurrentDb.OpenRecordset(SQL4)
                                'Check to see if the recordset actually contains rows
                                If Not (rsConsultingMatrix.EOF And rsConsultingMatrix.BOF) Then
                                    rsConsultingMatrix.MoveFirst
                                    Do Until rsConsultingMatrix.EOF = True
                                    
                              'ShowPractice logic here for child
                                        Select Case rsPARENT!RoleID
                                            Case 7, 6, 5
                                                ShowPractice = origPractice
                                                Practice = 5
                                            Case 4
                                                SQL = "SELECT aID FROM tblSkills WHERE practiceID = " & origPractice & " AND ProjectRoleID = " & rsCHILD!RoleID & " AND ServiceTypeID = " & rsServiceType!ServiceTypeID
                                                Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset, dbFailOnError + dbSeeChanges)
                                                'Debug.Print "Call skills Role4 SQL " & Format(Now, "h:nn:ss AM/PM")
                                                If Not (rs.EOF And rs.BOF) Then
                                                    'records exist
                                                    ShowPractice = origPractice
                                                    Practice = origPractice
                                                Else
                                                    'records do NOT exist
                                                    ShowPractice = origPractice
                                                    Practice = 5
                                                End If
                                            Case 3, 2, 1
                                                ShowPractice = origPractice
                                                Practice = origPractice
                                        End Select
    'Technical Skills to be populated HERE
                                        'Behavioral needs be done outside/after ServiceType Loop
                                        If rsConsultingMatrix!Technical = 1 Then
                                        ''Debug.Print "Begin Technical Skills " & Format(Now, "h:nn:ss AM/PM")
                                            SQL = "SELECT aID, SkillNo, Skill, SkillVersion, BusinessTypeID, ShortForm "
                                            SQL = SQL + "FROM tblSKills "
                                            SQL = SQL + "WHERE PracticeID = " & Practice & " AND ProjectRoleID = " & rsCHILD!RoleID & " AND "
                                            SQL = SQL + "SkillTypeID = " & rsConsultingMatrix!Technical & " AND ServiceTypeID = " & rsServiceType!ServiceTypeID & " AND "
                                            SQL = SQL + "SkillVersion = (SELECT MAX(SkillVersion) FROM tblSkills WHERE PracticeID = " & Practice & " AND ProjectRoleID = " & rsCHILD!RoleID & " AND SkillTypeID = " & rsConsultingMatrix!Technical & " AND ServiceTypeID = " & rsServiceType!ServiceTypeID & ") AND "
                                            SQL = SQL + "ActiveYn = 1 AND BusinessTypeID = 1 "
                                            SQL = SQL + "ORDER BY SkillNo ASC "
                                            Set rsSkills = db.OpenRecordset(SQL, dbOpenDynaset, dbFailOnError + dbSeeChanges) '--, dbSeeChanges)
                                            'Debug.Print "Skill Recordset Defined: " & Format(Now, "h:nn:ss AM/PM")
                           
                                            If Not (rsSkills.EOF And rsSkills.BOF) Then
                                                rsSkills.MoveFirst
                                                Do Until rsSkills.EOF = True
    ''                                                Set rsSurveyGenerated = CurrentDb.OpenRecordset("tblSurveysGenerated", dbOpenDynaset, dbFailOnError + dbSeeChanges)
    ''                                                rsSurveyGenerated.AddNew
    ''                                                    rsSurveyGenerated!SurveyNumberID = SurveyNumber
    ''                                                    rsSurveyGenerated!ClientNumberID = Client
    ''                                                    rsSurveyGenerated!ParentID = rsPARENT!AssociateID
    ''                                                    rsSurveyGenerated!ChildID = rsCHILD!AssociateID
    ''                                                    rsSurveyGenerated!SkillAutoID = rsSkills!aID
    ''                                                    rsSurveyGenerated!SkillOrderNumber = rsSkills!SkillNo
    ''                                                    rsSurveyGenerated!SkillVersion = rsSkills!SkillVersion
    ''                                                    rsSurveyGenerated!ShowPracticeID = ShowPractice
    ''                                                    rsSurveyGenerated!PracticeID = Practice
    ''                                                    rsSurveyGenerated!RoleID = rsCHILD!RoleID
    ''                                                    rsSurveyGenerated!SkillTypeID = rsConsultingMatrix!Technical
    ''                                                    rsSurveyGenerated!ServiceTypeID = rsServiceType!ServiceTypeID
    ''                                                    rsSurveyGenerated!BusinessTypeID = rsSkills!BusinessTypeID
    ''                                                    rsSurveyGenerated!SurveyCreatedDate = Date
    ''                                                rsSurveyGenerated.Update
    '
                                                    SQL2 = "INSERT INTO tblSurveysGenerated (SurveyNumberID, ClientNumberID, ParentID, ChildID, SkillAutoID, SkillOrderNumber, SkillVersion, ShowPracticeID, PracticeID, RoleID, SkillTypeID, ServiceTypeID, BusinessTypeID, SurveyCreatedDate) "
                                                    SQL2 = SQL2 + "VALUES (" & SurveyNumber & ", " & Client & ", " & Chr(34) & rsPARENT!AssociateID & Chr(34) & ", " & Chr(34) & rsCHILD!AssociateID & Chr(34) & ", " & rsSkills!aID & ", " & rsSkills!SkillNo & ", " & rsSkills!SkillVersion & ", " & ShowPractice & ", " & Practice & ", " & rsCHILD!RoleID & ", " & rsConsultingMatrix!Technical & ", " & rsServiceType!ServiceTypeID & ", " & rsSkills!BusinessTypeID & ", '" & Date & "') "
                                                    db.Execute SQL2
    '                                                DoCmd.SetWarnings False
    '                                                DoCmd.RunSQL SQL2
    '                                                DoCmd.SetWarnings True
                                                    rsSkills.MoveNext
                                                Loop 'Skills
                                            Else 'Skills
                                                'Do not populate Skills
                                            End If 'Skills
                                            'Debug.Print "T:Skills done " & Format(Now, "h:nn:ss AM/PM")
                                            
                                        Else 'Technical Skills
                                            'Do not populate Technical Skills (0's)
                                        End If 'Technical Skills
                                        'Debug.Print "End ConsultingMatrix : " & Format(Now, "h:nn:ss AM/PM")
                                        rsConsultingMatrix.MoveNext
                                    Loop 'ConsultingMatrix
                                Else 'ConsultingMatrix
                                    'Debug.Print "There are no records in the Consulting Matrix recordset."
                                End If 'ConsultingMatrix
                                'Debug.Print "Finished looping through CONSULTING MATRIX. " & Format(Now, "h:nn:ss AM/PM")
    
                                rsServiceType.MoveNext
                            Loop 'rsServiceType
                        Else 'rsServiceType
                            'Debug.Print "There are no records in the SERVICE TYPE recordset. " & Format(Now, "h:nn:ss AM/PM")
                        End If 'rsServiceType
                                s = "Technical Ends: " & Format(Now, "h:nn:ss AM/PM")
                                Debug.Print s
''                                Print #n, s
                        
    'Begin Behavioral Skills HERE (OUTSIDE SERVICE TYPES)
    'ServiceType Loop rsServiceType (Behavioral; NOT Technical skills)
    
                        s = "Behavioral Begins: " & Format(Now, "h:nn:ss AM/PM")
                        Debug.Print s
''                        Print #n, s
                        SQL3 = "SELECT DISTINCT ServiceTypeID, ServNo FROM tblSkills "
                        SQL3 = SQL3 + "WHERE ProjectRoleID = " & rsCHILD!RoleID & " AND PracticeID in (5," & Practice & ") AND SkillTypeID = 2 AND ActiveYn = 1 AND BusinessTypeID = 1 "
                        SQL3 = SQL3 + "ORDER BY ServNo ASC"
                        Set rsServiceType = CurrentDb.OpenRecordset(SQL3)
                        'Check to see if the recordset actually contains rows
                        If Not (rsServiceType.EOF And rsServiceType.BOF) Then
                            rsServiceType.MoveFirst 'Unnecessary in this case, but still a good habit
                            Do Until rsServiceType.EOF = True
                                'Consulting Matrix Part 2: Behavioral Skills
                                SQL4 = "SELECT Behavioral FROM tblConsultingMatrix WHERE ParentRole = " & rsPARENT!RoleID & " AND ChildRole = " & rsCHILD!RoleID & " ;"
                                Set rsConsultingMatrix = CurrentDb.OpenRecordset(SQL4)
                                'Check to see if the recordset actually contains rows
                                If Not (rsConsultingMatrix.EOF And rsConsultingMatrix.BOF) Then
                                    rsConsultingMatrix.MoveFirst
                                    Do Until rsConsultingMatrix.EOF = True
                                        '---ShowPractice logic here for child
                                        'Roles 7,6,5 are typically practice 'ALL' (5)
                                        'Role 4 can be ALL(5) or other
                                        'Roles 3,2,1 are typically Practice
                                        Select Case rsPARENT!RoleID
                                            Case 7, 6, 5
                                                ShowPractice = origPractice
                                                Practice = 5
                                            Case 4
                                                SQL = "SELECT aID FROM tblSkills WHERE practiceID = " & Practice & " AND ProjectRoleID = " & rsCHILD!RoleID & " AND ServiceTypeID = " & rsServiceType!ServiceTypeID
                                                Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset, dbFailOnError + dbSeeChanges)
                                                'Debug.Print "Call B:Skills Role 4 SQL " & Format(Now, "h:nn:ss AM/PM")
                                                If Not (rs.EOF And rs.BOF) Then
                                                    'records exist
                                                    ShowPractice = origPractice
                                                    Practice = origPractice
                                                Else
                                                    'records do NOT exist
                                                    ShowPractice = origPractice
                                                    Practice = 5
                                                End If
                                            Case 3, 2, 1
                                                ShowPractice = origPractice
                                                Practice = 5
                                        End Select
    
                                        If rsConsultingMatrix!Behavioral = 1 Then  ' 1 = Yes; 2 = No
                                            'Yes do Behavioral Skills
                                            SQL = "SELECT aID, SkillNo, Skill, SkillVersion, BusinessTypeID "
                                            SQL = SQL + "FROM tblSKills "
                                            SQL = SQL + "WHERE PracticeID = 5 AND ProjectRoleID = " & rsCHILD!RoleID & " AND "
                                            SQL = SQL + "SkillTypeID = 2 AND ServiceTypeID = " & rsServiceType!ServiceTypeID & " AND "
                                            SQL = SQL + "SkillVersion = (SELECT MAX(SkillVersion) FROM tblSkills WHERE PracticeID = 5 AND ProjectRoleID = " & rsCHILD!RoleID & " AND SkillTypeID = 2 AND ServiceTypeID = " & rsServiceType!ServiceTypeID & ") AND "
                                            SQL = SQL + "ActiveYn = 1 AND BusinessTypeID = 1 "
                                                'if Role = CRM(7), add filter for ShortForm
                                                If rsCHILD!RoleID = 7 Then
                                                    SQL = SQL + "AND ShortForm = 1 "
                                                Else
                                                    'Do nothing
                                                End If 'ShortForm
                                            SQL = SQL + "ORDER BY SkillNo ASC "
                                            Set rsSkills = db.OpenRecordset(SQL, dbOpenDynaset, dbFailOnError + dbSeeChanges)
    
                                            If Not (rsSkills.EOF And rsSkills.BOF) Then
                                                rsSkills.MoveFirst
                                                Do Until rsSkills.EOF = True
                                            
                                                    'Begin populating Behavioral Skills
                                                    SQL2 = "INSERT INTO tblSurveysGenerated (SurveyNumberID, ClientNumberID, ParentID, ChildID, SkillAutoID, SkillOrderNumber, SkillVersion, ShowPracticeID, PracticeID, RoleID, SkillTypeID, ServiceTypeID, BusinessTypeID, SurveyCreatedDate) "
                                                    SQL2 = SQL2 + "VALUES (" & SurveyNumber & ", " & Client & ", " & Chr(34) & rsPARENT!AssociateID & Chr(34) & ", " & Chr(34) & rsCHILD!AssociateID & Chr(34) & ", " & rsSkills!aID & ", " & rsSkills!SkillNo & ", " & rsSkills!SkillVersion & ", " & ShowPractice & ", " & Practice & ", " & rsCHILD!RoleID & ", 2, " & rsServiceType!ServiceTypeID & ", " & rsSkills!BusinessTypeID & ", '" & Date & "') "
                                                    db.Execute SQL2
    '                                                DoCmd.SetWarnings False
    '                                                DoCmd.RunSQL SQL2
    '                                                DoCmd.SetWarnings True
                                                    rsSkills.MoveNext
                                                Loop 'Skills
                                            Else 'Behavioral Skills
                                                'Do not populate Behavioral Skills (0's)
                                            End If 'Behavioral Skills
                                            'Debug.Print "B:Skills done " & Format(Now, "h:nn:ss AM/PM")
                                        End If 'rsConsultingMatrix!Behavioral
        
                                        rsConsultingMatrix.MoveNext
                                    Loop 'ConsultingMatrix
                                Else 'ConsultingMatrix
                                    ''Debug.Print "There are no records in the Consulting Matrix recordset."
                                End If 'ConsultingMatrix
                                'Debug.Print "Finished looping through CONSULTING MATRIX. " & Format(Now, "h:nn:ss AM/PM")
                                rsServiceType.MoveNext
                            Loop 'rsServiceType
                    
                        Else 'rsServiceType
                            'Debug.Print "There are no records in the SERVICE TYPE recordset. " & Format(Now, "h:nn:ss AM/PM")
                        End If 'rsServiceType
                        s = "Behavioral Ends: " & Format(Now, "h:nn:ss AM/PM")
                        Debug.Print s
''                        Print #n, s
    
                        'END Behavioral Skills

s = "Time: " & Format(Now, "h:nn:ss AM/PM") & " - ElapsedTime for Parent " & rsPARENT!RoleID & " with child " & rsCHILD!RoleID & " is " & HoursMinutes(StartTime, Now)
Debug.Print s
Print #n, s
                        
                        rsCHILD.MoveNext
                    Loop 'rsCHILD
                Else 'rsCHILD
                    'Debug.Print "There are no records in the CHILD recordset. " & Format(Now, "h:nn:ss AM/PM")
                End If 'rsCHILD
                'Debug.Print "Finished looping through CHILD records. " & Format(Now, "h:nn:ss AM/PM")
                
                rsPARENT.MoveNext
            Loop 'rsPARENT
        Else 'rsPARENT
            'Debug.Print "There are no records in the PARENT recordset. " & Format(Now, "h:nn:ss AM/PM")
        End If 'rsPARENT
        'Debug.Print "Finished looping through PARENT records. " & Format(Now, "h:nn:ss AM/PM")
    
    
    rs.Close
    rsPARENT.Close
    rsCHILD.Close
    rsServiceType.Close
    rsConsultingMatrix.Close
    
    Set rs = Nothing
    Set rsPARENT = Nothing
    Set rsCHILD = Nothing
    Set rsServiceType = Nothing
    Set rsConsultingMatrix = Nothing
    
    Close #n

End Function

 

[Andrzejek]

“Where is the bottleneck in the design that causes this to take long to execute?”

You should easily put a start and end time before and after any SQL execution, write it into a simple txt file, and see which point of your code takes the long time. You will see your bottlenecks right away.

Right from the start, you ask for PracticeID and ClientID from the same record in the same table in two ‘trips’ to your DB. Why? This is not going to make a difference in your performance, but …why?

You do know that all RED variables are Variants, right?[tt]
Dim [red]rs, rsPARENT, rsCHILD, rsServiceType, rsSkills, rsConsultingMatrix, [/red]rsSurveyGenerated As Recordset[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

Andy, I am now altering my code to capture times before and after the db.Execute and CurrentDB.OpenRecordSet lines.

Aren't the [rs, rsPARENT, rsCHILD, rsServiceType, rsSkills, rsConsultingMatrix, rsSurveyGenerated]
supposed to be Recordset definitions? Are you saying I should define them as Variants?

The bottleneck I see from my trappings (as you can see above) show that overall the process has a run time variation and at the molecular level (each parent / child) the runs are taking longer than I'd expect. that's why I posted my code... thinking maybe I am processing the loops in an inefficient method. Right now I am using DAO.

Example I have been running the same criteria 12 times... the fastest time is 6 minutes 8 seconds... with the slowest time being 7 minutes 48 seconds... yesterday my slowest was 8 minutes 46 seconds with 6 min 22 sec as fastest... WHY!!??!! would the time be such a spread? Doing the same thing over and over and over is resulting in nearly 2 minutes run time differences! And it's not that after the first run of the day, it's slow and it gets faster (as in caching)... not at all... time is bouncing up and down.

Thoughts??

 

[Andrzejek]

Aren't the [rs, rsPARENT, rsCHILD, rsServiceType, rsSkills, rsConsultingMatrix, rsSurveyGenerated] supposed to be Recordset definitions? Are you saying I should define them as Variants?

I know they are supposed to be Recordset definitions, but they are Variants.
If you want them to be Recordsets, define them that way:

[tt]Dim rs As Recordset, rsPARENT As Recordset, rsCHILD As Recordset, rsServiceType As Recordset, rsSkills As Recordset, rsConsultingMatrix As Recordset, rsSurveyGenerated As Recordset
[/tt]
But I would define them in separate lines, but that's just me...

In the loops like yours, I would create larger recordsets at the beginning and Filter them when I need, instead of asking for small pieces of data separately.


---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

Good to know about the definitions as they are Supposed to be vs what you are saying they Actually function as.

I will look into making a bigger recordset and loop of that. I have a feeling I can't eliminate all down to one RecordSet, but I believe I can minimize a few out of the mix.

Thanks for the suggestions!!

 

[Andrzejek]

I used to do some loops inside another loops where I was going after some data in every loop. That was taking some time. Then after re-doing the logic with this new approach - grab more data and filter when needed - the time went down BIG TIME. Sometimes in the range of: from several minutes to just a few seconds.

Even if you can cut just a few trips to your data base, you may improve your execution time as well. Imagine you loop 50 times so make 50 trips to your DB. Now you can go once and filter it 50 times, which is a LOT faster.

Good luck


---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

Andy, can you give me some samples of how you are filtering on the recordset if pulling more data in a run?

Thanks

 

[Andrzejek]

I deal with ADODB recordsets, so my approach is something like this:

Dim rec1 As New ADODB.Recordset
Dim rec2 As New ADODB.Recordset

rec2.Open "Select ID, Address From SomeTable"
rec1.Open "Select ID, FullName From MyTable"

Do While Not rec1.EOF
    rec2.Filter = "ID = " & rec1!ID.Value
    If rec2.RecordCount > 0 Then[green]
        'Do your magic here[/green]
    End If
    rec1.MoveNext
Loop

rec1.Close
Set rec1 = Nothing

rec2.Close
Set rec2 = Nothing

---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

Thank you Andy,

I found this URL:

http://www.access.mvps.org/access/modules/mdl0010.htm

With this as the sample (assuming it's DAO and not ADO)

'***************** Code Start *************
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Sub sFilterRS()
Dim db As Database, rs1 As Recordset
Dim rs2 As Recordset
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Select * from tblRuns")
    rs1.Filter = "RunID=4"
    Set rs2 = rs1.OpenRecordset
    'rs2 should contain only one record
    MsgBox rs2.RecordCount
    Set rs2 = Nothing:  Set rs1 = Nothing
    Set db = Nothing
End Sub
'***************** Code End **************

 

[dhookom]

I typically use DAO because it's been around the longest (just like me).
I would be more explicit with the rs definitions.

[COLOR=#4E9A06]'   using DAO objects[/color]
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsPARENT As DAO.Recordset
    Dim rsCHILD As DAO.Recordset
    Dim rsServiceType As DAO.Recordset
    Dim rsSkills As DAO.Recordset
    Dim rsConsultingMatrix As DAO.Recordset
    Dim rsSurveyGenerated As DAO.Recordset

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

Duane, is there a benefit for being explicit when declaring the variables? or does that go with the logic of coding with indenting... good practice but serves no performance benefits.

 

[Andrzejek]

Good practice, managing your resources, and assurance that you are using what you want to use, and not what the system may decide to use.

"coding with indenting" is a necessity, a must, IMHO if you want to see what is going on in your code. Otherwise, how do you know where the block of code starts and stops...


---- Andy

There is a great need for a sarcasm font.