Databases - Using DAO

Tuesday Nov 19th 2002 by John Percival
Share:

Databases - Using DAO

We saw last time how to use the data control to display and edit records in a database. This week, we will look at how to achieve the same results by using the built in database commands.

Types of Recordset

There are several types of recordset that you can create, and which one you choose depends on several things. You will meet four difference types when using the MS Jet engine:

  • Table-type Recordset - this type is formed from fields from only one table. You can add, change and delete records, and this is reflected in the actual database. Movement is unrestricted.
  • Dynaset-type Recordset - this type can be made up from more than one table, and is editable, as the table type. Movement is unrestricted. If you are only accessing one table, use the table type recordset.
  • Snapshot-type Recordset - this is a static copy of the original database data. It can be from multiple tables, but none of the original data will be updated. Movement is unrestricted.
  • Forward-only-type Recordset - this is identical to the snapshot type, except that movement is restricted to moving forwards only. This is useful if you want to make just one pass, for example in report creating.

This type can be set as a property of the data control, or when the RecordSet is opened using OpenRecordSet.

Although the data control does not allow us to run SQL queries directly, it is possible to assign the RecordSet object which the data control reads from. We will not take an in depth look at SQL here, as that would require an entire book, but we will have a look at the practical basics of the SELECT statement.

The overall form of the SELECT query is as follows:

SELECT fieldlist
FROM tablenames IN databasename
WHERE searchconditions
GROUP BY fieldlist
HAVING group criteria
ORDER BY fieldlist
WITH OWNERACCESS OPTION

fieldlist contains the fields that you want to return. These are separated by commas, and if they contain a space, are enclosed in square brackets []. If you want to return all fields, use the * operator. If you accessing more than one table, refer to the fields by:

tablename.fieldname

tablenames and databasename are the tables from which you wish to retrieve the data. Multiple ones are separated by commas, as above. You can use the database name parameter if there are tables in other databases, but this is beyond the scope of the article, and you can look in help for more info.

searchconditions is where this statement's power really is. You can use it to limit the records selected to within only certain parameters. This is similar to the operations that you can do with the If statement in VB, and multiple statements can be linked with AND and OR. See help file for more details.

The GROUP BY and HAVING parts are used to group and select certain records when using aggregate functions, such as SUM, AVG, MIN and MAX.

ORDER BY selects which field is used to sort the returned records.

WITH OWNERACCESS OPTION is used in multi-user environments to enable other users to access the table data while you are.

That is only a very brief introduction to the SELECT statement. It is so useful because, as it is a string, you can include variables, such as textboxes, so that the user can change the data that is returned.

Well, now that we have got the basic idea of what the SELECT statement can do, let's see some examples. These examples require a Data control called Data1, and you may have to modify the path to the database:

Dim db As Database
Set db = OpenDatabase("C:Program _
  FilesDevStudioVBBiblio.mdb")
Set Data1.Recordset = db.OpenRecordset("SELECT _
  * FROM Titles WHERE Title Between 'A' _
  And 'B';")

The best way to display the data is to use a MS FlexiGrid, setting the DataSource property to Data1. The SELECT statement can be used to build very complex queries, and can be nested inside other SELECT statements, as well as joining to other tables.

Note that when you have set the data control's record set using this method, you must keep the database the you got the data from open, otherwise moving between records will fail.

You can move through a Recordset in code using the MoveFirst, MovePrevious, MoveNext and MoveLast methods. If you are at the first record, and you use the MovePrevious method, the BOF property will be set to true, and there will be no current record. If you cal it again, an error occurs. A similar thing happens with MoveNext.

Before you move to a different record, you should make sure that you invoke the Update method to save any changes to the current record.

When you are working with dynaset or snapshot type recordsets, the data is not fully populated when the recordset is created. To populate the recordset, execute MoveLast to move to the end of the data. You should not do this too often, as it has an impact on the application's performance. To improve this problem, you could call the MoveLast method with the dbRunAsync parameter to call it asynchrous. This means that the call returns immediately, and the database does the work in the background. To see if the call is completed, check the StillExecuting property, and to stop it, call the Cancel method.

To move more than one record at a time, use the Move method. This moves forwards or backwards a number of records relative to the current record, depending on the parameter. To move to a set record, set the AbsolutePosition property.

To find certain criteria within the recordset, use the FindFirst, FindLast, FindNext and FindPrevious methods. These all do a search through the recordset, starting either and the beginning (FindFirst), end (FindLast) or at the current record. FindLast and FindPrevious both search backwards through the data, and FindFirst and FindNext search forwards. The search criteria are similar to what you would use in the SELECT statement found above, and more information can be found in the help file. However, these methods only work for dynaset and snapshot type recordsets. If you have a table type recordset, use the seek method. You may find that for large recordsets, these are quite slow, and you may have to revert to a SQL SELECT statement to speed things up.

Adding Records

This is extremely simple to do: just use the AddNew method. This adds a new record to the end, and selects it as the current record.

Accessing Text Databases

If you do not need all the features of MS Access databases, then a simple text database may be all you need. This will probably be quick than code created by hand, and will save lots of extra coding.
The essential part of text databases is the schema.ini file. All you need to know is in the help file, as usual, under "Initializing the Text Data Source Driver". In here, it shows you how to create the necessary information in the schema.ini file to load the text file. This file defines the fields and delimiters between them, so that the database engine can load the text file. Then you can access and change it as you would any other recordset.

That's about it for coding databases with DAO. Some of the things in this article refer only to MS Jet data sources, so check that the data source that you are using supports the methods before you start to use them.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved