DAO with Access 97

The link between an Access form and its data is so seamless and automatic that many developers do not even realise that there is an alternative. Using DAO (Data Access Objects) to bind a form to its data does mean more work but brings greater flexibility and the possibility of higher performance.

Later versions of Access have Projects where ADO (ActiveX Data Objects) can be used to connect an Access form to a data source. Access 97 predates ADO but the earlier DAO can be used instead.

Advantages

Data Access Objects give you complete control over the entry of data. Using the default Access binding the data is saved when the user selects Records|Save Record from the menu or presses SHIFT+ENTER. These are explicit saves under the control of the user but Access will also save changes quietly when the user moves to a new record. There are times when you want to intercept this process and ask the user 'Are you sure?'. DAO gives you this sort of control. It also gives you as the developer the opportunity to provide a better mechanism to share database access between multiple users.

Example

The first thing to do is to define the database and recordset as Private within the form so that all code within this module can make use of them:

Loading data

All the controls on the form are unbound and have an empty ControlSource property. Instead, the following code is used to put values from the fields of the recordset into the text boxes:

Saving data

The reverse process is required when values are to be saved from the form to the table:

Note the need to put the DAO recordset into Edit mode before values can be saved and the need to request an update of the recordset afterwards. Be very careful with these methods. An error will occur if every Edit is not matched with an Update or Cancel.

Navigation

Because the controls are not bound to fields of the table, the normal form navigation buttons cannot be used. New navigation buttons are needed with code such as: