Microsoft KB Archive/225918

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

Article ID: 225918

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q225918





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 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:  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 </li>  Try to create a recordset from this "stored procedure" by entering the following code in the Immediate window, and then pressing ENTER: ?RSFromParameterQuery("London") </li></ol>

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: kbbug kbpending KB225918

-

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

© Microsoft Corporation. All rights reserved.