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

Article translations Article translations
Article ID: 305388 - View products that this article applies to.
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).

Expand all | Collapse all

On This Page

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.

Properties

Article ID: 305388 - Last Review: August 10, 2004 - Revision: 4.0
APPLIES TO
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbprogramming kbdta kbprb kbvba KB305388

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com