The Open method on a Recordset object opens a cursor that represents records from a base table or the results of a query.
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
The information needed to establish a connection to a data source can be set in the ActiveConnection property of a Recordset object or passed as part of the Open method on a Recordset object in the ActiveConnection parameter. In either case, this information must be in a specific format for use with the OLE DB Provider for AS/400 and VSAM. This information can be a data source name (DSN) or a detailed connection string containing a series of argument=value statements separated by semicolons. ADO supports several standard ADO-defined arguments for the ActiveConnection property as follows:
| Argument | Description |
|---|---|
| Data Source | Specifies the name of the data source for the connection. This argument is the Data Source name stored in the registry under the OLE DB Provider for AS/400 and VSAM. |
| File Name | Specifies the name of the provider-specific file containing preset connection information. This argument cannot be used if a Provider argument is passed. This argument is not supported by the OLE DB Provider for AS/400 and VSAM. |
| Password | Specifies a valid mainframe or AS/400 password to use when opening the connection. This password is used by Microsoft® SNA Server to validate that the user can log on to the target host system and has appropriate access rights to the file. |
| Provider | Specifies the name of the provider to use for the connection. To use the OLE DB Provider for AS/400 and VSAM the Provider string must be set to "SNAOLEDB". |
| User ID | Specifies a valid mainframe or AS/400 user name to use when opening the connection. This user name is used by SNA Server to validate that the user can log on to the target host system and has appropriate access rights to the file. |
The OLE DB Provider for AS/400 and VSAM also supports a number of provider-specific arguments, some of which default to values in the registry. These arguments are as follows:
| Argument | Description |
|---|---|
| CCSID | The Code Character Set Identifier (CCSID) attribute indicates the character set used on the host. If this argument is omitted, the default value for CCSID is set to the default value in the registry, typically EBCDIC US English (37). |
| CodePage | The character code page to use on the PC. If this argument is omitted, the default value for CodePage is set to the default value in the registry, typically US ASCII (437). This value is only used when configured for custom code page conversion. |
| Local LU | The name of the local LU alias configured in the SNA server. |
| Mode | The APPC mode (must be set to a value that matches the host configuration and SNA Server configuration). If this argument is omitted, the default value for Mode is set to the default value in the registry. Legal values for the APPC mode include QPCSUPP (5250), #NTER (interactive), #NTERSC (interactive), #BATCH (batch), #BATCHSC (batch), and custom modes. |
| RDB | The Remote DataBase name for OS/400. You only need to specify this value if it is different from the remote LU alias configured in the SNA server. |
| Remote LU | The name of the remote LU alias configured in the SNA server. |
This parameter can be one of the following enumerated values for CursorTypeEnum:
| Enumeration | Value | Description |
|---|---|---|
| adOpenUnspecified | -1 | Indicates an unspecified value for the CursorType (not supported by the OLE DB Provider for AS/400 and VSAM). |
| adOpenForwardOnly | 0 | Specifying this value opens a forward-only-type cursor. Under the OLE DB Provider for AS/400 and VSAM this cursortype is mapped to adOpenDynamic. |
| adOpenKeyset | 1 | Specifying this value opens a keyset-type cursor (not supported by the OLE DB Provider for AS/400 and VSAM). |
| adOpenDynamic | 2 | Specifying this value opens a dynamic-type cursor, the only CursorType supported by the OLE DB Provider for AS/400 and VSAM. Additions, changes, and deletions by other users are visible, and all types of movement through the recordset are allowed, except for bookmarks if the provider does not support them. |
| adOpenStatic | 3 | Specifying this value opens a static-type cursor (not supported by the OLE DB Provider for AS/400 and VSAM). |
This optional argument defaults to adOpenForwardOnly, a value that is mapped to adOpendDynamic by the OLE DB provider for AS/400 and VSAM.
This parameter can be one of the following enumerated values for LockTypeEnum:
| Enumeration | Value | Description |
|---|---|---|
| adLockUnspecified | -1 | Indicates an unspecified value for the LockType (not supported by the OLE DB Provider for AS/400 and VSAM). |
| adLockReadOnly | 1 | Specifying this value opens a Recordset object read-only and data cannot be altered. |
| adLockPessimistic | 2 | Specifying this value opens a recordset with pessimistic locking. Record-by-record, the OLE DB Provider does whatever is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing. |
| adLockOptimistic | 3 | Specifying this value opens a recordset with optimistic locking. Record-by-record, the OLE DB Provider locks records only when the Update method is invoked on a Recordset object. |
| adLockBatchOptimistic | 4 | Specifying this value opens a Recordset with batch optimistic locking. This value is required for batch update mode as opposed to immediate update. |
This optional argument defaults to adLockReadOnly.
| Enumeration | Value | Description |
|---|---|---|
| adCmdUnspecified | -1 | This value indicates that the CommandText property is unspecified (not supported by the OLE DB Provider for AS/400 and VSAM). |
| adCmdText | 1 | This value evaluates the CommandText property as a text string. |
| adCmdTable | 2 | This value evaluates the CommandText property as a table name (not supported by the OLE DB Provider for AS/400 and VSAM). |
| adCmdStoredProc | 4 | This value evaluates the CommandText property as a stored procedure (not supported by the OLE DB Provider for AS/400 and VSAM). |
| adCmdUnknown | 8 | This value indicates that the type of command in CommandText is not known and is not supported by the OLE DB Provider for AS/400 and VSAM. |
The Open method on a Recordset object is used to open tables on a remote DDM server. Using the Open method on a Recordset object establishes the physical connection to a data source and opens a cursor that represents records from a base table or the results of a query. After this method successfully completes, the Recordset object is live and other methods can be invoked on the Recordset object to process results.
The optional Source parameter is used to specify the command text required to open a data file on the host. The syntax in this case is as follows:
recordset = CreateObject("ADODB.Recordset.1.5")
recordset.Open "EXEC OPEN LIBRARY/FILE", connection, adOpenDynamic, adLockOptimistic, adCmdText
The optional ActiveConnection parameter corresponds to the ActiveConnection property on a Recordset object and specifies on which connection to open the Recordset object. If a connection string definition is passed for this argument, ADO opens a new connection using the specified parameters. The value of this ActiveConnection property can be changed after opening the Recordset to send updates to another provider. The ActiveConnection property is set to Nothing (in Microsoft® Visual Basic®) to disconnect the recordset from the OLE DB Provider. If the optional ActiveConnection parameter is used to specify a connection string, this string must containing a series of argument=value statements separated by semicolons.
The ActiveConnection property on a Recordset object automatically inherits the value used for the ActiveConnection parameter. Therefore, the ActiveConnection property of the Recordset object can be set before opening the Recordset object, or the ActiveConnection parameter can be used to set or override the current connection parameters during the Open method call.
The CursorType parameter cannot be omitted using the OLE DB Provider for AS/400 and VSAM since this parameter defaults to adOpenForwardOnly, a CursorType that is not supported on the OLE DB Provider. The CursorType parameter must be set to adOpenDynamic, otherwise an error will occur and results will be unpredictable.
There are a number of different ways to open a recordset and connect to a data source. The Open method of the Recordset object can pass all of the appropriate connection information as part of the ActiveConnection parameter or by setting the ActiveConnection property of the Recordset object, if this information is known in advance. The syntax in this case using the ActiveConnection parameter is as follows:
recordset = CreateObject("ADODB.Recordset.1.5")
recordset.Open "EXEC OPEN LIBRARY/FILE","Provider=SNAOLEDB;Data Source=REMLU;User ID=USERNAME;Password=password;Local LU=LOCAL;Remote LU=DATABASE; Mode=QPCSUPP;CCSID=37;CodePage=437", adOpenDynamic, adLockOptimistic, adCmdText
Note Not all of these parameters are required. The registry settings for the Data Source usually have default values set for remote LU, local LU, Mode, CCSID, and CodePage. If a data source is specified, this other information is not usually needed. These registry settings are configured by using the Microsoft Management Console snap-in for the OLE DB Provider for AS/400 and VSAM.
For the other parameters that correspond directly to the properties of a Recordset object (Source, CursorType, and LockType), the relationship of the parameters to the properties is as follows:
Note For Recordset objects whose Source property is set to a valid Command object, the ActiveConnection property is read-only, even if the Recordset object is not open.
If a Command object is passed in the Source parameter and an ActiveConnection parameter is also passed, an error occurs. The ActiveConnection property of the Command object must already be set to a valid Connection object or connection string.
If a Command object is not passed in the Source argument, the Options argument must be set to adCmdText. If the Options argument is not defined, you may experience diminished performance because ADO must make calls to the OLE DB Provider to determine if the argument is a command statement. If you know what Source type you are using, setting the Options argument instructs ADO to jump directly to the relevant code.
If the data source returns no records, the provider sets both the BOF and EOF properties on the Recordset object to True, and the current record position is undefined. You can still add new data to this empty Recordset object if the cursor type allows it.
When operations have been concluded over an open Recordset object, the Close method should be invoked on the Recordset object to free any associated system resources. Closing a Recordset object does not remove it from memory; you may change its property settings and use the Open method to open it again later. To completely eliminate an object from memory, set the Recordset object variable to Nothing.
If errors occur, these can be examined with the Errors collection on the Recordset object.