how to pass a return value from a sql 2000 stored procedure into access vba routine

[Keith G Hicks]

I need to have a SQL 2000 stored procedure pass an output parameter back to
my MS Access code. I've got the sp all set up but how do I execute it and
get the value back in vba?
I just need to pass a couple of parameters to the sp so that the select in
the sp will return the correct record and then I need to pass back a
particular field's value of that record.

Here's my sp so far:

CREATE PROCEDURE spE_GetAssetRec
@MoveDirection as char(1),
@UnitNum as Int,
@CatFilter as varchar(30),
@ReturnUnitNum as Int Output

AS
Set NoCount On

Declare
@Sql as varchar(8000)


If @CatFilter = ''
Select @CatFilter = ''
else
Select @CatFilter = ' And AssetCategoryCode = ''' + @CatFilter + ''''

If @MoveDirection = 'N'
begin
Select @Sql = 'Select Top 2 UnitNumber From tEAssets Where UnitNumber >= '
+ str(@UnitNum) + @CatFilter + ' Order By UnitNumber'

EXEC ( 'Declare Cur_Assets Insensitive cursor for ' + @Sql)
Open Cur_Assets

-- go to the NEXT unit number
Fetch Next from Cur_Assets INTO @ReturnUnitNum

-- Still need to account for end of file

end
--exec(@sql)
Return @ReturnUnitNum

Close Cur_Assets
Deallocate Cur_Assets
GO


Again, I just need code to run the sp from Acess and get the return value
into a variable in vba.


Thanks,

Keith

 

[Vadim Rapp]

KG> I need to have a SQL 2000 stored procedure pass an
KG> output parameter back to my MS Access code.

For an s.p like this

create PROCEDURE sptest (@p1 int) AS return @p1*2

the VBA code would be

Dim c As New ADODB.Command, Result As Integer
c.CommandText = "{? = CALL dbo.sptest(?) }"
c.ActiveConnection = CurrentProject.Connection
c.Parameters.Refresh
c.Parameters("@p1") = 3
c.Execute
Result = c.Parameters("@return_value")

Note that we didn't declare the output parameter in the s.p.


Though it would be easier to implement as scalar function:

create FUNCTION dbo.Function1 (@p1 int) RETURNS int AS
BEGIN
RETURN @p1*2
END

then the code would be

Result = CurrentProject.Connection.Execute("select dbo.function1(4)")(0)


Vadim

 

[Keith G Hicks]

Vadim,

Thanks for the info.
What are the question marks doing in the command text? I'm not familiar
with this.
Also, I get the following error on the c.Parameters.Refresh line:

Invalid SQL Statement; Expected "DELETE", "INSERT", "PROCEDURE", "SELECT" or
"UPDATE."

Keith

 

[Vadim Rapp]

KG> What are the question marks doing in the command
KG> text? I'm not familiar with this.

It's in the article "How to call stored procedures (ODBC)" in Books Online

KG> Also, I get the following error on the
KG> c.Parameters.Refresh line:

KG> Invalid SQL Statement; Expected "DELETE",
KG> "INSERT",
KG> "PROCEDURE", "SELECT" or "UPDATE."


hmm.. I just tried it here and it worked... are you sure you copied
everything correctly? created stored procedure sptest?

You can try more traditional way:

c.CommandText = "sptest"
c.CommandType = adCmdStoredProc


Vadim

 

[Keith G Hicks]

Vadim,

Ok. I see what the ? are for. Just placeholders. That makes sense.

Another problem now:
Since I'm in an MDB and not an ADP I get the feeling from other posts that
CurrentProject.Connection does not work here since it points to the mdb. I
tried the same connection string that I use for creatign a pass thru query
for reports but that didn't work. I have a DSN connection for the linked
tables. The folllowing is the string that I use for my report code:

strSQLConnectionString =
"ODBC;DSN=AEQ;UID=anyuser;PWD=mypwd;DATABASE=mydatabase"

I'm not a conection string expert. They seem to be all over the map as far
as how they are set up. How do I need to do the follwoing given what I have
above?

c.ActiveConnection = CurrentProject.Connection
c.Parameters.Refresh

Thanks again,

Keith

 

[Keith G Hicks]

never mind. i got it.

 

[Vadim Rapp]

KG> Since I'm in an MDB and not an ADP

note the name of this newsgroup.

Vadim

 

[Van T. Dinh]

Most of the times, I have to check carefully whether
people mean MDB or ADP since there is no separate
newsgroup for MDB with SQL Server back-end and this
newsgroup got "SQLServer" in the name so people will post
questions about MDB with SQLServer back-end.

Cheers
Van T. Dinh
MVP (Access)

 

[Keith G Hicks]

Thank you. You're absolutely right.
I looked at all the MS NG's and this was the one that made the most sense.
There's not always a NG that is exactly what you need so you have to choose
whatever is closeset!

One other note. I posted some questions a year or so ago about whether it
was best to use an mdb or an adp with a sql back end. Most of the responses
were adp is a waste of time. There are some advantages, but too many
disadvantages (don't remember them off hand right now). I had the feeling
that the adp idea didn't really take hold. That being the case, it was
clear why this ng has so few posts compared to most of the other MS NG's.
Since adp seemed to be dead wood, it made sense to as an mdb question in
here.

 

[Van T. Dinh]

I don't think ADP is a dead wood. It is probably the way to go in the
future since using MDB with Tables linked from SQLServer back-end, you are
basically use ODBC and Microsoft consider ODBC driver obsolete.

Personally, I still use MDB since it is more or less my comfort zone.

 

[Jay]

I'm trying to implement this, but when I hit the line:'

Result = c.Parameters("@return_value")

I get the error "Item cannot be found in the collection
corresponding to the requested name or ordinal"

What am I missing?

Jay