Create a Recordset

by Linda Quinn

Close Window

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






=================================================================
This content was created by Linda Quinn of LQNet.

See http://www.lqnet.com for a great collection of articles on this and other topics.

=================================================================
Copyright © 2006-2008, LQ Systems,Inc. All rights reserved.


====================================================================
Want an expert to help with your project?    LQ Systems, Inc.   Business Solutions
====================================================================