• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

Retrieving stored procedure output through a recordset


In the function below, I'm attempting to get the returned data from a Sybase stored procedure.   When it's run, I get this error at the dbOpenRecordset:

The Microsoft Office Access database engine cannot find the input table or query 'get_itn 'rule_no', 1'.  Make sure it exists and that its name is spelled correctly. (#3078)

I've confirmed that the stored procedure runs fine at the isql command line on the server, just as it's called:   get_itn 'rule_no',1

Is it not possible to call a stored procedure this way?

Public Function GetItn(pstrColName As String, Optional plngCnt As Long = 1) As Long

   On Error GoTo 0   'force caller to handle any error
   Const strProcedureName = "GetItn"
   Dim rec              As Recordset
   Dim strSQL           As String
   Dim db               As Database
   Set db = CurrentDb
   strSQL = "get_itn '" & pstrColName & "', " & CStr(plngCnt)
   Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
   If rec.EOF Then
       Assert FAILURE, 101, "No value returned", strProcedureName
   End If

   '*** Return the internal number.
   GetItn = rec.Fields(0)
   On Error Resume Next
   Exit Function
End Function

Open in new window

  • 2
  • 2
  • 2
2 Solutions
Microsoft Access doesn't support the notion of stored procedures - you've only got tables and queries.  However, you should be able to get to your stored procedure using ADO instead of DAO.
You don't even have to use ADO, you can retrieve using DAO but its only read only
I stand corrected!  :-)
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

L_MalchiodiAuthor Commented:
We had done this previously with ODBCDirect, but it's no longer supported in 2007, so I'm trying to figure out another way to accomplish the same thing.   Is there an advantage to executing the stored procedure through DAO, as opposed to ADO?
I dont think there is advantage either way
L_MalchiodiAuthor Commented:
Thank you both!  Maybe I'll try it both ways, see what happens.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now