OpenConnection method in an Access project does not work with UDL files in Access 2002 (305388)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q305388
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you try to pass a valid UDL file as the BaseConnectionString argument of the CurrentProject.OpenConnection method in an Access project (ADP), you receive no error message, but the ADP file is not connected.

CAUSE

The BaseConnectionString argument of the OpenConnection method does not accept UDL files.

RESOLUTION

To work around this behavior, use the UDL file to open an ADO connection, and then pass the connection string information of the newly opened connection object as the BaseConnectionString argument of the CurrentProject.OpenConnection method. For an example of how to do so, follow these steps:
  1. Create a UDL file that has connection information for a valid SQL Server using the Microsoft OLEDB driver for SQL Server (see the "More Information" section later in this article for instructions on how to create a UDL file). Save the file as Test.udl.
  2. Open the sample project NorthwindCS.adp.
  3. In the Database window, click Forms under Objects, click New, and then click OK to open a new form in Design view.
  4. Add a command button to the new form.
  5. Set the OnClick property of the command button to the following event procedure:
    On Error GoTo EH:
Application.CurrentProject.CloseConnection
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
'Change the following path to your actual path to Test.udl
cnn.Open "File Name=C:\Path\Test.udl;"
Application.CurrentProject.OpenConnection cnn.ConnectionString
cnn.Close
Set cnn = Nothing
If CurrentProject.IsConnected Then
 'It worked.
MsgBox "You are now connected using a UDL file"
Else
 'It didn't work.
MsgBox "Unable to Connect using the specified UDL file"
End If
Exit Sub
EH:
MsgBox Err.Number & ": " & Err.Description
  6. Close the Visual Basic Environment to return to the form.
  7. Save the form, and then open it in Form view.
  8. Click the command button to run the underlying code.
Note that you receive a message box that indicates success.

MORE INFORMATION

UDL files are text files that have connection information for OLE DB data sources, much in the same way that DSN files have connection information for ODBC data sources. If applications use a common UDL file to get connection settings, changing the connection settings of the UDL file can then affect any applications that rely on it. For example, UDL files can be useful for redirecting ADP files to a backup server when the main server is offline.

For additional information about how to create UDL files and to use them to create connections using ADO, click the following article numbers to view the articles in the Microsoft Knowledge Base:

189680 How To Use Data Link Files with ADO

300261 How To Create an ADO Connection from a Data Link File in Data Access Components

Although the OpenConnection method of the current project is similar to the ADO Open method, the two are not exactly the same. In this case, although specifying a UDL file in the Open method of an ADO Connection object works, it does not work in the OpenConnection method.

For additional information about using the OpenConnection method , click the following article number to view the article in the Microsoft Knowledge Base:

306881 How to programmatically change the connection of a Microsoft Access project

Steps to Reproduce the Behavior

  1. Open the sample project NorthwindCS.adp.
  2. In the Database window, click Forms under Objects, click New, and then click OK.
  3. Add a command button to the new form.
  4. Set the OnClick property of the command button to the following event procedure:
    On Error GoTo EH:
Application.CurrentProject.CloseConnection
 'It does not matter if the UDL file path is valid or not.
Application.CurrentProject.OpenConnection "File Name=C:\Path\Test.udl;"
Exit Sub
EH:
MsgBox Err.Number & ": " & Err.Description
  5. Close the Visual Basic Environment to return to the form.
  6. Save the form, and then open it in Form view.
  7. Click the command button to run the underlying code.

    Note that you do not receive an error message. However, the project is not connected because the OpenConnection method was not successful.
Modification Type:MajorLast Reviewed:8/10/2004
Keywords:kbProgramming KbVBA kbdta kbprb KB305388
©2004 Microsoft Corporation. All rights reserved.