Making Connections by Using the Microsoft Data Link API

In this recipe, you learn how to use the Microsoft Data Link API to prompt users for OLE DB connection information at run time, and to provide persistent connection information for the SAS Data Providers.

Applies to:	All SAS Data Providers
Implement using:	ADO

Overview

You can use the Microsoft Data Link API to prompt the user for OLE DB connection information at run time. You also can use the API to provide persistent connection information for the SAS Data Providers, similar to what is provided for ODBC by the ODBC Administrator

Prompting a user for connection information at run time is a relatively straight forward process. It requires that you display the Data Link Properties dialog box, which is provided by the Data Link API. The Data Link Properties dialog box hosts a set of generic property pages, as well as an optional provider-specific property page. Property pages are used to collect connection information from the user and return a Connection object. For details and sample code, see Prompting for Connection Information at Run Time.

Using the Data Link API to provide persistent connection information to the SAS Data Providers is more complex and involves storing the Connection object's ConnectionString property in a special text file. For more information, see Providing Persistent Connection Information.

Prompting for Connection Information at Run Time

There are two ways that you can prompt a user for connection information at run time, depending on whether or not access to your data source requires a password.

Method 1: Password Not Required

If a password is not required, you must add following two library references to your Visual Basic project (see Minimum System Requirements for the SAS Data Providers for supported versions):

Microsoft OLE DB Service Component Type Library
Microsoft ActiveX Data Objects Library

Then, to display the Data Link Properties dialog box, you can use code similar to the following:


Sub Main()
    Dim dl As MSDASC.DataLinks
    Dim cn As ADODB.Connection
    Dim rs as ADODB.Recordset
	
    Set dl = New MSDASC.DataLinks
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    Set cn = dl.PromptNew
    cn.Open

    rs.Open "sasuser.MyData", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
    ' Operate on rs ...
End Sub

When the Data Link Properties dialog box is displayed, the user is shown a list of OLE DB Providers installed on the system. After the user selects a provider and clicks the Next button, the Connection tab is displayed. The content of the Connection tab depends on which provider the user selected:

If the IOM or OLAP provider is selected, then the Connection tab displays a customized property page.
If then local or SAS/SHARE provider is selected, the Connection tab displays a generic property page.

Note: For more information, see How Users Complete the Connection Tab.

Note: If you use this method to allow users to access a data source that requires a password, the password will be converted to a string of asterisks and the call to Connection.Open will fail with an invalid password error. If your data source requires a password, use Method 2.

Method 2: Password Required or Provider Known

If your data source is secured by a password or if you already know which provider will be used, then you can write code that sends the user directly to the Connection tab. To do this, you use the Connection object's "Prompt" property and a provider name. In addition, you do not need to add a reference to the Microsoft OLE DB Service Component Type Library to your Visual Basic project. The following sample code shows you how to implement this method.

Note: For information about supported library versions, see Minimum System Requirements.

' Add a reference to the Microsoft ActiveX Data Objects Library
Sub Main()
    Dim dl As MSDASC.DataLinks
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
	
    Set dl = New MSDASC.DataLinks
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

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

    rs.Open "sasuser.MyData", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
    ' Operate on rs ...
End Sub

How Users Complete the Connection Tab

The content of the Connection tab depends on which provider is being used.

Note: For more information about using the properties discussed in this section, see Opening an ADO Connection Object.

If the IOM provider is selected, the Connection tab shown below is displayed. The default protocol is set to ProtocolCom; the user enters the rest of the information. If the OLAP provider is selected, the default port number is 5451 and the default protocol is ProtocolBridge.

Note: Help on completing this tab displays when a user clicks the Help button on the Data Link Properties dialog box.

Customized Version of the Connection Tab

The following table shows how the ADO items on the customized property version of the Connection tab map to OLE DB properties:

Custom Connection Tab Item	OLE DB Property
Data Source	DBPROP_INIT_DATASOURCE
User ID	DBPROP_AUTH_USERID
Password	DBPROP_AUTH_PASSWORD
SAS Logical Name	DBPROP_SAS_INIT_LOGICALNAME
SAS Machine DNS Name	DBPROP_SAS_INIT_MACHINEDNSNAME
SAS Service Name/Port	DBPROP_SAS_INIT_SERVICENAME (if a name is entered)
SAS Service Name/Port	DBPROP_SAS_INIT_PORT (if a number is entered)
SAS Protocol	DBPROP_SAS_INIT_PROTOCOL

If the SAS/SHARE or local data provider is selected, the Connection tab shown below is displayed. The user enters standard connection information for the SAS/SHARE or local data provider.

Generic Version of the Connection Tab

The following table shows how the ADO items on the generic property version of the Connection tab map to OLE DB properties.

Generic Connection Tab Item	OLE DB Property
Data Source	DBPROP_INIT_DATASOURCE
Location	DBPROP_INIT_LOCATION
User name	DBPROP_AUTH_USERID
Password	DBPROP_AUTH_PASSWORD

Providing Persistent Connection Information

You can store a Connection object's ConnectionString property in a Microsoft Data Link (.UDL) file, which is special text file that can be used in conjunction with ADO and Visual Basic's DataEnvironment tool to initialize a Connection object.

Note: DataEnvironment is a Visual Basic GUI tool that can be used to manage static connections to known data sources. To add it to your project, select Project Add Data Environment.

To create a .UDL file, first create an empty text file with the extension .UDL. Then, from Windows Explorer, double click on your new file. The Data Link Properties dialog box appears. The Data Link Properties dialog box is the same interface that the user sees when you create an application that uses the MSDASC.DataLinks object (see Prompting for Connection Information at Run Time). After you provide the connection information about the Data Link Properties dialog box, the .UDL file that you created will contain all of the connection information necessary to open an ADO Connection object.

You can store a .UDL file on a server to more easily accommodate a data source that might move to a different server. Your Visual Basic application would then refer to the .UDL file rather than directly to the data source. If the data source moves, then you would update the .UDL file with the new location rather than modify the application. You can refer to the .UDL file in the Visual Basic's DataEnvironment tool or in the ConnectionString property of a Connection object. For example, the following sample code refers to a .UDL file named myfile.udl in a directory named c:\mydir in a connection string: