FIX: Property Change Through Collection Is Not Effective (192644)


The information in this article applies to:

  • ActiveX Data Objects (ADO) 1.5
  • ActiveX Data Objects (ADO) 2.0
  • ActiveX Data Objects (ADO) 2.1
This article was previously published under Q192644

SYMPTOMS

Setting the properties of a connection's existing ConnectionString, and using the Properties collection prior to connecting may not update the connection string used by the connection object to connect to the database.

CAUSE

If the connection string specifies a property that is set through the Properties collection, the connection string takes precedence.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

This bug has been fixed in Microsoft ActiveX Data Objects 2.1 Service Pack 2 and later.

You can download Microsoft ActiveX Data Objects from the following site:

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp

MORE INFORMATION

  1. Start a new Standard EXE project.
  2. Set a reference to the Microsoft ActiveX Data Objects 2.0 Library.
  3. Place a command button on the form.
  4. Paste the following code in the Click event of the command button: 
    Dim cn As New ADODB.Connection

      With cn
       'Set the initial connection string.
       'Following line should be commented to test for the workaround
       'in the second test as mentioned in Step 6.
       .ConnectionString = "Provider=SQLOLEDB;Data Source=<yourserver>;" &
             "Initial Catalog=pubs;" 

       ' Following line has to be uncommented when you are commenting
       ' the preceding line as mentioned in step 6.
       '.ConnectionString = "Provider=SQLOLEDB;Data Source=<yourserver>"
       ' If Initial Catalog is not set in connection string,
       ' it works correctly

       'Check which is the current catalog.
       Debug.Print " Catalog is "; .Properties ("Initial Catalog")

       .CursorLocation = adUseClient

       'Override connection string with property setting.
       .Properties("Initial Catalog") = "NorthWind"
       'Check which is the current catalog, has it changed?
        Debug.Print " Catalog is "; .Properties ("Initial Catalog")


       'Open the connection.
       .Open UserID:="sa"

       'Check the database that was opened, is it the one you wanted ?
       Debug.Print " Catalog "; .Properties ("Current Catalog")

      End with
      Set cn = nothing
  5. Execute the program. Even though you attempted to set the Initial Catalog property to the NorthWind database, after opening the connection, the connection string displays Pubs as the database the Initial Catalog.

    Results For Setting the "Initial Catalog" Initially

    =============================================================== 
    '.ConnectionString = "Provider=SQLOLEDB;Data Source=nuvee;Initial
   Catalog=pubs;"
    ===============================================================

    Catalog is pubs
    Catalog is northwind
    Catalog is pubs
  6. Comment the line where the .ConnectionString has the Initial Catalog set in the string itself and uncomment the line that does not set the Initial Catalog inline. Execute the program. Results For not Setting the Initial Catalog Initially

    =============================================================== 
    '.ConnectionString = "Provider=SQLOLEDB;Data Source=nuvee;"
    ===============================================================

    Catalog is
    Catalog is northwind
    Catalog is northwind

    RESULTS: Now you can connect exclusively to the database.
Modification Type:MinorLast Reviewed:3/2/2005
Keywords:kbADO210sp2fix kbbug kbDatabase kbfix kbfix kbMDACNoSweep KB192644
©2004 Microsoft Corporation. All rights reserved.