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 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.
Compensation Survey Manager@Mercer
Wyman is human resources professional, specialized in business analysis, project management, data transformation with Access and Excel.
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
You may drop me a message if you are interested in HR consultancy services from Mercer.
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
You may drop me a message if you are interested in HR consultancy services from Mercer.
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
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