Description
| Opens an ADODB recordset using the specified VIEW, TABLE or SELECT query.
This does not require an ADODB.Connection or even an initialization of the recordset object first. If Records refers to an opened recordset, it will first be closed and the object reinitialized.
Parameter
| Description
| ViewOrTableOrQuery
| A reference to a View, Table or Select query. *)
| Records
| The ADODB record set with the result.
| Cursortype
| Optional. The type of connection to the record set, see below: CursorTypeEnum.
| Locktype
| Optional. The type of locking mechanism applicable to this record set, see below: LockTypeEnum.
| Location
| Optional. The method of server access for this record set, see below: CursorLocationEnum.
| CacheSize
| Optional. The record buffer of the opened record set, set to 50 by default.
| TimeOut
| Optional. Specifies the maximum wait time in seconds for the query. If the result does not appear within the specified time, the query is aborted and an error message is generated. Default value: 120 seconds.
| MaxRecords
| Optional. Specifies the maximum number of records that may be included in the result. Default value: -1 (unlimited number of records)
|
*) In addition to opening SQL tables, queries and views, it is also possible to open Microsoft Excel files (.xls, .xlsx) as tables using this method. Thereby it can be optionally specified which worksheet should be read from the Excel file. By default, "Sheet1" is read out.
The same applies to comma-separated text files (.asc, .csv, .tab, .txt). Here you can optionally specify whether the first line contains the field names. By default, the first line is considered the field name definition.
The pararameter then refers to the file name, including path. To set the worksheet, or field names option, two colons should be specified directly after the file name, followed by the option. Some examples:
"c:\mijn documenten\lijst.xls::Blad2"
"c:\mijn documenten\kvklijst.csv"
"c:\mijn documenten\prijzen.txt::NO"
|
CursorTypeEnum Constants
Constant
| Value
| Description
| adOpenDynamic
| 2
| A dynamic cursor with both forward and backward scrolling where additions, deletions, insertions, and updates made by other users are visible
| adOpenForwardOnly
| 0
| Default, a forward scrolling only, static cursor where changes made by other users are not visible
| adOpenKeyset
| 1
| A keyset cursor allows you to see dynamic changes to a specific group of records but you cannot see new records added by other users
| adOpenStatic
| 3
| A static cursor allowing forward and backward scrolling of a fixed, unchangeable set of records
| adOpenUnspecified
| -1
| Cursor type not specified
|
LockTypeEnum Constants
Constant
| Value
| Description
| adLockBatchOptimistic
| 4
| Multiple users can modify the data and the changes are cached until BatchUpdate is called
| adLockOptimistic
| 3
| Multiple users can modify the data which is not locked until Update is called
| adLockPessimistic
| 2
| The provider locks each record before and after you edit, and prevents other users from modifying the data
| adLockReadOnly
| 1
| Read-only data
| adLockUnspecified
| -1
| Lock type unknown
|
CursorLocationEnum Constants
Constant
| Value
| Description
| adUseClient
| 3
| Uses a client-side cursor provided by the local library
| adUseClientBatch
| 3
| Obsolete
| adUseNone
| 1
| Obsolete
| adUseServer
| 2
| Uses a server-side cursor provided by the local library
|
|
Example
|
Sub Main()
Dim rsRec As ADODB.Recordset
SQL.RunRecordset "Parameters", rsRec
Do While Not rsRec.Eof
Debug.Print rsRec(0), rsRec(1)
rsRec.MoveNext
Loop
Set RsRec = Nothing
End Sub
' Excel bestand uitlezen
Sub Main()
Dim rsRec As ADODB.Recordset
SQL.RunRecordset "c:\mijn documenten\prijslijst.xls::Blad1", rsRec
Do While Not rsRec.Eof
Debug.Print rsRec(0), rsRec(1)
rsRec.MoveNext
Loop
Set RsRec = Nothing
End Sub
|