In this recipe, you learn several ways to open an ADO Connection object. You will also find lists of the required and optional connection properties for each of the SAS Data Providers.
| Applies to: | All SAS Data Providers |
| Implement using: | ADO |
To open a Connection object, you must specify which provider to use and the appropriate connection properties.
After you have this information, you can use one of these methods to send the information to your selected SAS Data Provider.
To specify the provider in your application, you enter its ProgID, which is a unique name that identifies a COM component. All providers support both version-dependent and version-independent ProgIDs, which enables you to install different versions of the providers on the same system and to write applications that use specific versions.
| To create an application that... | Use this form... | Examples |
|---|---|---|
| always uses the latest version of the provider that is installed on a machine | "SAS.provider" |
"sas.LocalProvider" "sas.ShareProvider" "sas.IOMProvider" "sas.OLAPProvider" |
| only uses a specific version of a provider | "sas.provider.major version.minor version" | "sas.LocalProvider.9.0" |
To maintain compatibility with previous releases, the special ProgID "sas.provider.1" is maintained as a synonym for the version-independent ProgID. For example, the last SAS IOM Data Provider that was installed on the system can be identified in either of these two ways:
The SAS 9.1 IOM Data Provider can be specifically identified as "sas.IOMProvider.9.1".
Each provider supports its own unique set of Connection object properties. To review the syntax used by each provider, see the following sections:
The SAS IOM Data Provider supports the following properties on the ADO Connection object:
| Property | Value | Required? |
|---|---|---|
| Data Source | Use the keyword "_LOCAL_" to indicate a local instance of the IOM server. To specify a remote server, enter a user-defined name that will be used to refer to the data source. | Yes |
| User ID | User ID that is used to authenticate against the server. | No |
| Password | Password to use with the user ID in authenticating against the server. | No |
| SAS Workspace ID | A unique ID generated by the SAS Workspace Manager. | No |
| SAS Logical Name | An entry name in your LDAP (Lightweight Directory Access Protocol) repository that defines an IOM server. | No |
| SAS Port | Port number of remote server. | No |
| SAS Service Name | A logical reference to the port associated with a remote server. | No |
| SAS Machine DNS Name | Network DNS name of the remote server. | No |
| SAS Protocol | IOM protocol to use when connecting to the remote server. | No |
| SAS Server Type | Tabular data server or OLAP server. | No |
The simplest connection that you can make with the IOM provider is to a server that is running on your local Windows machine. Such a server is bound exclusively to the Connection object that instantiated it. For this type of connection, you only need to specify a "Data Source" property value of "_LOCAL_" , as shown in the following code:
obConnection.Provider = "sas.IOMProvider"
obConnection.Properties("Data Source") = "_LOCAL_"
obConnection.Open
The SAS OLAP Data Provider supports the following properties on the ADO Connection object:
| Property | Value | Required? |
|---|---|---|
| Data Source | The name of the OLAP server to which you are connecting. | Yes |
| User ID | User ID that is used to authenticate against the server. | No |
| Password | Password to use with the user ID in authenticating against the server. | No |
| SAS Port | Port number of remote server. | No |
| SAS Service Name | A logical reference to the port associated with a remote server. | No |
| SAS Protocol | IOM protocol to use when connecting to the remote server. | No |
The local provider supports the following properties on the ADO Connection object:
| Property | Value | Required? |
|---|---|---|
| Data Source | A physical directory that contains the SAS data set that you want to access with the connection. | Yes |
| Mode | adModeRead or adModeReadWrite | No |
| SAS File Format | The SAS file format access method to associate with the
connection. Valid values: "V6", "V7", "V8", and "V9". |
No |
The local provider is usually the simplest to configure with an ADO connection. You only need to specify the physical location of the SAS files that you want to access. For example, if your data sets can be found in c:\my documents\sas files, then the following Visual Basic code will open the connection that you need:
obConnection.Provider = "sas.LocalProvider"
obConnection.Properties("Data Source") = "c:\my documents\sas files"
obConnection.Open
The SAS/SHARE provider supports the following properties on the ADO Connection object:
| Property | Value | Required? |
|---|---|---|
| Data Source | The server ID (established by the server administrator when the SAS/SHARE server is started). | Yes |
| Mode | adModeRead or adModeReadWrite | No |
| Location | The node that the server is running on. | Only if the node is not the one running the ADO application. |
| User ID | The user ID that is used to access a remote server. | No |
| Password | The password that is used to access a remote server. | No |
| SAS Server Access Password | The server access password (if one was established by the server administrator when the SAS/SHARE server was started). | No |
| SAS Server Release | The server access method to associate with the connection.
Valid values: "7", "8", and "9" |
No |
The simplest connection that you can make using the SAS/SHARE provider is to a SAS/SHARE server that is running on your local Windows machine. After the Connection object is opened, it is assigned to this server for the duration of the session. For this type of a connection, you only need to specify the server's ID as the "Data Source."
obConnection.Provider = "sas.SHAREProvider"
obConnection.Properties("Data Source") = server_id
obConnection.Open
obConnection.Open "Provider=provider_name;Data Source=data_source;other_provider_specific_info"
obConnection.ConnectionString = "Provider=provider_name;Data Source=data_source;other_provider_specific_info" obConnection.Open
obConnection.Provider = "provider_name"
obConnection.Properties("Data Source") = "data_source"
' Set additional properties via the Properties collection as needed
obConnection.Open