Adding Data Using a Recordset

 < 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:

  • First, call ADO's AddNew method to create a new row in the Recordset.

  • Next, supply the value for each required field.

  • Finally, call ADO's Update method to save the new row to the data source. If you forget to call the Update method, the new data is discarded without warning.


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 > 

    Automating Microsoft Access with VBA
    Automating Microsoft Access with VBA
    ISBN: 0789732440
    EAN: 2147483647
    Year: 2003
    Pages: 186 © 2008-2017.
    If you may any questions please contact us: