How to Create a Data Link File

[ADezii]

Rather than using CurrentProject.Connection or entering your own Connection information, ADO supports storing Connection information in an external file called a Data Link File (which normally has a *.UDL extension). Data Link Files provide two very important capabilities:

1. They implement a graphical interface for constructing what can be complex and confusing OLE DB Connection Strings.
2. They offer a way to allow Users to edit Connection information, for example, by entering their own User Name and Password, without the necessity of writing code to capture this information.

-----------------------------------------------------------------------------------------------------------------------

The process of creating a new Data Link File is simple and straightforward:

1. Right Click in Windows Explorer and choose New ==> Text Document. Doing so will create a New Text Document (*.TXT) in the current Folder.
2. Rename the file to any name you like, changing the extension to .UDL. Windows will warn you that changing the file name extension may make the file unusable; respond Yes to this warning.
3. Double-Click the file to edit the Data Link properties.
4. Select the Provider tab and choose the appropriate Provider.
5. Select the Connection Tab and fill in the Database Name and Log in information.
6. Click the Test Connection button to verify that the information entered is correct, and then click OK to save the file.

-----------------------------------------------------------------------------------------------------------------------

Once you've created a Data Link File, you needn't supply any information when opening the Connection in code other than the location of the *.UDL file. This process makes it simple for you to manage changing Data Sources without having to modify any code. When you want to open a Connection based on a UDL file, you simply specify the *.UDL file name as the ConnectionString property, or specify this information when you call the Open Method.
For the purpose of this Tip, I'll create a Data Link File (SQLServer.udl) that will establish a Connection to the Northwind Database residing in a Local installation of SQL Server. A Connection will be opened via the Northwind.udl Data Link File, a Recordset will be created based on the Employees Table, and the First and Last names of Northwind Employees will be printed to the Immediate Window.

-----------------------------------------------------------------------------------------------------------------------

Relevant information regarding the creation of the Data Link File is as follows:

1. Provider Tab
   1. Provider: Microsoft OLE DB for SQL Server
2. Connection Tab
   
   1. Server Name: (local)
   2. Select a specific User Name and Password Log on option
   3. User Name: sa
   4. Password: none
   5. Select Database on Server: Northwind

-----------------------------------------------------------------------------------------------------------------------

Now, the code segment that ties it all together. Pay particular attention to Line #6:

Dim cnnSQL As ADODB.Connection
Dim rstEmployees As New ADODB.Recordset
 
Set cnnSQL = New ADODB.Connection
 
cnnSQL.Open "File Name=" & "C:\Test\SQLServer.udl"
Debug.Print cnnSQL.ConnectionString
 
rstEmployees.Open "Employees", cnnSQL, adOpenKeyset, adLockOptimistic
 
Do While Not rstEmployees.EOF
  Debug.Print rstEmployees![FirstName] & " " & rstEmployees![LastName]
  rstEmployees.MoveNext
Loop
 
rstEmployees.Close
cnnSQL.Close
 
Set rstEmployees = Nothing
Set cnnSQL = Nothing

-----------------------------------------------------------------------------------------------------------------------

Connection String generated from code line #7:

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;
Initial Catalog=Northwind;Data Source=(local);Use Procedure for Prepare=1;
Auto Translate=True;Packet Size=4096;Workstation ID=DEZII;
Use Encryption for Data=False;Tag with column collation when possible=False