Access VBA Macro Programming

QueryDefs

TheQueryDefscollection represents all the query definitions within the database. You can
iterate through this collection to see the names of all the queries and the underlying SQL:

Dim Qd As QueryDef
For Each Qd In CurrentDb.QueryDefs

MsgBox Qd.Name & " " & Qd.SQL

Next Qd

You can use this collection to change the SQL of a given query.

TableDefs

TheTableDefscollection represents all the table definitions within the database. You can
iterate through this collection to see the names of all queries and the underlying fields:

Dim Td As TableDef
For Each Td In CurrentDb.TableDefs

MsgBox Td.Name & " " & Td.Fields.Count

Next Td

This code will show the names of all the tables in the database and the number of fields in
each.

Recordset Object

TheRecordsetobject represents open recordsets based on tables or queries. It is used for
performing deletes, edits, and updates on records. It is usually used in conjunction with the
CurrentDb.OpenRecordset method:

Dim RecSet As Recordset
Set RecSet = CurrentDb.OpenRecordset("MyTable")
Do Until RecSet.EOF
MsgBox RecSet![MyField]
RecSet.MoveNext
Loop

This code is a simple example of how to iterate through a recordset based on the table
MyTable, showing the value of MyField for each record.
All the following examples use the variable RecSet to define theRecordsetobject.

Chapter 15: The Main Objects 207