This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

ACCESS 2000

An Easy Way to Create OLE DB Connection Strings

by Sean Kavanagh

One of the aspects of ADO that can be confusing at first is the use of the Connection object. Building connection strings correctly, with all the proper OLE DB provider settings, can be cumbersome. Fortunately, this process can be simplified greatly. In this article, we'll show you how to use the Data Link API to set up data source connections easily.

Overview

You can think of the Data Link API as a front-end to your connection string. The result is a Microsoft Data Link file, which has a UDL extension. You can then extract the connection string from the UDL and incorporate it directly into your application, or you can simply reference the UDL when opening connections.

Create a UDL file

The way you create a UDL file will depend on your Windows installation. Chances are you'll be able to create the file directly from the shortcut menu. First, right-click on the Windows desktop, or in the folder where you want to create the file. Next, select New from the shortcut menu. If you see a choice for Microsoft Data Link, select it--that's all there is to it. If you're using Windows 2000, you most likely won't find a Microsoft Data Link choice. If you don't see Microsoft Data Link listed in the shortcut menu, you can still create a UDL file, but you'll have to do a little more work.

Creating a UDL when Microsoft Data Link isn't an available choice

For an alternative way of creating a UDL file, right-click on the Windows desktop and choose New/Text Document from the shortcut menu. Next, ensure that Windows is set up to display file extensions. If you don't see the TXT extension included in the new text file's name, you'll need to change your Windows configuration. To do so, open any folder and choose View/Folder Options from the menu bar. Then, click on the View tab, clear the Hide File Extensions For Known File Types check box, and click OK. Next, right-click on the text file you just created, select Rename from the shortcut menu, and change the TXT extension to UDL. Finally, press [Enter] and click Yes when Windows asks if you're sure you want to change the extension.

Building the connection

At this point, double-click on the UDL file you created to launch the Data Link API. The first property sheet of the Data Link Properties dialog box is where you select the type of OLE DB provider you need to connect to the database. Simply choose from the list of available providers, as shown in Figure A, and click Next.

Figure A: The first sheet of the Data Link Properties dialog box displays a list of the available providers.

The Connection property sheet of the Data Link Properties dialog box is context sensitive--it only shows options that are relevant to the provider selected on the previous sheet. For instance, Figure B shows an example of a UDL configured for the Northwind database, using the Jet 4.0 provider.

Figure B: This Connection property sheet shows the settings available when setting up a Jet 4.0 connection.

In contrast, a connection to a SQL Server database requires more information, as shown in Figure C.

Figure C: The options available on the Connection property sheet depend on the provider selected on the first property sheet.

Regardless of the provider you're using, one of the nicest aspects of working with Data Link API is that you can easily verify that you've configured everything correctly. Simply click the Test Connection button, and you'll receive either a positive confirmation or an appropriate error message.

The Advanced property sheet of the Data Link Properties dialog box allows you to specify additional network and access permission settings. As you can see in Figure D, which shows a UDL file configured for the Jet 4.0 provider, only the options relevant to the selected provider will be enabled.

Figure D: The Advanced property sheet lets you specify access permissions and network settings, if the options are relevant to the selected provider.

The final sheet of the Data Link Properties dialog box, shown in Figure E, provides a summary of the initialization properties for the database connection you've set up. You can edit any of the properties directly from this sheet by double-clicking on the property name or selecting the name and clicking the Edit Value button.

Figure E: You can edit any initialization properties from this property sheet.

Working with the finished UDL file

Once you've configured the connection settings, click OK to close the Data Link Properties dialog box. You can either reference this data link file from an application, or you can copy the connection string that it generates directly into your Access application.

Referencing the UDL file

To open a connection (we've named it cnn) to a database using a UDL file, use the syntax:

cnn.ConnectionString = "File Name=path\filename.udl;"
cnn.Open

One drawback to this technique is that you need to ensure that the UDL file is distributed with your application.

Copying the connection string into your code

To get the connection string created by the data link file, rename the UDL file so that it has a TXT extension. Then, open the text file, preferably with WordPad. (For some reason, although Notepad seemed to open the files correctly in our Windows NT environment, opening the UDL with Notepad in Windows 95/98 produced strange results.) Once you've opened the file, you'll find text resembling what was generated by our previous SQL Server example

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=imnottelling; _
	Persist Security Info=True;User ID=zorroadmin; _
	Initial Catalog=Emissions_Data.MDF; _
	Data Source=zorro;Extended Properties="Trusted_
	Connection=yes"; _
	Network Library=DBMSSOCN

You can now simply copy and paste the OLE DB connection string into your application, using the syntax

cnn.Open "connection string"

Conclusion

If you don't already, you'll probably soon find that you have to start using ADO in your Access applications. Learning a new language is a difficult task, and constructing complex OLE DB connection strings doesn't make it any easier. Fortunately, you can use the Data Link API to simplify the process significantly.

Copyright © 2000 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.