K & K Consulting    K & K Consulting

K&K Home VB Guru Home Search VB Site VB Code VB Tips VB Tutorials VB Questions

K&K Home
VB Home


UDL File Tutorial


Does your company change user names and passwords to your database often? Can your application access multiple databases, each one using different settings?  Are you sick to death recompiling your application because you are hard coding data access settings? 


Well, a UDL file just might be your savior.


If you are developing a COM+ application, the spiffy new constructor string may help you in a few instances, but, if you are accessing multiple databases, using different settings, even different drivers, then that option is out. A UDL (Universal Data Link), on the other hand allows you to create Data Link files on the fly, with out having to re-compile your application every time you add new databases, settings or passwords.  I particularly like the way each UDL file can use different drivers, for example, Shape and standard SQL.


Now, lets look at connecting to a database more closely.


Before you can access data from OLE DB, you must provide specific connection Information, such as the following:

bullet The type of data that you want to access.
bullet The server on which the data resides.
bullet The database in which the data is stored.


So, to access a SQL database, you would have to provide the following:

bullet The OLE DB provider for SQL Server.
bullet A server name.
bullet A database name.
bullet User Name
bullet Password


OK, raise your hand if your code looks like one of these:


bullet No DSN

Dim MyConnection


Set MyConnection = New ADODB.Connection


With MyConnection

.ConnectionString = "driver={SQL Server}; server=srv;uid=sa;pwd=pwd;database=Pubs"

.ConnectionTimeout = 30


            End With


bullet With DSN Connection

Dim MyConnection


Set MyConnection = New ADODB.Connection


With MyConnection

.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"


            End With


   As I have mentioned earlier, if you change anything to do with the connection parameters, you must manually change the setting, re-compile, then re-deploy, not very efficient. If you assess multiple databases, your problems are multiplied.  Would you not like your code to look like this instead?


Dim MyConnection


Set MyConnection = New ADODB.Connection


With MyConnection

.ConnectionString = "File Name=" & <Path> & "\" & UDLName & ".UDL;"

                                    .CursorLocation = adUseClient

                                    .ConnectionTimeout = 0

                                    .CommandTimeout = 0


                         End With


I just pass the UDL name to the function and I am all set. I know you could just create a text file with all of your settings, but that would require extra steps to load in the text file, plus, the UDL interface guarantees your setting will be correct. Your UDL file can reside on the client, meaning you must add the UDL file to the set up package, or, as I like to do, place it on a server on the network/VPN, so you only have to change it once.  This will also add a granularity level of security to the file.


Lets us now look at how you can create a UDL file:


bullet Windows 98/ME or Win NT.

1.      Right-clicking on the Desktop.

2.      Select New

3.      Select Microsoft Data Link to create a .udl file.

·        Windows 2000

1.      Open Windows Explorer or My Computer.

2.      Select the folder in which you want to save the .UDL file.

3.      On the File menu, click NEW and then click Text Document.

4.      Right-click on the text file you created in step 3, then click Rename. Type the new file name using a .udl file extension. Press Enter.  For example. Myudl.UDL

5.      You may get a warning, message box, explaining that changing file extensions may cause files to become unusable. This is fine,  click OK.


Once you have created the UDL file, you must now configure it:


  1. Double click on the UDL file to bring up the Data Link Properties Dialog Box.
  2. Click on the Providers tab and select the driver you wish to use.
  3. Click on the Connection tab and enter the connection properties, each driver will require different settings, so I will not go into much details. All drivers will require a user name and password.
  4. Click on the “Test Connection” button to verify your connection is working correctly. Change accordingly if you get an error.
  5. Select the advanced tab only if you require advanced settings.


You are all set, just paste in the following code to a sample application, change the  <Path To UDL File>  to the path you plan to store the UDL in, and take it for a spin.


Private Function CreateConnection(ByVal UDLFileName As String) As ADODB.Connection

Dim Con             As ADODB.Connection


            Set Con = CreateObject("ADODB.Connection")


            With Con      ‘BTW, this code is for creating a disconnected recordset.

            .ConnectionString = "File Name=" & <Path To UDL File> & "\" & UDLFileName & ".UDL;"

            .CursorLocation = adUseClient

            .ConnectionTimeout = 120

            .CommandTimeout = 120


            End With


    Set CreateConnection = Con

    Set Con = Nothing

Exit Function






Copy Write, K&K Consulting's VB Guru, April 2001




Send mail to WebMaster with questions or comments about this web site.
This website is best viewed with a screen resolution of 800*600 or better.
This website is optimized for Microsoft Internet Explorer 6.x
K&K Consulting, Proud to be a Microsoft Business Partner.
Last modified: January 31, 2002