Databases - MS Access - Modifying Tables

In order to modify the data in a table, you need to use a RecordSet.

The help says that a table must contain a unique index in order to be modified via code. This may not be true for tables in the current database (the current mdb file).

Opening a RecordSet

You must use the Set command. Some variables contain data and others are pointers (references, addresses) to data. Set assigns a reference and is always required.


When possible, open the recordset once, use it a lot, and then close it.

I did an experiment of adding about 100 records to 4 different tables. In one trial, I opened the recordsets for each addition; in the other, I opened each recordset once, added all the records, then closed everything. The difference - 2 seconds verses 50 seconds.

RecordSet Commands

AddNew and Edit both require an Update in order to save the changes. Moving to another record or closing the RecordSet before executing an Update will cause the changes to be lost.

The help implies that Delete also requires an Update, but, in fact, it doesn't.

The Find commands only work with dynaset- or snapshot-type Recordsets. They also require you to enter a search criteria.

Either single quotes or double quotes can be used.

RecordSet Properties

Copy and Paste

These are working examples.

With and Delete

This code creates n identical labels starting in the specified row and column. It demonstrates using the With and Delete commands.

Notice the use of _UIEdit in the "variable" names - I use this to differentiate between Form components and variables.

Author: Robert Clemenzi -
URL: http:// / user / clemenzi / technical / Databases / MSAccess / ModifyingTables.html