Rs. Open SQL, Conn, adopenstatic, adlockoptimistic

The first parameter is an SQL statement.

Number 2: Conn, that is, database connection

Third parameter:
0. Create a read-only record set that can only be rolled forward.
1. The cursor allows you to add, delete, and modify records, but you cannot see the changes made by other users when your record set is opened.
2. The cursor allows you to add, delete, and modify records, and you can see the modifications made by other users.
3. Create a read-only record set with all positioning functions, that is, it can forward, backward, and can mark bookmarks

Fourth:
1. The record is read-only and cannot be changed.
2. The record is locked when you can edit it.
3. The record is locked when you call the update method to submit your changes
4. If you are batch updating a series of records

 

Description of Rs. Open parameters of record set: Rs. Open SQL, Conn, A, and B

A:

Adopenforwardonly (= 0)
Read-only, and the current data record can only be moved down

Adopenkeyset (= 1)
Read-only, the current data records can be freely moved

Adopendynamic (= 2)
Read/write: The current data records can be freely moved.

Adopenstatic (= 3)
Read/write: The current data record can be freely moved. You can see the new record.

B:

Adlockreadonly (= 1)
Default lock type. The record set is read-only and cannot be modified.

Adlockpessimistic (= 2)
Pessimistic locking: When a record is modified, the data provider attempts to lock the record to ensure that the record is edited successfully. You only need to edit the record at the beginning, and lock the record immediately.

Adlockoptimistic (= 3)
Optimistic Locking: The record is locked only when an update record is submitted using the update method.

Adlockbatchoptimistic (= 4)
Optimistic Locking in batches. Multiple records can be modified. Records are locked only after the updatebatch method is called.

When no record needs to be modified, the read-only record set should be used, so that the provider does not need to perform any detection.
For general use, Optimistic Locking may be the best option, because the record is locked for only a short period of time,
The data is updated during this period. This reduces resource usage.

The distinct parameter is used to select all different values in a column. For example, the table contains 3000 urban records, but there are only 5 urban records,

Extract the name of the city: Select distinct city from table.

You can use the following statement to associate sub-district data from a database in a similar urban area:

Dim CNN as ADODB. Connection
Dim RS as ADODB. recordset
Dim strsql as string

private sub datacombow.change ()
set rs = new ADODB. recordset
strsql = "select distinct street from shuju where city = '" & datacombo1.text & "'"
Rs. open strsql, CNN, adopenkeyset, adlockreadonly
set datacombo2.datasource = RS
set datacombo2.rowsource = RS
datacombo2.listfield = Rs. fields ("street "). name
end sub

private sub form_load () 'Load city to datacombo1
set CNN = new ADODB. connection
CNN. provider = "Microsoft. jet. oledb.4.0 "
CNN. connectionstring = "Data Source =" & App. path & "\ data. MDB; persist Security info = false "
CNN. open
set rs = new ADODB. recordset
strsql = "select distinct city from shuju"
Rs. open strsql, CNN, adopenkeyset, adlockreadonly
set datacombo1.datasource = RS
set datacombo1.rowsource = RS
datacombo1.listfield = Rs. fields ("city "). name
end sub