So far, that is, in the previous lessons, we have been
concerned with one database accessed by one user. In many environments, a
database will need to be accessed by more than one computer. This means
that, when creating a Recordset object, you need to keep different
factors in mind. For example, you (actually your user) may be accessing a
record or a series of records at the same time with someone else. In some
cases, there may usually be only one person using a database and there
might occasionally be someone else but unlikely, although possible. In
some other cases, such as on the Internet or in a big enterprise, there
might be many people accessing, or trying to access, a database, or a
specific set of records, at the same time. Imagine you are working on a large database such as a
bank application that has thousands or millions of records (such as
thousands or millions of customers). If you want to perform an operation
on the customers, you may have to deal with many or all records. You may
also have to deal with the fact that other people are accessing the same
records like you, at the same time.
operations don't require you to have access to all records, at least not
all the time. When working on records, thus when creating a Recordset
object, you can specify a way to isolate a range of records and deal only
with that range. The range of records that you select is called a cursor.
Because a cursor plays a tremendous role in a record
set, there are different options when using it. To support these options,
there are various types of cursors:
- A static cursor holds a constant set of records. Suppose you create
a record set and open it. Also suppose that either you only or other
people besides you are working on the same record set. You get to a
record and start viewing it (or even working on it). After using that
record, you move to another record, and you can do this back and forth
as you wish. Suppose that, while doing this back and forth navigation (we will learn later on how to programmatically navigate
through a record set),
another person has accessed a record that is part of your record set
and made a change. If using a static cursor, every time you visit the
record set, it shows the same records the way they were
when you opened the record set. It would not show the changes that
have taken place. This is why it is called a static cursor. A static
cursor is appropriate if you are not interested to know what changes
have taken place ever since you opened the record set.
In ADO, a static cursor is represented with the adOpenStatic constant.
- A cursor is referred to as forward-only if it allows you to move
forward through the records. Here is how it works. Suppose that you
create a Recordset object and specify its cursor. Suppose that, while
using the record set, you get to a record that was set as the start
point of your cursor. Also, suppose that either you only or other
people besides you are working on the same record. If you make a
change on the current record, the other people will be notified. If
other people make a change on the current record, you also would know.
After using that record, you move to the next. With the forward-only
cursor, you cannot move back to a record you left already. This means
that, even if you are still working on the record set, if there are
changes performed on a record you left behind (for example, if another
person who is working on the same record changes something on a record
that you passed already), you cannot know and you cannot find out
because you cannot go back to a record left behind. If this becomes a
necessity, you can close the cursor and re-open it. A forward-only
cursor is appropriate if you don't need to navigate back and forth
among the records of a record set.
In ADO, this type or cursor is represented by the adOpenForwardOnly
- A cursor is called dynamic if it detects and shows all changes that
are occurring in a record set, whether the changes are caused by you
or by other people who are accessing the record set at the same time.
This type of cursor is appropriate if you want to know, live, what is
going on with the record set you are working with.
In an ADO database, to specify a dynamic cursor, use the adOpenDynamic
- A key set cursor creates and saves a key for each record that has
been modified since the record set was opened. If you access the
record, the key is used to check the data of the record set.
A key set cursor is created using the adOpenKeyset constant.
- If you don't want to specify a cursor when creating a record set,
you can use the adOpenUnspecified constant.
In Lesson 4, we
introduced database connections and showed different ways of creating on.
We have used these techniques ever since Lesson 4. When creating a record
set, in some cases, especially when using ADO, you may need to specify
what connection the Recordset object would use.
Imagine that, after creating a record set and working
on it, you want to control who else can have access to the records of the
set you are using. To exercise this control, you can create a
"lock". This allows you, for example, to prevent other people
from changing the records until you have finished with them. To support
locking, the ADO library provides various options:
- When a computer connects to a database, its user may need to make
changes to various records at the same time, such as deleting a range
of records or changing many records at the same time (such as giving a
raise to many employees), instead of making one change, then another,
then another. For this type of scenario, when the user accesses the
records, instead of monopolizing them and waiting for the user to
finish an operation that could take long, you can download the records
on the user's computer, and disconnect the user from the database. The
user would then make the necessary changes. When the user is ready to
commit the changes, you can then reconnect to the data source and
submit the changes. This type of lock is referred to as batch
optimistic. ADO supports this through a constant value named adLockBatchOptimistic
- You may have a database that a few different people access at the
same time. If the database is small enough, which is the case for
restricted environment, the likelihood of two people editing or
updating the same record (at the same time) may be low. In this case,
you can indicate that you want to lock the record only when necessary.
In this case, you use what is referred to as optimistic locking. This
is implemented using the adLockOptimistic value
- The above two options assume that you would lock many records to
apply the indicated scenarios. If you prefer to lock one record at a
time, you can use what is referred to as pessimistic locking. This is
done using the adLockPessimistic constant
- The above three scenarios allow a user to edit and/or update the
records that are included in the set. In some cases, you may want to
prevent any editing or update on the records while the set is being
accessed. In this case, you can set the records to read-only. To do
this, you can lock the set using the adLockReadOnly lock
- If you don't want to specify the type of lock system to use on a
record set, use the adLockUnspecified value