Creating and Using a Data Link

Instead of using CurrentProject.Connection to set your connection string, or writing your own, you can use a Data Link. You can specify the Data Link filename as a parameter of the Connection object, but you have to create a UDL file first. To create a custom data link, do the following:

1. Open Windows Explorer and select File O New O Text Document.

2. Rename the file to something meaningful, and change its file extension to .UDL.

3. Click Yes in the message box that Windows subsequently displays to rename the extension.

4. Double-click the new UDL file and the Data Link Properties dialog box opens (see Figure 7-1).

Figure 7-1

5. On the Provider tab, select Microsoft Office 12.0 Access Database Engine OLE DB Provider, and click the Next button.

6. In the Data Source text box, type the name and path to your Access database. For example:

C:\Users\<user>\Northwind 2007.accdb

7. Click the Test Connection button. If all went well, the message box shown in Figure 7-2 displays.

Figure 7-2

8. Go to the Advanced tab and examine the permissions you can set for this link. For this demonstration, accept the default settings. If you want, the ALL tab can be selected to change the settings manually.

The UDL file is now created and should be working properly. You can specify the connection string by supplying the filename and path to the data link file just created. For example, a function could be written to open the connection from the UDL file:

Function CreateConnectionUDL() As ADODB.Connection

' Define Variables

Dim strConnectionString As String

Dim cn As New ADODB.Connection

' Open the Conection from the UDL file cn.Open "C:\MyConnection.UDL"

' Return the Connection Set CreateConnectionUDL = cn

End Function

0 0

Post a comment