104
5.6 Record sets (DAO)
When we need complex record handling, there are two
ways to go: through SQL statements that update
records, or through programmed record access. In this
section we look at the latter possibility. The SQL way
is explained in section 7.1)
Select the database model. Before you can try the
following example, you should make sure that your
program accesses the database with the right method.
Over the years, Access has used different methods to
access the tables, trying at all times to be compatible
with earlier versions. Below we will use the DAO ap-
proach which works across all Access versions. Access
97 is born with DAO, but in Access 2000 and 2003 you
have to select DAO. Do as follows:
1. Open Visual Basic and select Tools -> References.
You now see a list of the libraries that Access may
use. At the top of the list, you see those selected at
present. When VBA looks for a built-in class name,
it first looks in the top library. If the name is not
there, it looks in the next library, and so on.
2. If MicroSoft DAO 3.6 Object Library is not se-
lected, go far down the list and select it. Next move
it to the top of the list. The easiest way is to close
the list, then reopen it. Now DAO 3.6 is in the top
list. Move it further up as far as it can go. Then
VBA will find the DAO 3.6 names first.
There is no reason to restart the system. You may no-
tice that in the Object Browser and with Ctrl+J you will
now see two RecordSet classes. Use the one with an
Edit property.
5.6.1 Programmed record updates
As the first example we will outline how the CheckIn
button on the Stay form could work (Figure 5.6A).
3. Open frmStay in design view. (If you followed the
earlier exercises closely, there are no buttons on the
form.)
4. Create a Book button and a CheckIn button and
give them names with the cmd prefix.
5. Define the event procedure for the OnClick event
on the CheckIn button. Figure 5.6A shows the body
of the procedure. It demonstrates many new things
that we explain below.
Declarations. The first lines of the procedure declare
two variables. The variable s can hold a text string. The
variable rs can hold a Recordset, or more precisely, it
holds a reference (a pointer) to a Recordset. These
variables are local for the procedure, which means that
VBA creates them when it calls the procedure, and de-
letes them when it returns from the procedure. Initially
they both have the value Nothing.
Computed SQL
The statement s="SELECT . . . " computes a text and
stores it in the variable s. If the form shows stay 728,
then s will hold this text:
SELECT * FROM tblRoomState
WHERE stayID=728;
It is an SQL statement that selects all fields from the
RoomState records that belong to the stay. As you see,
the program computes this SQL statement from three
parts, the text "SELECT . . . ", the expression
Me.stayID and the text ";".
If you are not fluent in SQL, it may be easier to make
the query with the query grid. Then switch to SQL-
view and copy and paste the statement into the pro-
gram. VBA makes a lot of noise about this non-VBA
statement, but just modify the statement with quotation
marks, &-operators, and so on. As usual, don't care
about capitalization. SELECT may for instance be
written with small letters. VBA doesn't look at what is
inside the quotes and the SQL-engine doesn't care
about caps.
Warning. You may wonder why we have to compute
the SQL. When working with the query grid, we could
write things like
WHERE stayID=Forms!frmStay!stayID
Why don't we write something similar here, for in-
stance
WHERE stayID=Me.stayID
The answer is that in VBA we compose the final SQL-
statements directly. When working with the query grid,
Access translates our SQL-statement into the final SQL
version. As part of this, Access finds the current value
of Forms!frmStay!stayID and inserts it into the SQL-
string that it passes to the database engine. In other
words, it does the same kind of work that our VBA
program does.
Open the Recordset. The statement
Set rs = CurrentDB.OpenRecordset(s)
creates a Recordset and stores a reference to it in rs.
The command Set says that we want to store the ref-
erence, not the Recordset itself. CurrentDB is the da-
tabase currently used by the program, and we ask it to
open a record set that gives us the records specified by
the SQL statement in s. When Access has opened the
record set, the first record in the set becomes the cur-
rent record. If the set is empty, there is no current
record, and End Of File (EOF) is true.
While loop. The statements from While to Wend are a
loop. The four statements inside the loop are repeated
until rs.EOF becomes True. This happens when the
program tries to move the current record beyond the
102
5. Access through Visual Basic
