Code to loop through all records in MS Access

Question

I need a code to loop through all the records in a table so I can extract some data  In addition to this  is it also possible to loop through filtered records and  again  extract data  Thanks

User · Answer

In  References   import DAO 3 6 object reference   private sub showTableData  dim db as dao database dim rs as dao recordset  set db   currentDb set rs   db OpenRecordSet  myTable    myTable is a MS-Access table created previously   populate the table rs movelast rs movefirst  do while not rs EOF    debug print rs myField   myField is a field name in table myTable    rs movenext              press Ctrl G to see debuG window beneath loop  msgbox  End of Table    end sub   You can interate data objects like queries and filtered tables in different ways   Trhough query   private sub showQueryData  dim db as dao database dim rs as dao recordset dim sqlStr as string  sqlStr    SELECT   FROM customers as c WHERE c country  Brazil    set db   currentDb set rs   db openRecordset sqlStr   rs movefirst  do while not rs EOF   debug print  cust ID     amp  rs id  amp    cust name     amp  rs name    rs movenext loop  msgbox  End of customers from Brazil    end sub   You should also look for  Filter  property of the recordset object to filter only the desired records and then interact with them in the same way  see VB6 Help in MS-Access code window   or create a  QueryDef  object to run a query and use it as a recordset too  a little bit more tricky   Tell me if you want another aproach   I hope I ve helped

User · Answer

You should be able to do this with a pretty standard DAO recordset loop  You can see some examples at the following links  http   msdn microsoft com en-us library bb243789 28v office 12 29 aspx http   www granite ab ca access email recordsetloop htm    My own standard loop looks something like this   Dim rs As DAO Recordset Set rs   CurrentDb OpenRecordset  SELECT   FROM Contacts     Check to see if the recordset actually contains rows If Not  rs EOF And rs BOF  Then     rs MoveFirst  Unnecessary in this case  but still a good habit     Do Until rs EOF   True          Perform an edit         rs Edit         rs VendorYN   True         rs  VendorYN     True  The other way to refer to a field         rs Update           Save contact name into a variable         sContactName   rs FirstName  amp       amp  rs LastName           Move to the next record  Don t ever forget to do this          rs MoveNext     Loop Else     MsgBox  There are no records in the recordset   End If  MsgBox  Finished looping through records    rs Close  Close the recordset Set rs   Nothing  Clean up

User · Answer

Found a good code with comments explaining each statement  Code found at - accessallinone  Sub DAOLooping   On Error GoTo ErrorHandler  Dim strSQL As String Dim rs As DAO Recordset  strSQL    tblTeachers   For the purposes of this post  we are simply going to make   strSQL equal to tblTeachers   You could use a full SELECT statement such as   SELECT   FROM tblTeachers  this would produce the same result in fact    You could also add a Where clause to filter which records are returned   SELECT   FROM tblTeachers Where ZIPPostal    98052     this would return 5 records   Set rs   CurrentDb OpenRecordset strSQL   This line of code instantiates the recordset object      In English  this means that we have opened up a recordset   and can access its values using the rs variable   With rs       If Not  BOF And Not  EOF Then      We don   t know if the recordset has any records        so we use this line of code to check  If there are no records       we won   t execute any code in the if  end if statement                MoveLast          MoveFirst          It is not necessary to move to the last record and then back           to the first one but it is good practice to do so           While  Not  EOF           With this code  we are using a while loop to loop           through the records  If we reach the end of the recordset   EOF           will return true and we will exit the while loop               Debug Print rs Fields  teacherID    amp       amp  rs Fields  FirstName                prints info from fields to the immediate window               MoveNext              We need to ensure that we use  MoveNext                otherwise we will be stuck in a loop forever                   or at least until you press CTRL Break          Wend      End If       close      Make sure you close the recordset    End With  ExitSub      Set rs   Nothing        and set it to nothing     Exit Sub ErrorHandler      Resume ExitSub End Sub   Recordsets have two important properties when looping through data  EOF  End-Of-File  and BOF  Beginning-Of-File   Recordsets are like tables and when you loop through one  you are literally moving from record to record in sequence  As you move through the records the EOF property is set to false but after you try and go past the last record  the EOF property becomes true  This works the same in reverse for the BOF property   These properties let us know when we have reached the limits of a recordset

[ms-access] Code to loop through all records in MS Access

Examples related to ms-access

Examples related to vba