FIX: ADO 2.0: Open Connection May Succeed with Invalid Provider

If you specify a valid OLE DB provider in the ConnectionString property of a Connection object and then try to specify a provider that does not exist (or is otherwise not registered) on the system in the Provider property of the same Connection object, the Open method of the Connection object succeeds against the original provider.

The workaround for this code is to specify the provider in the ConnectionString property or on the Open method.

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

This problem is fixed in Microsoft Data Access Components 2.5 (MDAC 2.5). The latest version of MDAC can be obtained from the following Microsoft Web site:

WARNING: Despite the current fix, Microsoft does not recommended that you specify a provider in more than one place while calling the Open method because this behavior might result in unpredictable results.

Steps to Reproduce Behavior

This code only fails on a computer with MDAC 2.1 Service Pack 2 or earlier.

1. Create a new Standard EXE project in Visual Basic.
2. On the Project menu, click to select References. Add a reference to Microsoft ActiveX Data Objects 2.1 Library (OLEDB).
3. Add the following code to your Form1:

   Option Explicit
   
   Private Sub Form1_Load()
      Dim cn as ADODB.Connection
      Set cn = new ADODB.Connection
   
      cn.Open "Provider=SQLOLEDB;Data Source=sqltest;"
      cn.Close
   
      cn.Provider = "InvalidProvider"
      '  The following line will succeed without MDAC 2.5 or higher
      cn.Open
   End Sub
   					

For more information, please refer to the Platform SDK for Windows 2000.