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 and in many ways it is a better tool to use.

Advantages

[A simple form using DAO] Data Access Objects give you complete control over the entry of data. If you build a form using the default Access binding then the data will be saved when the user selects Records|Save Record from the menu or presses SHIFT+ENTER.

These are both explicit actions under the control of the user but Access will also save changes quietly when the user moves to a new record or if the user just closes the form. There are times when you want to intercept these automatic processes and ask the user 'Are you sure?' before saving the data.

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:

Private dbs As DAO.Database
Private rst As DAO.RecordSet

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:

Private Sub RefreshMe()

txtName = rst.Fields![CompanyName]
txtCity = rst.Fields![City].Value

Saving data

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

rst.Edit

'-- Copy data from screen to the recordset
rst.Fields![CompanyName].Value = txtName
rst.Fields![City].Value = txtCity

rst.Update

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 to save the data or a Cancel to abandon it.

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:

'-- Move to the first record and show that data
rst.MoveFirst
RefreshMe

MS Access technical tips

Visual FoxPro technical tips

General Tips

 

More tips from Alvechurch Data

Backing up an Access Database

How to backup an Access database using VBA and DAO

Read More

Generating primary keys for an Access database

How to generate a unique primary key for an Access database without using AutoNumber.

Read More

Linking a table in Access

Connecting external tables into Access from another database

Read More