Characteristics of a Record Set

To work on a record set, you must communicate to the database engine what rules your set will follow to be considered as such. In Lessons 8 and 9, we saw how to create a table and its columns. In Lesson 12, we saw how to populate a table with data. We saw that a table in Lesson 10 or a query in Lesson 15 could be as the record source of a form (or as that of a report). Once the table has been created, it is considered a record set. You can use it as the contents of a Recordset object.

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.

Normally, some 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 constant.
• 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 constant.
• 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.

 

The Lock Type

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