You use the Microsoft
Data Link API in order to display the
Data Link Properties dialog box, which prompts the 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 specify
the provider and allow the user to enter connection information only.
-
Method 2: If the data source does
require a password, then you write code that specifies the provider.
The user enters connection information only 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
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 the connection
information only. 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.