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

ID: Q225918

The information in this article applies to:

Microsoft Access 2000

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

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

RESOLUTION

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

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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:

Open the sample database Northwind.mdb.

Create a module and type the following line in the Declarations section:


Option Explicit

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]));

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

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:

Invalid SQL Statement; expected 'DELETE', 'INSERT','PROCEDURE', 'SELECT' or 'UPDATE'

For the example above to work, make the following change to the RSFromParameterQuery procedure:

Change the line

cmd.CommandType = adCmdStoredProc

to the following:

cmd.CommandType = adCmdTable

Additional query words: pra

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug