Databases Reference
In-Depth Information
1
2
3
Index
Index
Index
BOF
BOF
BOF
a
1
2
3
4
a
1
a
1
2
3
4
5
2
3
4
5
b
c
d
e
EOF
Original
b
d
e
EOF
b
d
e
c
EOF
Element “c”
deleted
Element “c”
added
FIGURE 8.1
Dynamic indexing challenges in database Record sets.
At the top and bottom of each database are the terms BOF and EOF, which stand for Beginning
Of File and End Of File, respectively. BOF and EOF are Properties that can be accessed using
Excel VBA. The definition of these two properties is:
BOF — Indicates that the current record position is before the first record in a Record set
object.
EOF — Indicates that the current record position is after the last record in a Record set object.
It will be shown later in this chapter how to utilize the BOF and EOF properties to create loop
structures that allow the developer to create routines that loop through every element or a chosen
subset of elements in a database.
Looking at Figure 8.1, let us suppose the element (c), which has an index of 3, is deleted from
the database. (This scenario is shown in Section 2.) This change causes two profound effects that
should be of concern to the developer. First, the number of records in the database is decreased by
1 from 5 to 4. Second, the indexes of elements (d and e) are now reduced by 1.
Upon inspection, an answer that comes to mind is to simply set the number of loops to an
infinitely high maximum that the database should never attain and simply loop through the elements
until a NULL is encountered, which indicates the end of the records. The problem with such an
approach is that when a record is accessed beyond the end of the last index in the record set, it
does not return a null but triggers an error. Thus, such an approach is not practical.
Another thought that comes to mind is to keep a certain
element as the last record in
the database, thus marking the end of the records in the database (in this example “e”). As soon
as the unique element is encountered, an exit for statement could terminate the looping structure
in the database. This solution is also impractical for the reasons shown in Figure 8.1, section (3).
Suppose another element is now added to the database — in this example, “c” is put back into
the database. Now “e,” the supposed “last record” in the database, precedes the record “c.” Such
a looping structure would now terminate prematurely before accessing every element in the data-
base. Although it is true that a dynamic loop counter could be incremented with each record added,
and decremented with each record deleted, such a system is hardly a model of efficiency. That is
why using the BOF and EOF properties when dealing with databases is so important for maintaining
clean and easy-to-understand code.
Also notice that, in Figure 8.1, section (3), the indexes of records (d,e,c) are now different from
the original database. To complicate matters even further, if the user implements complex SQL
commands to manipulate the database, it will not be possible to count the number of records added
or deleted utilizing a counter. Thus, pointers for individual database records or elements are
impractical and should not be used.
One situation in which pointers
unique
be utilized is when populating dynamic arrays. Often-
times when coding, it is convenient to store the results of a query in an array or to use an array to
populate a list box or combo box on a GUI. Looking at Figure 8.2, if elements (a,c,d) of the example
must
