How to capture result from stored procedure

[shabba]

I am using Access with SQL backend. I have a stored proc in sql that returns an int. I want to capture this value and use it in my code. How do I do this?

[KnooKie]

you can do it like the example below but there may be better ways...

[vb]
Dim qdf as QueryDef
Dim rst as recordset
Dim wrkODBC As Workspace, cn As Connection, dbs as Database

Set dbs = CurrentDb

Set wrkODBC = CreateWorkspace("", "sa", "", dbUseODBC)
Set cn = wrkODBC.OpenConnection("NewConnection", dbDriverNoPrompt, False, "ODBC;DATABASE=MyDB;UID=MyUID;PWD=MyPswd;DSN=MyDSN ")

Set qdf = cn.CreateQueryDef("q1")

qdf.SQL = "{ call usp_MyStoredProc }"
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
[/vb]

the recordset will be returning your integer

[shabba]

if result set returns a singe integer value, can I just declare an int variable and store result in it?

[KnooKie]

yes absolutely...

Dim MyInteger as Integer

MyInteger = rst.Fields(0)

[shabba]

i do
dim synsetno as integer
Set db = CurrentDb
Set qdflist = db.QueryDefs("runstoredproc") 'passthroughquery
strsql = <some string>
qdflist.SQL = strsql
DoCmd.SetWarnings (False)
DoCmd.OpenQuery "runstoredproc"

which works

when i try
qdflist.openrecordset instead of docmd.openquery
synsetno = qdflist.Fields(0)

It tells me field not found. The stored proc ran ok and I get a datasheetview of the result integer returned. How do I store the result in a variable. I tried your method also, but to no avail.

[KnooKie]

see if this helps...

http://www.sqlteam.com/forums/topic....ored,procedure

[bannor]

Maybe try this (I bolded my modifications).

dim rs As recordset
dim synsetno as integer
Set db = CurrentDb
Set qdflist = db.QueryDefs("runstoredproc") 'passthroughquery
strsql = <some string>
qdflist.SQL = strsql
Set rs = qdflist.openrecordset
synsetno = rs.Fields(0)