Chapter 13: Accessing Data with VBA Code
517
Adding a new record
You can use ADO to add a record to a table just as easily as updating a record. Use the AddNew
method to add a new record to a table. The following shows an ADO procedure for adding a new
customer to tblCustomers:
Private Sub AddNewCustomer(FName As String, LName As String)
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open “tblCustomers”, CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic
With rs
.AddNew ‘Add new record
‘Add data:
![LastName] = LName
![LastName] = FName
.Update ‘Commit changes
End With
rs.Close
Set rs = Nothing
End SubAs you see in this example, using the AddNew method is similar to using ADO to edit recordset
data. AddNew creates a buffer for a new record. After executing AddNew, you assign values to
fields in the new record. The Update method adds the new record to the end of the recordset,
and then to the underlying table.
Deleting a record
To remove a record from a table, you use the ADO method Delete. The following code shows an
ADO procedure for deleting a record from tblCustomers.
Private Sub DeleteContact(CustomerID As Long)
Dim rs As ADODB.Recordset
Dim strSQL as string
Set rs = New ADODB.Recordset
‘Select single record from tblCustomers:
strSQL = “SELECT * FROM tblCustomers “ _
& “WHERE [CustomerID] = “ & CustomerID
rs.Open strSQL, CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic
With rs
If not .EOF Then
.Delete ‘Delete the record
End If
End With
rs.Close
Set rs = Nothing
End Sub