none
Loop Through Records Question RRS feed

  • Question

  • I have a access 2013 DB that I used to open a form with and loop through all the records  in a table to update records based on the data in that record.  It worked fine, but was slow.  I was given information that I could do this in a alternative method using DAO.  The code below is the gist of what I have.

    Sub loopTable()
        Dim strSQL As String
        Dim rs As DAO.Recordset
        
        strSQL = "SELECT * FROM employee_tbl"   'define the SQL result that you want to loop
        Set rs = CurrentDb.OpenRecordset(strSQL)
        
        If Not rs.BOF And Not rs.EOF Then
            rs.MoveFirst
            While (Not rs.EOF)
                Debug.Print rs.Fields("Employee Name") 'define the field you want to return data
                rs.MoveNext
            Wend
        End If
        rs.Close
        Set rs = Nothing
    End Sub

    This is faster, it loops through all the records quickly, but is there a way I can modify the data in the recordset?    I tried substituting the stuff I want to change for the line "Debug.Print rs.Fields("Employee Name") 'define the field you want to return data", but doesn't change the data in the table.  Can Anyone help me?

    Wednesday, October 23, 2019 2:30 PM

Answers

  • ..............loop through all the records  in a table to update records based on the data in that record.


    Can't you just use an 'update' query? e.g.

    UPDATE employee_tbl
    SET NoResourceIdCheck = (V_DocumentID IS NULL);


    Ken Sheridan, Stafford, England

    • Marked as answer by tkosel Friday, October 25, 2019 10:26 PM
    Wednesday, October 23, 2019 5:51 PM
  • To all,

    As usual, thanks for all your input.  I , as usual learned a lot.  Ken Sheridan's suggestion was the most appro and the one I used.  I was able to eliminate my Looping and replace it with 4 queries, which also improved performance.

    • Marked as answer by tkosel Friday, October 25, 2019 10:31 PM
    Friday, October 25, 2019 10:31 PM

