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:
- 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: - 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
