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!

Answer 1

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 & " " & 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