|< Day Day Up >|
At some time, you might need to add new data using a Recordset object. The process is a little more involved than just viewing or finding data. Adding a new record requires three actions:
A Recordset object isn't the only way to add new data. The ADO Command object can execute a SQL statement directly against the data source. The Command object is covered in "Working with Command Objects," p. 242 in Chapter 16.
Let's look at a simple example that inserts new employees into TimeTrack's Employees table:
Sub AddEmployee(fname As String, lname As String) 'Enter new employee record Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset With rst .Open "Employees", CurrentProject.Connection, adOpenDynamic, _ adLockPessimistic .AddNew .Fields("FirstName") = fname .Fields("LastName") = lname .Update End With rst.Close Set rst = Nothing End Sub
Add the procedure to a standard module or use Chapter 17's example module. In the Immediate window, run the statement
AddEmployee "Susan", "Harkins"
(You can use any names you like.) Then, open the Employees table and you'll find the new employee record, as shown in Figure 17.4.
Figure 17.4. You can use an ADO Recordset object to add new records.
After opening and populating the Recordset, the AddNew method makes room for the new record. Then, the next two statements supply the values for the new record. Finally, the Update method commits the new data. The Update method takes the form
recordset.Update [fields] [, values]
where fields identifies the fields you're updating and values supplies the data that's replacing the existing value.
You might be wondering why we didn't add a value for the EmployeeID field. That field is an AutoNumber field and Access adds the value when the new record is committed. An attempt to add a value to that field returns an error.
When adding new data, you really need to know the underlying data source. Failing to supply a required value or the right data type returns an error. In addition, this example contains no error handling. When you're adding new data, you probably want to include routines for catching common errors. Consider, for instance, whether the record creates a key violation (duplicates), or whether any of the new values violate any validation rules.
|< Day Day Up >|