Instead of using CurrentProject.Connection to set your connection string, or writing your own, you can use a Data Link.
You can specify a 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 | New | 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.
4. Then double-click the file; the Data Link Properties dialog box is displayed, as in Figure 7-1.
5. For the purpose of demonstration, select Microsoft Jet 4.0 OLE DB Provider, and click Next.
6. Browse and select an Access database (*.mdb).
7. Enter your username and password (if applicable), remembering that the default username for an unsecured Access database is Admin, with a blank password. If you want to avoid having to enter your password every time you make a connection, tick the Allow saving password check box.
8. Click Test Connection. If all went well, the message box is displayed, as in Figure 7-2.
9. Select the Advanced tab and examine the permissions you can set for this link. For this demonstration, accept the default settings. If you want, you can select the All tab and change the settings manually.
You can then specify the connection string by supplying the filename and path to the data link file just created.
Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.Open "File Name=C:\myDatabase\myDataLink.udl"
Was this article helpful?