FIX: Property Change Through Collection Is Not Effective

• ActiveX Data Objects (ADO), versions 1.5, 2.0

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 this to be a bug in the Microsoft products 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://www.microsoft.com/data/download.htm

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.

Additional query words:

Keywords : kbADO150 kbADO150bug kbADO200 kbADO200bug kbDatabase kbVBp kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2fix
Version : WINDOWS:1.5,2.0
Platform : WINDOWS
Issue type : kbbug