The following steps set up a Visual Basic sample that demonstrates how to use the DataLinks object of the OLE DB Service Component 1.0 Type Library to edit the connection string properties of an ADO connection object:
- Open a new Standard EXE project in Visual Basic. Form1 is created by default.
- On the Project menu, set references to the Microsoft ActiveX Data Objects 2.x Library and the OLE DB Service Component 1.0 Type Library.
- Drag-and-drop a CommandButton on Form1 and make the caption Define Connection.
- Copy and paste the following code into Form1's code module:
Dim cn As ADODB.Connection
Private Sub Command1_Click()
Dim MSDASCObj As MSDASC.DataLinks
Set MSDASCObj = New MSDASC.DataLinks
Set cn = New ADODB.Connection
MsgBox "Connection opened successfully"
- Save the project and run it.
- Click Define Connection, and note that the code in the Click event procedure of the CommandButton creates an instance of the MSDASC.DataLinks object and executes its PromptEdit method to display the OLE DB Data Link Properties dialog box. This dialog box is displayed as a modal window. As a result, subsequent code that follows the call to the PromptEdit method is not executed until the Data link properties dialog box is dismissed.
- Select a suitable OLEDB provider in the Providers tab, and then specify the other connection attributes to establish a connection to one of your databases (Access, SQL Server, Oracle, and so forth).
- Click Test Connection in the Data link properties dialog box to test the connection. Note that you receive a Test Connection succeeded message box if the connection is successful. Dismiss the dialog box by clicking OK. The remaining code in the Click event procedure of the CommandButton is now executed. The ConnectionString property of the ADO Connection object that was passed as a parameter to the PromptEdit method is initialized based on the settings that you selected in the Data link properties dialog box.
The statements that follow the call to the PromptEdit method of the MSDASC.DataLinks object open and close an ADO Connection by using the ADO Connection object that is initialized by the call to the PromptEdit method. This verifies that the ADO Connection object's ConnectionString property was set correctly according to the options that are selected in the Data Link properties dialog box. If you click Cancel in the Data Link properties dialog box, the cn.Open statement in the Visual Basic code generates a run-time error to indicate that it could not establish a connection by using the uninitialized ConnectionString.
NOTE: You can check the connection's ConnectionString to see if it is empty ("") and catch the Cancel before the connection.open statement.