Adding a new record to a dynaset- or table-type Recordset object is a three-step process:
The following example adds a new record to a table called Shippers. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Shippers")
With rst
' Add new record.
.AddNew
' Supply values for fields.
!CompanyName = "Global Parcel Service"
!Phone = "(503)555-9786"
' Update.
.Update
.Close
End With
When you use the AddNew method, Microsoft Jet prepares a new, blank record and makes it the current record. When you use the Update method to save the new record, the record that was current before you used the AddNew method becomes the current record again. The new record’s position in the Recordset object depends on whether you added the record to a dynaset- or a table-type Recordset object.
If you add a record to a dynaset-type Recordset object, the new record appears at the end of the Recordset object, no matter how the Recordset object is sorted. To force the new record to appear in its properly sorted position, you can use the Requery method or re-create the Recordset object.
If you add a record to a table-type Recordset object, the record appears positioned according to the current index, or at the end of the table if there is no primary key and no current index. Because Microsoft Jet version 3.5 allows multiple users to create new records on a table simultaneously, your record may not appear at the end of the Recordset object as it did in previous versions of Microsoft Jet. Be sure to use the LastModified property rather than the MoveLast method to move to the record you just added.
Important If you use the AddNew method to add a new record, and then move to another record or close the Recordset object without first using the Update method, your changes will be lost without warning. For example, omitting the Update method from the preceding example results in no changes being made to the Shippers table.
You can use a dynaset-type Recordset object to create a local cache for ODBC data. Creating a local cache lets you retrieve records in batches instead of one at a time as each record is requested, and makes much better use of your server connection, improving performance.
The CacheSize and CacheStart properties establish the size and starting offset (expressed as a Bookmark property value) for the cache. For example, you may set the CacheSize property to 100 records. Then, by using the FillCache method, you can retrieve sufficient records to fill the cache.
See Also For more information about caching ODBC data, see Chapter 9, “Developing Client/Server Applications.”