Access VBA loop through Table records

Posted on by Wyman W

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.

employee_tbl
Employee ID Employee Name Department Team Salary
001 David HR Compensation 10000
002 Mary Finance Reporting 20000
003 Peter HR HR System 30000
004 Apple HR Compensation 40000
005 June IT Operation 50000

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

 

Wyman W
Wyman is human resources professional specialized in implementation of HR information system.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

2 thoughts on “Access VBA loop through Table records”

  1. Hello, i’ve change my sql and instead of 2 query , i’ve got only one. And this is my code :

    Public Sub selectSQL()

    Dim qdf As QueryDef
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim Table1 As DAO.TableDef
    Set db = CurrentDb

    For Each tdf In db.TableDefs
    If tdf.Name Like “G10*” Then
    strSQL = “INSERT INTO Table1 ( Ligne, DateJ, Heure, CodPro, Pds_insuf, Pds_Acc )” _
    & “SELECT Max(IIf([ID]=7,[Champ1])) AS Ligne, Max(IIf([ID]=9,Right(Left$([Champ1],17),10))) AS DateJ, Max(IIf([ID]=9,Right([Champ1],8))) AS Heure, Max(IIf([ID]=15,Right([Champ1],6))) AS CodPro, Max(IIf([ID]=43,Right(Left$([Champ1],30),9))) AS Pds_insuf, Max(IIf([ID]=45,Right(Left$([Champ1],30),9))) AS Pds_Acc” _
    & “FROM tdf.name;”
    On Error Resume Next
    DoCmd.DeleteObject acQuery, “tempQry”
    On Error GoTo 0
    Set qdf = CurrentDb.CreateQueryDef(“tempQry”, strSQL)
    DoCmd.OpenQuery (“tempQry”)
    End If

    Next
    End Sub

    But here is the error message : La requête doit être construite à partir d’au moins une table ou une requête source.
    I don’t understand.
    Can you help me, i think i’m not too far from the goog result ! thank you. Valérie

    Reply

  2. Hello, i’ve changed my code about the ‘tdf.name’. Now i’ve got a problem with the criteria ‘like “G10*”, that doesn’t recognise it. Nothing is adding in my Table1.
    code :
    Public Sub Table1_SQL()

    Dim strSQL As String
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim Table1 As DAO.TableDef
    Set db = CurrentDb

    For Each tdf In db.TableDefs
    If tdf.Name Like “‘G10*'” Then

    strSQL = “INSERT INTO Table1 ( Ligne, DateJ, Heure, CodPro, Pds_insuf, Pds_Acc )” _
    & ” SELECT Max(IIf([ID]=7,[Champ1])) AS Ligne, Max(IIf([ID]=9,Right(Left$([Champ1],17),10))) AS DateJ,” _
    & ” Max(IIf([ID]=9,Right([Champ1],8))) AS Heure, Max(IIf([ID]=15,Right([Champ1],6))) AS CodPro,” _
    & ” Max(IIf([ID]=43,Right(Left$([Champ1],30),9))) AS Pds_insuf, Max(IIf([ID]=45,Right(Left$([Champ1],30),9))) AS Pds_Acc” _
    & ” FROM ” & tdf.Name & “;”

    DoCmd.RunSQL strSQL

    End If

    Next
    MsgBox (“terminé”)
    End Sub

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *