Read Data from Table, Access VBA
In this article I will explain how you can read data from an Access table using VBA.
This article uses the ADODB.Recordset object. In order to use this object you may need to add reference to ADODB object library. For more information on this topic please see the link below:
As you can see the table has 4 fields:
The following code will initiate an ADODB.Recordset object for the table:
Note: The code above will initiate a recordset object that will pointing at the first record of the access table.
Note: Rather than opening the entire table, we could have used a query to filter the data and return only the values we need. For example we could have replace the last line of the code above with the code below to only return values from the table whose first field has a value greater than 15:
For more information about this topic please see the link below:
By using the code below we can move the recordset pointer to the next record:
Example: The code below will loop through all the records in the recrodset:
The value of the current record the recordset object is pointing at can be read through the
collection. The code below will return the value in the first field of the current record the Recordset object is pointing at:
Note: The Fields collection of the Recordset object is a zero based collection. Therefore the first field is reference by the index “0” and the 4th field is referenced by the index “3”.
Note: The Recordset object can be moved to the previous record using the code below:
Note: The Recordset object can be moved to the last record using the code below:
Note: The Recordset object can be moved to the first record using the code below:
In this example we are going to the:
- Step 1: Search the table for the record whose first field has the value “14”.
- Step 2: Display the value in the 3rd field (“MyField3”) of that record in a message box.
Note: “MyField3” is the 3rd field therefore is reference through the index “2”:
In this example all the values in the second field (“MyField2”) will be stored in an array:
In this example we will loop through the values of all records whose first field has value between 5 and 10. Although we could have used the method explained in the previous examples, but a faster method would be using a Select Query:
You can download the file and code used in this article from the link below:
- Modify Existing Data in Table, Access VBA Recordsets
- Microsoft MSDN: Recordset Object (ADO)
- Append Data to Table, Access VBA
- Recordset.AddNew Method VBA Access
- Delete Record, Access VBA Recordset