4.3.3 Working with Recordsets: Adding Records

In Access VBA, Recordsets allow you to interact with the data in your database, including adding new records. The AddNew method is used to add new records to a table or query through a Recordset. After adding a new record, you must call the Update method to save it to the database.

This section will explain how to add new records using Recordsets, the key steps involved, and provide an example.

1. Basic Steps to Add Records with Recordsets

To add a new record using VBA, follow these general steps:

1. Open a Recordset: Open a Recordset in Edit mode to allow adding records.
2. Use the AddNew Method: Call the AddNew method to prepare the Recordset to accept new data.
3. Set Field Values: After calling AddNew, assign values to the fields (columns) of the new record.
4. Call the Update Method: After setting all the field values, call Update to save the record to the database.
5. Close the Recordset: Finally, always close the Recordset when done.

2. Using the AddNew Method

The AddNew method is called on a Recordset object to add a new record. After calling AddNew, you can set the values for the fields of the new record.

Key Syntax:

Recordset.AddNew
Recordset!FieldName = Value
Recordset.Update

• AddNew: Prepares the Recordset for adding a new record.
• FieldName: Refers to the name of the field/column in the table (or query).
• Value: The value you want to assign to that field.

3. Example of Adding a Record

Here's an example that demonstrates adding a new record to the Employees table using VBA:

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset) ' Open table in Dynaset mode

' Begin adding a new record
rs.AddNew

' Set values for the fields of the new record
rs!EmployeeName = "John Doe"
rs!Position = "Software Developer"
rs!Salary = 50000
rs!Department = "IT"

' Save the new record to the database
rs.Update

' Close the Recordset
rs.Close
Set rs = Nothing
Set db = Nothing

In this example:

1. The Employees table is opened in Dynaset mode (which allows you to edit records).
2. The AddNew method prepares the Recordset to accept a new record.
3. Values are assigned to the fields EmployeeName, Position, Salary, and Department.
4. The Update method is called to save the new record to the table.

4. Adding Multiple Records in a Loop

If you need to add multiple records, you can use a loop to iterate through the data and call the AddNew method for each record.

Example of Adding Multiple Records:

Dim db As Database
Dim rs As Recordset
Dim EmployeeData As Variant
Dim i As Integer

' Sample data to add
EmployeeData = Array(Array("Jane Smith", "Manager", 75000, "Sales"), _
                     Array("Paul Brown", "Analyst", 55000, "Finance"), _
                     Array("Lisa White", "HR Specialist", 45000, "HR"))

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset)

' Loop through the data and add each record
For i = LBound(EmployeeData) To UBound(EmployeeData)
    rs.AddNew
    rs!EmployeeName = EmployeeData(i)(0)
    rs!Position = EmployeeData(i)(1)
    rs!Salary = EmployeeData(i)(2)
    rs!Department = EmployeeData(i)(3)
    rs.Update
Next i

' Close the Recordset
rs.Close
Set rs = Nothing
Set db = Nothing

In this example:

• An array EmployeeData is used to store multiple records.
• The loop iterates through the array, adding each record using the AddNew method and setting field values for EmployeeName, Position, Salary, and Department.
• After each new record is added, the Update method is called to save it.

5. Error Handling for Adding Records

It’s important to include error handling when working with recordsets, especially when adding new records, to ensure that the process is safe and doesn’t cause issues with your database.

Example with Error Handling:

Dim db As Database
Dim rs As Recordset

On Error GoTo ErrorHandler ' Define error handling

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset)

' Begin adding a new record
rs.AddNew

' Set values for the new record
rs!EmployeeName = "John Doe"
rs!Position = "Developer"
rs!Salary = 60000
rs!Department = "Engineering"

' Save the new record to the database
rs.Update

rs.Close
Set rs = Nothing
Set db = Nothing

Exit Sub ' Normal exit

ErrorHandler:
    MsgBox "Error: " & Err.Description
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

In this example:

• Error handling is set up using On Error GoTo ErrorHandler.
• If any error occurs during the process, the ErrorHandler block will display an error message, close the Recordset, and clean up the objects.

6. Closing the Recordset

Once you have finished adding records, always remember to close the Recordset to release resources and avoid any database locking issues. This can be done using the Close method as shown in the examples above.

Conclusion

Adding records using Recordsets in VBA is a powerful feature for interacting with databases. By using the AddNew and Update methods, you can easily add single or multiple records to your tables. Always ensure that proper error handling is in place to manage potential issues and close the Recordset once done. This will allow your VBA code to effectively manage data in Access.