469,917 Members | 1,529 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

How to Create a Data Link File

ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim cnnSQL As ADODB.Connection
  2. Dim rstEmployees As New ADODB.Recordset
  3.  
  4. Set cnnSQL = New ADODB.Connection
  5.  
  6. cnnSQL.Open "File Name=" & "C:\Test\SQLServer.udl"
  7. Debug.Print cnnSQL.ConnectionString
  8.  
  9. rstEmployees.Open "Employees", cnnSQL, adOpenKeyset, adLockOptimistic
  10.  
  11. Do While Not rstEmployees.EOF
  12.   Debug.Print rstEmployees![FirstName] & " " & rstEmployees![LastName]
  13.   rstEmployees.MoveNext
  14. Loop
  15.  
  16. rstEmployees.Close
  17. cnnSQL.Close
  18.  
  19. Set rstEmployees = Nothing
  20. Set cnnSQL = Nothing
-----------------------------------------------------------------------------------------------------------------------

Connection String generated from code line #7:
Expand|Select|Wrap|Line Numbers
  1. Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;
  2. Initial Catalog=Northwind;Data Source=(local);Use Procedure for Prepare=1;
  3. Auto Translate=True;Packet Size=4096;Workstation ID=DEZII;
  4. Use Encryption for Data=False;Tag with column collation when possible=False
Dec 3 '07 #1
0 13155

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Lauren Quantrell | last post: by
3 posts views Thread by G rumpy O ld D uffer | last post: by
5 posts views Thread by nephish | last post: by
3 posts views Thread by shapper | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.