FIX: Parameterized Query Moves from Procedures Collection to Views Collection When Tables Referenced in Query Do Not Exist

When using the Microsoft Jet OLEDB Provider 4.0 and ADO 2.1 and appending a parameterized query to the Procedures collection, the query is actually appended to the Views collection if the tables referenced do not exist.

This problem is observed in MDAC 2.1 version 2.1.0.3513.2.

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 MDAC 2.1 SP1 (version 2.1.1.3711.11) and later.

Steps to Reproduce Behavior

1. Start a new Standard EXE project in Visual Basic. Form1 is created by default. Reference the following two object libraries:
   Microsoft ActiveX Data Objects Library
   Microsoft ADO Ext. for DDL and Security
2. Double-click Form1. Copy and paste the following code in the Form_Load()event:

   Private Sub FORM_LOAD()
      Dim cnn As New ADODB.Connection
      Dim cmd As New ADODB.Command
      Dim prm As ADODB.Parameter
      Dim cat As New ADOX.Catalog
      Dim strDB As String
      Dim i  As Integer
   
   ' Append parameterized query
      strDB = "MyTest.mdb"
      If Dir(strDB) <> "" Then Kill (strDB)
         cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB
         cmd.CommandText = "Select * From Table1 Where Fld1 = [CustId]"
         Set prm = cmd.CreateParameter("CustId", adVarChar, adParamInput, 5)
         cmd.Parameters.Append prm
   
   ' Append the new Procedure
      cat.Procedures.Append "CustomerById", cmd
      cat.Procedures.Refresh
      cat.Views.Refresh
      If cat.Procedures.Count = 0 Then
         Debug.Print "No procedure in Procedures collection"
      Else
         For i = 0 To cat.Procedures.Count - 1
            Debug.Print "Procedures Collection: " & cat.Procedures(i).Name
         Next
      End If
      If cat.Views.Count = 0 Then
         Debug.Print "No View in Views collection"
      Else
         For i = 0 To cat.Views.Count - 1
            Debug.Print "Views Collection: " & cat.Views(i).Name
         Next
      End If
   End Sub
   					

3. Run the project. With version 2.1.0.3513.2 of MDAC, the result in the Immediate Window incorrectly shows that no query exists in the Procedures collection; it is listed in the Views collection. With later versions of MDAC, the query correctly appears in the Procedures collection.