Description of parameters in Rs.Open in ADO

Recordset Rs.Open parameter Description rs.open sql,conn,a,b


A:

adOpenForwardOnly (=0)
Read-only and the current data record can only move Down

adOpenKeyset (=1)
Read-only, the current data record is free to move

adOpenDynamic (=2)
Can read and write, the current data record is free to move

adOpenStatic (=3)
Can read and write, the current data record can move freely, can see the new record

B:

adLockReadOnly (=1)
Default lock type, Recordset is read-only, record cannot be modified

adLockPessimistic (=2)
Pessimistic locking, when a record is modified, the data provider attempts to lock the record to ensure that it is successfully edited. Lock the record immediately as soon as the editor starts.

adLockOptimistic (=3)
Optimistic locking is not locked until the update record is submitted with the Update method.

adLockBatchOptimistic (=4)
Bulk optimistic locking allows multiple records to be modified, and records are locked only after the UpdateBatch method is invoked.

When you do not need to change any records, you should use a read-only recordset so that the provider does not have to do any testing.
For general use, optimistic locking may be the best option because records are locked for a short period of time,
The data is updated during this time period. This reduces the use of resources.

The DISTINCT parameter is used to select all the different values in a column. For example: There are 3,000 data in the city records, but there are only 5 urban areas,

Extract the name of the city from: SELECT DISTINCT town from Table.

For similar urban areas, where the street is associated with data from the database, you can use the following statement:

Dim CNN as ADODB. Connection
Dim rs as ADODB. Recordset
Dim strSQL as String

Private Sub datacombo1_change ()
Set rs = New ADODB. The Recordset
strSQL = "Select DISTINCT Street from Shuju WHERE town = '" & 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 Metropolitan 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. The Recordset
strSQL = "Select DISTINCT Urban from Shuju"
Rs. Open strSQL, CNN, adOpenKeyset, adLockReadOnly
    Set datacombo1.datasource = rs
     Set Datacombo1.rowsource = rs
    Datacombo1.listfield = Rs. Fields ("urban area"). Name
End Sub