Prompting Users for Connection Information by Displaying the Data Link Properties Dialog Box

Goal

You want your application to prompt users for connection information at run time by displaying the Data Link Properties dialog box.

This recipe applies to all the SAS providers that are being used to access SAS 9.2 servers. This recipe applies only to ADO. Sample code is included.

Implementation

Sample Code That Displays the Data Link Properties Dialog Box

You use the Microsoft Data Link API in order to display the Data Link Properties dialog box, which prompts user for connection information. The dialog box has property pages that the user completes in order to select a provider and enter connection information. After the user enters the information, an ADO Connection object is returned.

Here are two ways in which you can present the Data Link Properties dialog box to users:

Method 1: If the data source does not require a password, then you can allow the user to select a provider in addition to entering connection information. You can also preselect the provider and just allow the user to enter connection information.
Method 2: If the data source does require a password, then you write code that specifies the provider. The user just enters connection information in the dialog box.

The following sample code can be used if the data source does not require a password and you want to allow the user to select a provider. This code displays a version of the Data Link Properties dialog box that includes the Provider tab.

Note: To use the sample code, you must reference these type libraries in your Visual Basic project: the Microsoft OLE DB Service Component Type Library and the Microsoft ActiveX Data Objects Library.

CAUTION:

Do not use this code if the data source requires a password.

If you do, the password is converted to a string of asterisks and the call to Connection.Open fails with an invalid password error.

Method 1: A Password Is Not Required and the User Is Prompted to Select a Provider

 Dim dl As New MSDASC.DataLinks
 Dim obConnection As New ADODB.Connection
 Dim obRecordset as New ADODB.Recordset
	
 Set obConnection = dl.PromptNew
 obConnection.Open

 obRecordset.Open "sasuser.MyData", obConnection, adOpenDynamic, adLockOptimistic, adCmdTableDirect
 ' Operate on obRecordset.

Provider Tab with a List of the SAS Providers for OLE DB

Data Link Properties dialog box Provider tab

If your data source is secured by a password or if you want to preselect the provider, then you can write code that hides the Provider tab from the user. In this case, the user enters only connection information. Here is sample code that implements this method. The code uses the Connection object Prompt property and includes a provider name (SAS.IOMProvider).

Note: To use the sample code, you must reference the Microsoft ActiveX Data Objects Library in your Visual Basic project.

Method 2: A Password Is Required or the Provider Is Known

    Dim obConnection As New ADODB.Connection
    Dim obRecordset As New ADODB.Recordset

    obConnection.Provider = "SAS.IOMProvider"
    obConnection.Properties("Prompt") = adPromptAlways
    obConnection.Open

    obRecordset.Open "sasuser.MyData", obConnection, adOpenDynamic, adLockOptimistic, adCmdTableDirect
    ' Operate on obRecordset.

Note: For more information about the "Prompt" property, see DBPROP_INIT_PROMPT.

What You Need to Know about Connecting with the IOM and OLAP Providers

If the IOM or OLAP provider is being used (either because you specified it or the user selected it), the user can enter connection information on either the Connection tab or on the Advanced tab. Both tabs are customized specifically for a connection to a SAS Workspace or SAS OLAP server.

Note: The user must use the Advanced tab if the selected provider is defined in a SAS Metadata Server that has not been configured by using the SAS Integration Technologies configuration utility (ITConfig).

Here is the customized version of the Connection tab.

The Customized Connection Tab As It Appears When the IOM Provider Is Selected

Data Link Properties Custom Connection Tab

SAS provides customized help for completing this tab (see Data Link Properties Dialog Box (Connection Tab)). To access the information, users click the Help button.

The following table explains how the fields on the customized Connection tab correspond to the ADO and OLE DB properties.

How the Fields Correspond to ADO and OLE DB Properties

Field Name	ADO Property Name	OLE DB Property Name
Data Source	"Data Source"	DBPROP_INIT_DATASOURCE
User name	"User ID"	DBPROP_AUTH_USERID
Password	"Password"	DBPROP_AUTH_PASSWORD

Here is the customized version of the Advanced tab.

The Advanced Tab As It Appears When the IOM Provider Is Selected

SAS provides customized help for completing this tab (see Data Link Properties Dialog Box (Advanced Tab)). To access the information, users click the Help button.

The following table explains how the fields on the Advanced tab correspond to the ADO and OLE DB properties.

How the Fields Correspond to ADO and OLE DB Properties

Field Name	ADO Property Name	OLE DB Property Name
SAS Metadata Server Information
Metadata Server Location	"SAS Metadata Location"	DBPROP_SAS_INIT_METALOCATION
User name	"SAS Metadata User ID"	DBPROP_SAS_INIT_METAUSERID
Password	"SAS Metadata Location"	DBPROP_SAS_INIT_METAPASSWORD
SAS Server Information
Data Source	"Data Source"	DBPROP_INIT_DATASOURCE
Location	"Location"	DBPROP_INIT_LOCATION
User name	"User ID"	DBPROP_AUTH_USERID
Password	"Password"	DBPROP_AUTH_PASSWORD

What You Need to Know about Connecting with the Local, SAS/SHARE, or Base SAS Provider

If the local, SAS/SHARE, or Base SAS provider is being used (either because you specified it or the user selected it), the user enters information on the generic version of the Connection tab.

The Generic Connection Tab for Use with the Local, SAS/SHARE, and Base SAS Providers

Data Link Properties Generic Connection Tab

For information about completing the generic Connection tab, users can click the Help button. The standard help content is provided by Microsoft.

The following table explains how the fields on the generic Connection tab correspond to the ADO and OLE DB properties. These fields have specifications that are specific to SAS.

How the Fields Correspond to ADO and OLE DB Properties

Field Name	ADO Property Name	OLE DB Property Name
Data Source Information
Data Source	"Data Source"	DBPROP_INIT_DATASOURCE
Location	"Location"	DBPROP_INIT_LOCATION
SAS Server Information
User name	"User ID"	DBPROP_AUTH_USERID
Password	"Password"	DBPROP_AUTH_PASSWORD