The ConnectionString property on a Connection object contains the information used to establish a connection to a data source. This property sets or returns a string value.
oldString = connection.ConnectionString
connection.ConnectionString = newString
The ConnectionString property is used to specify a data source by passing a detailed connection string containing a series of argument = value statements separated by semicolons.
ADO supports several standard arguments for the ConnectionString property. Any other arguments are passed directly to the provider without any processing by ADO. 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. |
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.
After the ConnectionString property is set and the Connection object is opened, the provider may alter the contents of the property, for example, by mapping the ADO-defined argument names to their provider equivalents. Using the OLE DB Provider for AS/400 and VSAM, three items are stripped from the ConnectionString after a Connection object is opened: Data Source, User ID, and Password.
The ConnectionString property automatically inherits the value used for the ConnectionString argument of the Open method on a Connection object, so you can override the current ConnectionString property during the Open method call. 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.
The ConnectionString property is read/write when the connection is closed and read-only when it is open.
If user and password information is set in both the ConnectionString property and in the optional UserID and Password parameters to the Open method, the results may be unpredictable. Such information should only be passed in either the ConnectionString property (or the ConnectionString parameter to the Open method call) or in 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 property is as follows:
connection = CreateObject("ADODB.Connection.1.5")
connection.ConnectionString = "Provider=SNAOLEDB;Data Source=REMLU;User ID=USERNAME;Password=password;Local LU=LOCAL;Remote LU=DATABASE; Mode=QPCSUPP;CCSID=37;CodePage=437"
Note the lack of spaces after the semicolons in the string. If spaces are inserted after the semicolons, an error will occur.
The simplest form of a ConnectionString property that contains all necessary information is as follows:
connection = CreateObject("ADODB.Connection.1.5")
connection.ConnectionString = "Provider=SNAOLEDB;Data Source=REMLU;User ID=USERNAME;Password=password"
Note The Data Source, User ID and Password must be included. Note the lack of spaces after the semicolons in the string. If spaces are inserted after the semicolons, an error will occur.
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.