Once you have opened an updateable recordset by
specifying the appropriate DAO Recordset object Type or ADO CursorType and LockType you can change, delete, or add new records using
methods of the Recordset object.
Adding New Records
Both DAO and ADO allow you to add new records to
an updatable Recordset by first calling the AddNew method, then specifying the values for the fields, and
finally committing the changes with the Update method.
The following code shows how to add a new record using DAO and ADO.
DAO
Sub DAOAddRecord()
Dim db As DAO.Database
Dim rst As DAO.Recordset
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset _
("SELECT * FROM Customers", dbOpenDynaset)
' Add a new record
rst.AddNew
' Specify the values for the fields
rst!CustomerId = "HENRY"
rst!CompanyName = "Henry's Chop House"
rst!ContactName = "Mark Henry"
rst!ContactTitle = "Sales Representative"
rst!Address = "40178 NE 8th Street"
rst!City = "Bellevue"
rst!Region = "WA"
rst!PostalCode = "98107"
rst!Country = "USA"
rst!Phone = "(425) 555-9876"
rst!Fax = "(425) 555-8908"
' Save the changes you made to the
' current record in the Recordset
rst.Update
' For this example, just print out
' CustomerId for the new record
' Position recordset on new record
rst.Bookmark = rst.LastModified
Debug.Print rst!CustomerId
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOAddRecord()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open "SELECT * FROM Customers", _
cnn, adOpenKeyset, adLockOptimistic
' Add a new record
rst.AddNew
' Specify the values for the fields
rst!CustomerId = "HENRY"
rst!CompanyName = "Henry's Chop House"
rst!ContactName = "Mark Henry"
rst!ContactTitle = "Sales Representative"
rst!Address = "40178 NE 8th Street"
rst!City = "Bellevue"
rst!Region = "WA"
rst!PostalCode = "98107"
rst!Country = "USA"
rst!Phone = "(425) 555-9876"
rst!Fax = "(425) 555-8908"
' Save the changes you made to the
' current record in the Recordset
rst.Update
' For this example, just print out
' CustomerId for the new record
Debug.Print rst!CustomerId
' Close the recordset
rst.Close
End Sub
DAO and ADO behave differently when a new record
is added. With DAO, the record that was current before you used AddNew remains current. With ADO, the newly inserted record
becomes the current record. Because of this, it is not necessary to explicitly
reposition on the new record to get information such as the value of an
auto-increment column for the new record. For this reason, in the ADO example
above, there is no equivalent code to the rst.Bookmark =
rst.LastModified code found in the DAO example.
ADO also provides a shortcut syntax for adding new
records. The AddNew method has two optional parameters,
FieldList and Values, that
take an array of field names and field values respectively. The following
example demonstrates how to use the shortcut syntax.
Sub ADOAddRecord2()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open "SELECT * FROM Shippers", _
cnn, adOpenKeyset, adLockOptimistic
' Add a new record
rst.AddNew Array("CompanyName", "Phone"), _
Array("World Express", "(425) 555-7863")
' Save the changes you made to the
' current record in the Recordset
rst.Update
' For this example, just print out the
' ShipperId for the new row.
Debug.Print rst!ShipperId
' Close the recordset
rst.Close
End Sub
Updating Existing Records
The following code demonstrates how to open a
scrollable, updateable Recordset and modify the data in
a record.
DAO
Sub DAOUpdateRecord()
Dim db As DAO.Database
Dim rst As DAO.Recordset
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset _
("SELECT * FROM Customers WHERE CustomerId = 'LAZYK'", _
dbOpenDynaset)
' Put the Recordset in Edit Mode
rst.Edit
' Update the Contact name of the
' first record
rst.Fields("ContactName").Value = "New Name"
' Save the changes you made to the
' current record in the Recordset
rst.Update
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOUpdateRecord()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open _
"SELECT * FROM Customers WHERE CustomerId = 'LAZYK'", _
cnn, adOpenKeyset, adLockOptimistic
' Update the Contact name of the
' first record
rst.Fields("ContactName").Value = "New Name"
' Save the changes you made to the
' current record in the Recordset
rst.Update
' Close the recordset
rst.Close
End Sub
Alternatively, in both the DAO and ADO code
examples, the explicit syntax
rst.Fields("ContactName").Value = "New Name"
can be
shortened to
rst!ContactName = "New Name"
The
ADO and DAO code for updating data in a Recordset is
very similar. The major difference between the two examples above is that DAO
requires that you put the Recordset into an editable
state with the Edit method. ADO does not require you to
explicitly indicate that you want to be in edit mode. With both DAO and ADO,
you can verify the edit status of the current record by using the EditMode property.
One difference between DAO and ADO is the
behavior when updating a record and then moving to another record without
calling the Update method. With DAO, any changes made
to the current record are lost when moving to another record without first
calling Update. ADO automatically commits the changes
to the current record when moving to a new record. You can explicitly discard
changes to the current record with both DAO and ADO by using the CancelUpdate method.
EmoticonEmoticon