Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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.
