Microsoft KB Archive/252888

Knowledge Base

PRB: Non-Parameterized SELECT Query Appears in ADO Procedures Collection

Article ID: 252888

Article Last Modified on 8/23/2001

---

APPLIES TO

• Microsoft ActiveX Data Objects 2.1
• Microsoft ActiveX Data Objects 2.1 Service Pack 1
• Microsoft ActiveX Data Objects 2.1 Service Pack 2
• Microsoft ActiveX Data Objects 2.5
• Microsoft ActiveX Data Objects 2.6
• Microsoft ActiveX Data Objects 2.7
• Microsoft Visual Basic 5.0 Professional Edition
• Microsoft Visual Basic 6.0 Professional Edition
• Microsoft Visual Basic 5.0 Enterprise Edition
• Microsoft Visual Basic 6.0 Enterprise Edition

---

This article was previously published under Q252888

SYMPTOMS

When using the Microsoft Jet OLE DB 4.0 provider and ADOX, a non-parameterized SELECT query can appear in the Procedures collection instead of the Views collection.

CAUSE

One or more tables referenced by the SELECT statement do not exist.

STATUS

This behavior is due to the way the Microsoft Jet OLE DB 4.0 provider maps Jet queries to ADOX Views and Procedures. It does not affect query execution.

NOTE: You need to create appropriate tables prior to executing the query.

MORE INFORMATION

The Microsoft Jet database engine does not divide queries into Procedures and Views; this is done by the Microsoft Jet OLE DB 4.0 provider. In general, a non-parameterized SELECT query is mapped to the ADOX Views collection and all other query types are mapped to the ADOX Procedures collection.

In order to determine whether a SELECT query is parameterized, all identifiers must be able to be resolved by the query engine. A query that references non-existing table or field names is mapped to the Procedures collection because the identifier names cannot be resolved.

Steps to Reproduce Behavior

1. Start a new Standard EXE project in Microsoft Visual Basic 5.0 or 6.0. Form1 is created by default.
2. Reference the following two object libraries:

   Microsoft ActiveX Data Objects 2.1 Library
   Microsoft ADO Ext. 2.1 for DDL and Security

3. Add a Command button (Command1) and the following code to the form:

   Private Sub Command1_Click()
   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"
   
   '
   ' Append the new Procedure
   '
     cat.Views.Append "SimpleSELECT", cmd
     cat.Views.Refresh
     cat.Procedures.Refresh
   
   '
   ' Enumerate Procedures and Views
   '
     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
                       

4. Run the project and click the Command button. The Debug/Immediate window shows that the SimpleSELECT query is a member of the Procedures collection.

Keywords: kbprb KB252888

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.