Create a Recordset
Printer Friendly Page
A
Recordset is a temporary table of data in memory.
A
Recordset is created from a
Data Store
through a
Data Provider.
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "tblSalesGoals", cn, adOpenDynamic, adLockOptimistic
SYNTAX of the
Open method.
recordset.Open Source, Connection, CursorType, LockType, Options
| Source |
The source of the recordset's data. Usually a table name or a SQL statement.
|
| Connection |
The connection object defined earlier in the code.
|
| CursorType |
"Access methods:
adOpenDynamic
- Can find and update data.
- Can move forward and backward through the recordset.
- Can see changes made by other users.
adOpenStatic
- Provides a static copy of the recordset.
- Can move forward and backward through the recordset.
- Can NOT see changes made by other users.
adOpenForwardOnly
- Like the static cursor, except only forward scrolling allowed.
adOpenKeyset
- Like a dynamic cursor, except other users changes are NOT visible.
|
| LockType |
adLockReadOnly
Read-only access
adLockPessimistic
Provider is responsible for successful editing.
adLockOptimist
Provider locks records when Update method is invoked.
adLockBatchOptimistic
For batch updates.
|
| Options |
adCmdTable
The Data Source is a table
adCmdText
The Data Source is a text file.
adCmdStoredProcedure
The Data Source is a stored procedure.
|
Print the field names (from first row)
For Each fld In rs.Fields
Debug.Print fld.Name
Next
Reference an individual field
txtAddress.text = rs!Address
txtCity.text = rs!City
Get the rows all at once
vrecs = rs.GetRows(6)
For r = 0 To Ubound(vrecs, 1)
For f = 0 To Ubound(vrecs, 2)
Debug.Print vrecs(f, r)
Next
Next
rs.Close