All replies

  • Sub loopTable()
        Dim strSQL As String
        Dim rs As DAO.Recordset
        
        strSQL = "SELECT * FROM employee_tbl"   'define the SQL result that you want to loop
        Set rs = CurrentDb.OpenRecordset(strSQL)
        
        If Not rs.BOF And Not rs.EOF Then
            rs.MoveFirst
            While (Not rs.EOF)
                Debug.Print rs.Fields("Employee Name") 'define the field you want to return data
                rs.MoveNext
            Wend
        End If
        rs.Close
        Set rs = Nothing
    End Sub

    Hi tkosel,

    This is what I would use, but perhaps there are some modifications that you can add.

        Do While (Not rs.EOF)
    '     Debug.Print rs.Fields("Employee Name") 'define the field you want to return data
          
          If (rs("Employee Name") <> new_name) Then
            rs.Edit
            rs("Employee Name") = new_name
            rs.Update
          End If
          rs.MoveNext
        Loop

    Imb.


    • Edited by Imb-hb Wednesday, October 23, 2019 2:56 PM edit
    Wednesday, October 23, 2019 2:55 PM
  • As usual, thanks for your prompt response.  Couple of issues.

     rs("Employee Name") = new_name

    yields an error,  If I use rs.fields("Employee Name) there is no error.

    In my code,

    If IsNull(V_DocumentID) = True Then 'Abbreviation is not present in Training Docs Parent
                        rs.Edit
                        rs.Fields("NoResourceIdCheck") = -1
                        rs.Update
                    Else. . .

    Gives no error, but the field is not updated.  I can include all my code if needed.  (It is kind of long.)

    Wednesday, October 23, 2019 3:49 PM
  • ..............loop through all the records  in a table to update records based on the data in that record.


    Can't you just use an 'update' query? e.g.

    UPDATE employee_tbl
    SET NoResourceIdCheck = (V_DocumentID IS NULL);


    Ken Sheridan, Stafford, England

    • Marked as answer by tkosel Friday, October 25, 2019 10:26 PM
    Wednesday, October 23, 2019 5:51 PM
  • Ken,

    As usual thanks for your feedback.  I have thought about it and cannot figure out how to write a query to do all of what I need too.  I have included all my code to illustrate the various things I am attempting to do to update the table records.  Sorry about the length.

    Private Sub DoIt_Click()
        
        On Error Resume Next
        
        Dim V_ClockNo, V_ResourceID, V_DocumentID, V_DocumentName

        If IsNull(Me.Select_Location) = True Then 'Check to see that a location is selected
            MsgBox "You must select a Location", , "No location Selected!"
            GoTo Done
        End If
        If IsNull(Me.SelectOption) = True Then 'Check to see that a report is selected
            MsgBox "You must select a option", , "No Option Selected!"
            GoTo Done
        End If
          
        Me.WaitMessage.Visible = True
        Me.Repaint
        Me.Refresh
       
        V_TableExists = DLookup("ClockNumber", "TempComplianceTable")
        If IsNull(V_TableExists) = True Or V_TableExists = "" Then 'Table doesn't exist or is empty create it
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "QueryToGetDataByDate"
            DoCmd.SetWarnings True

            Dim strSQL As String
            Dim rs As DAO.Recordset
        
            strSQL = "SELECT * FROM TempComplianceTable"   'define the SQL result that you want to loop
            Set rs = CurrentDb.OpenRecordset(strSQL)
        
            If Not rs.BOF And Not rs.EOF Then
                rs.MoveFirst
                While (Not rs.EOF)
                    ' Debug.Print rs.Fields("Employee Name") 'define the field you want to return data
                    ' ==================================
                    V_ClockNo = rs.Fields("ClockNumber")
                    V_ResourceID = rs.Fields("RESOURCE_ID")
                    V_DocumentID = DLookup("[Training_Doc_ID]", "QueryToGetVisualMFGLaborTicketAbbreviations", "[VisualMFGAbbreviation] = " & """" & V_ResourceID & """")
                    V_DocumentName = DLookup("[Training_Doc_Name]", "QueryToGetVisualMFGLaborTicketAbbreviations", "[VisualMFGAbbreviation] = " & """" & V_ResourceID & """")
           
                    If IsNull(V_DocumentID) = True Then 'Abbreviation is not present in Training Docs Parent
                        rs.Edit
                        rs.Fields("NoResourceIdCheck") = "-1"
                        rs.Update
                    Else
                        rs.Edit
                        rs.Fields("NoResourceIdCheck") = "0"
                        rs.Fields("Document_ID") = V_DocumentID
                        rs.Fields("Document_Name") = V_DocumentName
                        rs.Update
                    End If
            
                    V_Completed = DLookup("[Clock_Number]", "QueryToSeeIfDocumentWasCompleted")
                    If IsNull(V_Completed) = True Or V_Completed = "" Then  'Employee has not completed the required document
                        rs.Edit
                        rs.Fields("Compliant") = "0"
                        rs.Update
                    Else
                        rs.Edit
                        rs.Fields(Compliant) = "-1"
                        rs.Update
                    End If
                             
                    rs.MoveNext
                Wend
            End If
            rs.Close
            Set rs = Nothing

            'DoCmd.OpenForm "ComplianceForm", , , , , acHidden
       
            'DoCmd.Close acForm, "ComplianceForm"
            Me.WaitMessage.Visible = False
        Else 'table exists do nothing as it has already been processed, ready to run reports next
        
        End If
        
        Dim V_Docname
        If Me.SelectOption = 1 Then
            V_Docname = "1-ComplianceReportNonCompliantOnly"
        ElseIf Me.SelectOption = 2 Then
            V_Docname = "2-ComplianceReportMissingIDsOnly"
        ElseIf Me.SelectOption = 3 Then
            V_Docname = "3-ComplianceReport"
        ElseIf Me.SelectOption = 4 Then
            V_Docname = "4-ComplianceReportCompliantOnly"
        End If
       
        If Me.SelectOutput = 1 Then
            DoCmd.OpenReport V_Docname, acViewPreview
        Else
            DoCmd.OpenReport V_Docname, acViewNormal
        End If
    Done:
      
    End Sub

    Wednesday, October 23, 2019 6:07 PM
  • The following loops through a recordset starting with the first record and allows editing. Just make sure the recordset is in fact updatable.

    Sub loopTable()

    On Error GoTo Error_LoopTable

    Dim db As Database
    Set db = CurrentDb

    Dim strSQL As String

    Dim rs As DAO.Recordset
        
    strSQL = "SELECT * FROM employee_tbl"   'define the SQL result that you want to loop
    Set rs = CurrentDb.OpenRecordset(strSQL)

    With rs

    .Edit

    .MoveLast   'Always move to the last record first when dealing with recordsets.

    .MoveFirst

    Do Until .EOF

    .Edit

    Debug.Print rs.Fields("Employee Name") 'define the field you want to return data

    EDIT YOUR RECORDS HERE STARTING WITH THE FIRST ONE.

    .Update

    .MoveNext

    Loop

    .Close

    End With

    Error_LoopTable:

    DoCmd.CancelEvent
    MsgBox Err.DESCRIPTION
    Resume Next

    Exit Sub
    End Sub

    Wednesday, October 23, 2019 6:36 PM
  •  rs("Employee Name") = new_name

    yields an error,  If I use rs.fields("Employee Name) there is no error.

    Hi tkosel,

    It is not necessary to use the property Fields, but if you feel comfortable with it, it is ok.

    If you get a compile error using rs("Employee Name"), and no compile error using rs.fields("Employee Name), i.e. one-sided quoted, then you miss probably a quote in the rest of the line. It will give you a runtime error lateron.

    In your complete code I saw the next lines:

                   If IsNull(V_DocumentID) = True Then 'Abbreviation is not present in Training Docs Parent
                        rs.Edit
                        rs.Fields("NoResourceIdCheck") = "-1"
                        rs.Update
                    Else

    Probably NOResourceIdCheck is a boolean field. Why you just don't use True or False? Moreover, the strings  "0" and "-1" have for booleans a complete different meaning then the numbers 0 and -1.

    Imb.

    Wednesday, October 23, 2019 6:46 PM
  • Imb-hb,

    NOResourceIdCheck is a  Short Text field.  Am trying all sorts of suggestions, but cannot get it to work yet.

    Wednesday, October 23, 2019 7:05 PM
  • NOResourceIdCheck is a  Short Text field.  Am trying all sorts of suggestions, but cannot get it to work yet.

    Hi tkosel,

    Did you step through the code with the debugger to see what happens in each line?

    With proper use of the debugger you can solve most (almost any) of the problems.

    Imb.

    Wednesday, October 23, 2019 7:26 PM
  • As Ken suggests, and, why not run more than one query?

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, October 23, 2019 7:44 PM
  • To all,

    Thanks for the many useful suggestions, I am making progress.  Can dlookup be used in a update query?  If so, can you give me a simple example?

    Wednesday, October 23, 2019 8:41 PM
  • Can dlookup be used in a update query?  If so, can you give me a simple example?

    Yes, e.g. the following is an example using Northwind tables  which would update the Employee ID column in all orders by customers in California to the employee based in BelleVue WA.

    UPDATE Customers INNER JOIN Orders
    ON Customers.ID = Orders.[Customer ID]
    SET [Employee ID] = DLookup("ID","Employees","City = ""BelleVue""")
    WHERE [State/Province]="CA";


    Ken Sheridan, Stafford, England

    Wednesday, October 23, 2019 9:09 PM
  • Ken,

    As usual I thank you for your assistance.  You know, I don't have the northwind db after all these years.  I tried to get it but can only find the sql version.  The access instructions to get it don't work.

    I will create the table structure to test this code unless you give me another example or can tell me how to get the access (2013) version of the northwind db.

    Wednesday, October 23, 2019 9:45 PM
  • You can download a zipped copy of the .mdb version of Northwind from the last post in the thread below:

    https://access-programmers.co.uk/forums/showthread.php?t=261520

    The data and table definitions differ from the later versions (they used good column names in those days!) so you'll need to change the SQL to:

    UPDATE Customers INNER JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID
    SET EmployeeID = DLookup("EmployeeID","Employees","City = ""Redmond""")
    WHERE Region ="CA";

    Ken Sheridan, Stafford, England

    Wednesday, October 23, 2019 10:23 PM
  • you know the difference between a programmer and a database developer?

    a programmer does everything with loops

    a database developer does everything with queries

    when one gets to multi millions of records; loops are not viable concepts.  You get away with it in Access typically due to the relatively small table sizes and pretty darn powerful PCs....

    update queries is the strategy to use if the loop is slow....

    Thursday, October 24, 2019 10:09 PM
  • Actually, if it's a calculation that matters, an engineer measures twice, cuts once

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Thursday, October 24, 2019 10:22 PM

  • This sounds like a put down, maybe I am too sensitive.  Others have already constructively suggested that I use queries, while I have also been given advice to use loops.  I have successfully replaced all my current stuff with Queries that do the job.
    • Edited by tkosel Thursday, October 24, 2019 10:27 PM
    Thursday, October 24, 2019 10:24 PM
  • Peter,

    While I have appreciated your input in the past, what does that response have to do with this thread?   Please do not hold this response against me, no ill will was meant.

    Thursday, October 24, 2019 10:30 PM
  • no put down intended..... 

    but it really is the difference...  programming teaches loops, and arrays, and other stuff.... but when you come over to working with record sets - particularly really big record sets then the query approach is needed...

    in a complex app there's also going to be stuff involving file path strings and such, not involving a record set, where loops are the way to go for sure.....

    gotta have the right tool for the job.....     I cut twice and it's still too small....

    Thursday, October 24, 2019 10:32 PM
  • No ill will taken! :D

    I was tweaking msdnPublicIdentity, who was tweaking our self categorizations and how we see things.

    With any kind of programming, “it has been shown” that the programmer’s guess as to where in their program the most time is spent is usually incorrect. That’s why measurement is important. Typically, in Access, the default choice for a calculation is to use queries because they’re faster than macros or VBA. Queries are a different way of thinking about a calculation because everything happens “at once”.

    The bottom line is get it right before you get it fast.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, October 25, 2019 1:50 AM
  • To all,

    As usual, thanks for all your input.  I , as usual learned a lot.  Ken Sheridan's suggestion was the most appro and the one I used.  I was able to eliminate my Looping and replace it with 4 queries, which also improved performance.

    • Marked as answer by tkosel Friday, October 25, 2019 10:31 PM
    Friday, October 25, 2019 10:31 PM