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]](../media/images/dao.gif "Displaying data with Data Access Objects")
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
from the menu or presses
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
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:
rst.MoveFirst
RefreshMe
|
