8,834
- They implement a graphical interface for constructing what can be complex and confusing OLE DB Connection Strings.
- 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:
- Right Click in Windows Explorer and choose New ==> Text Document. Doing so will create a New Text Document (*.TXT) in the current Folder.
- 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.
- Double-Click the file to edit the Data Link properties.
- Select the Provider tab and choose the appropriate Provider.
- Select the Connection Tab and fill in the Database Name and Log in information.
- 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:
- Provider Tab
- Provider: Microsoft OLE DB for SQL Server
- Connection Tab
- Server Name: (local)
- Select a specific User Name and Password Log on option
- User Name: sa
- Password: none
- 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
- 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:
Expand|Select|Wrap|Line Numbers
- 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
