Index Next

Characteristics of a Record Set

Once the table has been created, it is considered a record set. You can use it as the contents of a Recordset object

A Cursor

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.

there are various types of cursors:

• A static cursor holds a constant set of records. 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.  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. 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.

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:

• The below three scenarios allow a user to edit and/or update the records that are included in the set.
  • The below two options assume that you would lock many records to apply the indicated scenarios

1       When a computer connects to a database, its user may need to make changes to various records at the same time. 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

2       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

• If you prefer to lock one record at a time, you can use what is referred to as pessimistic locking.

3  This is done using the adLockPessimistic constant

• In some cases, you may want to prevent any editing or update on the records while the set is being accessed.

1       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