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:
| ¤ | The type of data that you want to access. |
| ¤ | The server on which the data resides. |
| ¤ | The database in which the data is stored. |
So, to access a SQL database, you would have to provide the following:
| ¤ | The OLE DB provider for SQL Server. |
| ¤ | A server name. |
| ¤ | A database name. |
| ¤ | User Name |
| ¤ | Password |
OK, raise your hand if your code looks like one of these:
| ¤ | 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
.Open
End With
| ¤ | With DSN Connection |
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:
| ¤ | 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. <o:p></o:p>
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:
- Double click on the UDL file to bring up the Data Link Properties Dialog Box.
- Click on the Providers tab and select the driver you wish to use.
- 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.
- Click on the “Test Connection” button to verify your connection is working correctly. Change accordingly if you get an error.
- 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
Source: here
