Modify Existing Data in Table, Access VBA Recordsets
In this article I will explain how you can modify existing data in an Access table using the Recordset object in VBA.
The 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:
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 “8”.
- Step 2: Change the value in the 3rd field (“MyField3”) of that record.
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 replace by the values in an arrray:
You can download the file and code used in this article from the link below:
- Read Data from Table, Access VBA
- Append Data to Table, Access VBA
- Microsoft MSDN: Recordset Object (ADO)
- Recordset.AddNew Method VBA Access
- Delete Record, Access VBA Recordset