FIX: Property Change Through Collection Is Not Effective

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.