The Open method on a Connection object opens a connection to a data source.
connection.Open ConnectionString, UserID, Password
The information needed to establish a connection to a data source can be set in the ConnectionString property or passed as part of the Open method in the ConnectionString 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 ConnectionString property as follows:
| Argument | Description |
|---|---|
| Data Source | This argument 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 | This argument 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 | This argument 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 | This argument 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". |
| Remote Provider | This argument specifies the name of a provider to use when opening a client-side connection (for a Remote Data Service only). This argument is not supported by the OLE DB Provider for AS/400 and VSAM. |
| Remote Server | This argument specifies the path name of a server to use when opening a client-side connection (for a Remote Data Service only). This argument is not supported by the OLE DB Provider for AS/400 and VSAM. |
| User ID | This argument 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. |
The Open method on a Connection object is used to open tables on a remote DDM server. Using the Open method on a Connection object establishes the physical connection to a data source. After this method successfully completes, the connection is live and other methods can be invoked on the Connection object to process results.
The optional ConnectionString parameter is used to specify a connection string containing a series of argument=value statements separated by semicolons. The ConnectionString property on a Connection object automatically inherits the value used for the ConnectionString parameter. Therefore, the ConnectionString property of the Connection object can be set before opening the Connection object, or the ConnectionString parameter can be used to set or override the current connection parameters during the Open method call.
If user and password information is set in both the ConnectionString parameter and in the optional UserID and Password parameters, the results may be unpredictable. Such information should only be passed in either the ConnectionString parameter or the UserID and Password parameters.
There are a number of different ways to open a connection. The Open method can pass all of the appropriate connection information as part of the ConnectionString parameter or by setting the ConnectionString property of the Connection object, if this information is known in advance. The syntax in this case using the ConnectionString parameter is as follows:
connection = CreateObject("ADODB.Connection.1.5")
connection.Open "Provider=SNAOLEDB;Data Source=REMLU;User ID=USERNAME;Password=password;Local LU=LOCAL;Remote LU=DATABASE; Mode=QPCSUPP;CCSID=37;CodePage=437"
Note that 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.
The simplest form of an Open command that contains all necessary information is as follows:
connection = CreateObject("ADODB.Connection.1.5")
connection.Open "Provider=SNAOLEDB;Data Source=REMLU;User ID=USERNAME;Password=password"
Note The Data Source, User ID and Password must be included.
In the case where you would like the user to input the connection information, the following syntax can be used. This syntax does not specify any connection information except the provider, which is always required unless this is set in the ConnectionString or Provider property of the Connection object:
connection = CreateObject("ADODB.Connection.1.5")
connection.ConnectionString = "Provider=SNAOLEDB"
connection.Open
This method of invoking the Open method automatically causes a dialog box to appear asking the user for the data source, user name, and password.
When operations have been concluded over an open Connection object, the Close method should be invoked on the Connection object to free any associated system resources. Closing a Connection 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 Connection object variable to Nothing.
If errors occur, these can be examined with the Errors collection on the Connection object.