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:

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

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

Dim MyConnection

Set MyConnection = New ADODB.Connection

With MyConnection

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

.ConnectionTimeout = 30

.Open

            End With

Dim MyConnection

Set MyConnection = New ADODB.Connection

With MyConnection

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

                                    .Open

            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

                                    .Open

                         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:

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

            .Open

            End With

    Set CreateConnection = Con

    Set Con = Nothing

Exit Function

Enjoy

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