Database access using UDL

Published on 08/14/01

Introduction

UDL is a rather old way of accessing a database, but it still works and is another way for the beginner to get access to the database that he is using. The samples are for Classic ASP, but the concept of UDL files is the same for .NET data access.

U..D...What?

UDL is a plain text file with a connection string, just like you've been using (assuming that you use DRIVER={DRIVER];DBQ=[DBQ];PQD=[Password] ), and the great part is that it doesn't take much to set it up.  I'm just going to go over the basics, but there isn't much more to it really.

Let There be UDL!

The easiest (and most uniform) way to create a UDL file is to create a blank text file and rename it to 'something.UDL' (make sure that you change the extension to UDL; otherwise it becomes 'something.udl.txt'). After a successful renaming, you should see an icon like the one below.

Now, all you have to do, is double-click on it, and a screen like this should appear:

Lets run down on what this is. The instructions follow from number 1.

1. Depending on how you access your database, you can either use a data source name (DSN) or a connection string (non-DSN), for the DSN just enter in the DSN name (for how to create a DSN see the related articles on the left). If you want to use a connection string, I'll explain how to later.
2. If you have a username/password then enter them in there, if you are using an access database with a password, then just use the password slow. Check 'Allow saving password' to save the password in the UDL file.
3. The initial catalog is for server users; you can use the drop-down list to see what's available.

The rest you can leave. If you want to specify a different provider, like Jet or OLE DB for SQL then you can change that under provider.  The one that's there (Microsoft OLE DB for ODBC Providers) should work with most (if not all) of your databases that you will be using.

DSN'ers

For all of you people with DSN's just do what I did here. I'm using a connection to an access database, so there is no username.

Non-DSN'ers

Your in a different boat. You have to use the connection string, which I'll guide you through. First, click on the 'Use Connection String' radio button, and you can copy your old connection string that I told you how to do, or click on "Build...." If you use the old connection string, you're fine.  If you click on "Build..." then use the tutorial below.

This will appear if you have a DSN up.  Then just click on the Machine Data Source tab and choose it. However, if you already have a File Data Source (a .DSN) file then browse for it in this tab, if you need a new one then click on New...

From here on in, it's basically like the DSN--choose your data type and click on Next... (I know that the picture says Finish.)

Now, this is where you save your .DSN file.  Create a location OUTSIDE of your web server root (so people can't download it) and save it with a descriptive name.

You're here again (if you've seen my DSN tutorial).  Click on Select to chose where the database is.  If there is a password, click on Advanced... (the Data Source Name and Description fields will be grayed out in your version of this screen.

And enter the password in the 'Password' slot. Chose Ok three times and you're here again:

Now, you can see the connection string is in and your all set!

Continuing On

Now, we have our connection string/DSN and our passwords all done.  Click on Ok, and we're into the ASP!

Your standard ASP connection stuff is there but it looks kinda like this:

Dim objConn             Dim connStr Set objConn = Server.CreateObject("ADODB.Connection")       connStr = "File Name=K:\da\conn.udl; " objConn.Open(connStr)

Your done; you can see that I used 'File Name' instead of that other mumbo jumbo.  Remember to put your UDL outside of your www pub folder, so people can't download it.