Microsoft KB Archive/245384

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

Article ID: 245384

Article Last Modified on 8/23/2001

-

APPLIES TO


 * Microsoft Data Access Components 2.1

-



This article was previously published under Q245384



SYMPTOMS
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.



STATUS
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
 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

  Double-click Form1. Copy and paste the following code in the Form_Loadevent: 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  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.

Keywords: kbbug kbfix kbado260fix kbmdac250fix kbdatabase kbmdac260fix kbmdacnosweep KB245384

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.