Database development and training for Birmingham and the midlands
Specialists in Microsoft Access and Visual Foxpro database training and development
I am happy to hear from existing clients but I am taking no new calls.
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.
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 selectsfrom 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.
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 rst As DAO.RecordSet
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:
txtName = rst.Fields![CompanyName]
txtCity = rst.Fields![City].Value
The reverse process is required when values are to be saved from the form to the table:
'-- Copy data from screen to the recordset
rst.Fields![CompanyName].Value = txtName
rst.Fields![City].Value = txtCity
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.
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: