ADO Recordset Object
The ADO Recordset object is used to accommodate a record set from a database table. A Recordset object consists of records and columns (fields).
In ADO, this object is the most important and most commonly used to operate on the data of a database.
ProgID
set objRecordset=("")
When you first open a Recordset, the current record pointer points to the first record, with the BOF and EOF attributes False. If no record is recorded, the BOF and EOF attributes are True.
The Recordset object can support two update types:
Update Now - Once the Update method is called, all changes are written to the database immediately. Batch Update - provider caches multiple changes and then uses the UpdateBatch method to transfer these changes to the database.
In ADO, different cursor (pointer) types are defined in 4:
• Dynamic Cursor - Allows you to view additions, changes, and deletions made by other users
•Keyset Cursor - Similar to dynamic cursors, the difference is that you cannot view the additions made by other users, and it will prevent you from accessing records that other users have deleted. Data changes made by other users are still visible.
• Static Cursor - Provides a static copy of the record set that can be used to find data or generate reports. Additionally, additions, changes, and deletions made by other users will be invisible. This is the only allowed cursor type when you open a client Recordset object.
•Forward Cursor Only - Only allow forward scrolling in Recordset. Additionally, additions, changes, and deletions made by other users will be invisible.
The cursor type can be set through the CursorType property or the CursorType parameter in the Open method.
Note: Not all providers support all methods and properties of Recordset objects.
Friends who want to know more about ADO Recordset objects can go tohereSee more.
If it is just reading and does not involve update operations, then use 1 and 1
If it involves reading and updating operations, you can use 1, 3 or 3, 2
The Open method prototype of the Recordset object:
Open（[Source],[ActiveConnection],[CursorType],[LockType],[Options]）
CursorType cursor type:
Const adOpenForwardOnly = 0
Forward cursors provide the fastest running performance as the default cursor. Use it to open the recordset and get all results from pair to tail. It does not support backward scrolling, and only allows one-way movement between results.
Const adOpenKeyset = 1
Static cursor reflects the status of the data in the table when the cursor is opened for the first time. The cursor cannot find out whether the data rows in the underlying table have been updated, deleted, or added new data. However, unlike the continental markers that can only move forward, static cursors can scroll back and forth between results.
Const adOpenDynamic = 2
A keyboard-driven cursor can query some changes in the underlying data rows in a table, but not all. It can especially accurately reflect whether the data has been updated. But it cannot find out whether other users have deleted data rows (deleted data rows will leave holes in the recordset). The keyboard-driven cursor supports scrolling back and forth between results.
Const adOpenStatic = 3
Dynamic cursors are the richest types of cursors. When the cursor is opened, you can query any changes to the table by other users, and scrolling is supported.
LockType lock type:
Const adLockReadOnly = 1
The default locking type, read-only locking allows multiple users to read the same data at the same time, but cannot change the data.
Const adLockPessimistic = 2
Open the data object in a pessimistic locking manner. This method assumes that other users will access the data when you edit the record. Once you start editing the record, other users will not be able to access the data.
Const adLockOptimistic = 3
Open the data object in an optimistic locking manner. This method assumes that no other users will access the data when you edit the record. The record cannot be accessed by other users until the change is completed.
Const adLockBatchOptimistic = 4
Use this type when performing multi-line batch updates
Options parameters:
The Options parameter indicates the type of command string used to open the record set. Information about the string contents that tell ADO to be executed helps to execute the command string efficiently.
adCMDTable. The executed string contains the name of a table.
adCMDText. The executed string contains a command text.
adCMDStoredProc. The executed string contains a stored procedure name.
adCMDUnknown. The contents of the string are not specified. (This is the default.)
To be simple:
SQL,CONN,A,B
A: ADOPENFORWARDONLY(=0) is read-only, and the current data record can only be moved downward
ADOPENSTATIC(=3) read-only, the current data record can be moved freely
ADOPENKEYSET(=1) can be read and write, the current data record can be moved freely
ADOPENDYNAMIC(=2) can be read and written, the current data record can be moved freely, and new records can be seen
B: ADLOCKREADONLY(=1) default value, used to open read-only records
ADLOCKPESSIMISTIC(=2) Pessimistic lock
ADLOCKOPTIMISTIC(=3) Optimistic lock
ADLOCKBATCHOPTIMISTIC(=4) batch optimistic lock