Access VBA loop through Table records

This Access VBA tutorial explains how to loop through Table records or Query records in Microsoft Access.

You may also want to read:

Access VBA loop through all Tables using DAO.TableDef

Access VBA loop through Table records

Suppose we have an Acess Table or Query and you want to loop through the records (in Excel terms, to loop the values in each row), we have to make use of Recordset objects, which has different Methods to help us manipulate data in a database at the record level.

In this tutorial, I will demonstrate a standard Procedure to loop through Table records.

VBA Procedure

Press ALT+F11, copy and paste the below in a new Module.

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

To explain the key things:

• BOF checks if current record position is before the first record
• EOF checks if current record position is after the last record
• If BOF and EOF both return TRUE, then the Table has no record
• rs.MoveFirst ‘makes the first record the current record, in case current record is not the first record

VBA Procedure

Suppose employee_tbl contains the below information.

Run the Procedure, the result is printed in Immediate box.