ACC2000: You May Need to Refer to Jet Stored Procedures as Tables in ADO

Even though you can programmatically create a Microsoft Jet 4.0 QueryDef (stored procedure), you may not be able to access it by using the adCmdStoredProc constant with the CommandText property in ActiveX Data Objects (ADO).

Instead of the adCmdStoredProc constant, use the adCmdTable constant of the CommandText property.

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

Steps to Reproduce Behavior

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

The following example creates a Jet QueryDef (stored procedure) and tries to access it through ADO by using adCmdStoredProc:

1. Open the sample database Northwind.mdb.
2. Create a module and type the following line in the Declarations section:

   Option Explicit
   					

3. Type the following procedure:

   Public Function CreateProc()
      Dim strProc As String
       
      strProc = "Create Procedure qryCustByCity " & _
         "(prmCity varchar) as " & _
         "select * from Customers where City = prmCity"
       
      CurrentProject.Connection.Execute strProc
               
   End Function
   						

   The above function creates the following query:

      PARAMETERS prmCity Text;
      SELECT *
      FROM Customers
      WHERE (((Customers.City)=[prmCity]));
   					

4. In the same module, type the following procedure:

   Public Function RSFromParameterQuery(strCity As String)
      Dim prm As ADODB.Parameter
      Dim cmd As ADODB.Command
      Dim rst As ADODB.Recordset
     
      Set cmd = New ADODB.Command
      Set cmd.ActiveConnection = CurrentProject.Connection
     
      cmd.CommandText = "qryCustByCity"
      cmd.CommandType = adCmdStoredProc
     
      Set prm = cmd.CreateParameter("prmCity", adVarChar, adParamInput, _
         Len(strCity))
   
      prm.Value = strCity
     
      cmd.Parameters.Append prm
     
      Set rst = New ADODB.Recordset
      rst.Open cmd
     
      Do Until rst.EOF
         Debug.Print rst(0), rst(1), rst(2)
        rst.MoveNext
      Loop
     
   End Function
   					

5. Try to create a recordset from this "stored procedure" by entering the following code in the Immediate window, and then pressing ENTER:

   ?RSFromParameterQuery("London")
   					

Note that you receive the following error: For the example above to work, make the following change to the RSFromParameterQuery procedure: