Using the Data Link API

Use this little-known utility to enhance OLE DB management

OLE DB is rapidly displacing ODBC as the standard database development API, but ODBC still offers several advantages, one of which is ease of management. You can easily view installed ODBC drivers and configure new data sources by using the ODBC Administrator, which you can find in the Control Panel for Windows NT and Windows 9x or in the Administrative Tools folder in Windows 2000. OLE DB has no interface that is equivalent to the ODBC Administrator, which is both good and bad news for developers.

On the upside, OLE DB providers don't rely on external configuration files, as ODBC drivers do. Instead, OLE DB uses properties to provide all required connection parameters dynamically at runtime. For instance, when the program first makes a connection using an OLE provider, the user is prompted to fill out the required connection information. On the downside, you can't easily see which OLE DB providers are available on a system. But you can use the little-known Data Link API to build an interface that is equivalent to the ODBC Administrator. In this article, I present the DataLink utility, which uses the Data Link API to display OLE DB providers installed on your system. The DataLink utility also lets you create and save .udl configuration files.

Using the DataLink Utility

When the DataLink utility starts, it displays the primary Data Link Properties dialog box, as Figure 1, page 44, shows. The Provider tab displays all OLE DB providers installed on the system. (You'll also find this dialog box in the Visual Basic—VB—Data Environment, Access Database Project—ADP—projects, and Visual InterDev projects.) To configure a connection by using an installed OLE DB provider, select a provider from the list and click Next to display the Connection tab, as Figure 2 shows.

The Connection tab lets you configure the connection attributes that the OLE DB provider uses. Depending on which OLE DB provider you select, this screen will look different and will contain different prompts. Figure 2 shows the connection attributes that the SQL Server OLE DB provider uses. In Prompt 1, select or type the name of the SQL Server system you want to connect to, which in my case was tecamobile2. In Prompt 2, select the authentication method used to connect to the SQL Server system and type the username and password. (In my example, it's the systems administrator—sa—user ID.) Select a default database in Prompt 3 (I chose the Pubs sample database). After entering all the required connection information, you can click Test Connection to verify that you can connect to the SQL Server system that you selected.

When entering the OLE DB connection attributes, click OK to display the standard Windows Save File dialog box, which lets you save the OLE DB connection attributes in a .udl file. Listing 1 presents the .udl file that I saved with the configuration attributes that Figure 2 shows.

Adding a Reference to the OLE DB Service Object

Before you can display the Data Link Properties dialog box from your VB application, you need to add references to both the Microsoft OLE DB Service Component 1.0 Type Library and the Microsoft ActiveX Data Objects 2.5 Library. To add an OLE DB Service Component 1.0 Type Library reference to VB's Integrated Development Environment (IDE), select the References option from VB's Project menu. The References dialog box appears, as Figure 3 shows.

Scroll through the list of references, select the box next to Microsoft OLE DB Service Component 1.0 Type Library, then click OK. Repeat this process for the Microsoft ActiveX Data Objects 2.5 Library. Unlike adding ActiveX Controls, adding a Reference to VB's IDE doesn't create any visual objects in VB's toolbox. To see the objects, properties, and methods that the reference provides, you need to use VB's Object Browser. Figure 4 shows the VB Object Browser, which you use to display the DataLinks object that the OLE DB Service Component 1.0 Type Library contains.

Displaying the Data Link Properties Dialog Box

After you add an OLE DB Service Component 1.0 Type Library reference, you can create an instance of the DataLink object in your application. Listing 2, page 46, presents the Sub Main subroutine, which contains the code to display the Data Link Properties dialog box and save the settings that you define in that dialog box to a new .udl file.

The highlighted code at Callout A in Listing 2 creates a new instance of the DataLinks object. The first Dim statement creates a new instance of the DataLinks object (named oDataLink), and the second Dim statement creates an ADO connection object (named oConn). The ADO connection object contains the instance of the connection object that the DataLinks object returns.

After you instantiate a new instance of the DataLinks object, you can use the DataLinks object's PromptNew method, which Callout B shows, to display the Data Link Properties dialog box that Figure 1 shows. When the PromptNew method executes, the Data Link Properties dialog box displays the Provider tab, which lists all OLE DB providers installed on the system. Note that the Data Link Properties dialog box lets you configure and connect to a target data source. To enable a connection to the target database, the Data Link Properties dialog box contains an ADO connection object, and the PromptNew method returns that connection object. Callout B shows that the connection object that the oDataLink object returns is assigned to the ADO connection object named oConn that the routine declared earlier in Callout A.

Next, the routine checks the oConn connection object; if the oConn object equals Nothing, then the user chose not to save any configuration values and clicked Cancel in the Data Link Properties dialog box. If the oConn connection object contains a valid instance of a connection object returned by the call to the oDataLink object's PromptNew method, then a dialog box will prompt the user to save the connection properties in a .udl file.

Saving the .udl File

The code at Callout C creates an instance of the CommonDialog control. Because the DataLink program runs from Sub Main and doesn't display forms of its own, the program uses the CreateObject function to create at runtime an instance of the CommonDialog control named oDialog. After the program creates a CommonDialog object, the object's Filter property will display only files with .udl extensions in the dialog box. Next, the ShowSave method displays the Save File dialog box to the user.

If you view the contents of a .udl file with a text editor such as Notepad, you might think that .udl files are simple text files, but they're binary files consisting of Unicode characters that must follow a predefined format. You can see the code that writes the .udl file to disk at Callout D. If the user enters a file name in the DataLink program's Save File dialog box, the file will be written to disk as a binary file. The For Binary keywords on the Open function specify that a new binary file is created when the Open function executes. Next, the Put function writes the hexadecimal characters FF FE to the first two bytes of the new .udl file. Then, the string [oledb] followed by the carriage return (Cr) and line feed (Lf) is copied into a Byte array, written as Unicode into the first line of the .udl file. (Because each Unicode character requires two bytes of storage, the Redim statement expands the array to twice the actual string value before the string is assigned to the Byte array.) The second line of the .udl is then set to the string "; Everything after this line is an OLE DB initstring" followed by the Cr and Lf characters. Note that the first two lines of the .udl file must follow this format exactly, or the file won't be recognized as valid. The last line of the .udl file consists of the ConnectionString property (contained in the oConn connection object), followed by the Cr and Lf values. This connection string contains the values that you originally specified by using the Data Link Properties dialog box's Connection tab. After all three lines are written to the .udl file, the Close statement closes the file, then each used object is set to nothing, which releases the system resources that the objects consumed.

After you've created the .udl file, you can use it to provide all required connection properties to your ADO applications at runtime. (For more information about opening an ADO connection by using a preexisting .udl file, see the sidebar "Using a .udl File from ADO." For information about adding a new data link option to Win2K, see the sidebar "Adding a New Data Link File Type to Windows 2000.")

The datalink.exe program can help you display the Data Link Properties dialog box from within your own application and create your own .udl files. (You can download the datalink.exe program, along with the project and source code for this article, by entering InstantDoc ID 9640 at http:// www.sqlmag.com/ and opening the 9640.zip file.) You can also use this program as a standalone utility to help view and manage your OLE DB providers.