Sorry, wrong button.
Oh, you are using the DoCmd.RunSQL command; this explains why the water
was
muddy. This command is an old relic from previous version of Access, is
still around for backward compatibility and can only be used to sent a
quick
sql instruction to SQL-Server without expecting any result in return.
ADP use ADO and when you want a quick result back, you should use the
..Execute function of the connection object CurrentProject.Connection:
CurrentProject.Connection.Execute ("Insert ...")
or:
Dim rs as ADODB.Recordset
Set rs = CurrentProject.Connection.Execute ("Select * ...")
This connection object is a standard ADO connection object and the
..Execute() function has other optional arguments for which I don't
remember
the values. Take a look at the ADO documentation.
Personnally, I usually prefer to create an ADO command object and use it
to
pass arguments forth and back with my stored procedures. As you want
some
arguments back, this is also the method that you should use; unless you
don't want to get back any Select result. If this is the case, then you
can
use a Select statement to get back the desired values.
SQL Server doesn't make any distinction between INPUT/OUTPUT or OUTPUT
values, so all you to do is to declare them as OUTPUT and they will then
also be used as INPUT values:
CREATE Procedure dbo.spCRR_GetCrrNumber (
@ClientID int INPUT,
@CRRNumber int OUTPUT,
@DebtorID int OUTPUT
)As
Select * from blblaalalal
Return 0
GO
And to call it:
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "spCRR_GetCrrNumber"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("The_Return_Value", adInteger,
adParamReturnValue)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ClientID ", adInteger, adParamInput, ,
ClientID)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("CRRNumber ", adInteger, adParamOutput)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("DebtorID", adBSTR, adParamOutput)
cmd.Parameters.Append prm
cmd.Execute
This is from memory, without any testing. For GUID, I don't remember
it's
type but here's a little procedure to display the parameters and their
properties for any SP:
Sub liste_des_parametres(nom_procedure As String)
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = nom_procedure
cmd.Parameters.Refresh
Dim p As ADODB.Parameter
For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next
Set cmd = Nothing
End Sub
For using this procedure, you must *not* give the prefix dbo. to the name
of
the SP. See
http://www.asp101.com/articles/john/adovbs/adojavas.inc for
the
numerical values.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail:
http://cerbermail.com/?QugbLEWINF
Okay I think I need to step back a little and explain why I seem so
lost
with
this.
I see all these posts about ADO and DOA coding and quite honestly I
don't
know which one I use because I use Visual Studio Tools for MSOffice and
I
simply use the File, Connection, Data Link Properties to select my data
source (in this case it's a SQL 2005 backend) within my Access ADP.
I
never programatically define my source. I simple use SQL Views as the
source
to my forms and reports and my controls are bound to the fields from
the
views.
So that explains why I'm not clear on the syntax on how to do what I'm
trying to accomplish.
The actual code I use to call my stored procedure is as follows:
SQLStmt = "Exec dbo.spCRR_GetCrrNumber " & Me.ClientID & ", '" &
Me.Guid &
"'"
DoCmd.RunSQL SQLStmt
I had previously posted a question as to how I could get a return value
from
this stored procedure and I tried adding an OUTPUT on the stored
procedure
itself but I was told that it wasn't possible to get a return value
using
the
technique above.
So what I was thinking is can I simply run a SQL Select statement
similar
to:
Select CRRNumber from [testserver3\testdata].CRRWEB.dbo.crrnumbers
where
RecordID ='" & Me.Guid & "'"
As you can see, the CRRNUMBERS table is on a linked server. This table
stores a list of unique CRRNumbers and DebtorID's which are generated
by
the
stored procedure. The reason this table is on a remote server is that
we
have two applications that need CRRNumbers and DebtorID's generated -
my
internal application and a public-facing web portal. So this table
allows
us
to make sure we're not generating a CRRNumber or DebtorID that's
already
used.
When I initially call the stored procedure from my application I pass
the
ClientID and GUID number to the stored procedure. My application needs
to
have a CRRNumber and DebtorID which is what the stored procedure does
for
me.
This generates a new record in the CRRNumbers table. This new record
uses
the GUID I passed it and stores it in the RecordID field. It uses the
ClientID to generate a new unique CRRNumber and DebtorID. These are
the
two
fields I want to pass back to my application but am struggling with.
Since I generate the GUID with my application and pass it to the stored
procedure this is my link between my application and the CRRNumbers
table
that I was hoping to use in my select statement to pull out the two
values
I
need.
Sorry for the lengthy explanation but I'm hoping this makes more sense.
Thanks for your patience.
:
I'm still not sure that I understand what you are trying to do. I
don't
see
why you should have any problem at writing something like
Select @debtorid, @CRRNumber
at the end of your stored procedure. When you call your SP, call it
by
using the OPEN statement of a recordset to capture the result in the
recordset.
You say that you are passing some parameters to your stored procedure;
however, you did not say which method you have used to do so. There
are
many ways of calling a SP and passing parameters, so I cannot tell you
more
at this moment with the current information that you have provided.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail:
http://cerbermail.com/?QugbLEWINF
Sylvain can you give me an example of what you mean by "Don't be so
complicated and make a SELECT statement at the end of your SP and
capture
the
result in a recordset."
This would probably point me in the right direction.
Thanks in advance
Guy
:
You can use the output feature of parameters for a SP if you are
using
an
ADODB command object. Of course, you cannot do that if you are
using
a
SP
bound to a form. For example of using a command object, see the
previous
posts in this newsgroup.
A second way of returning more than a single parameter is simply to
use a
recordset.
I don't understand what you mean by « So as an alternative I use a
SQL
Select statement where GUID = linked server GUID in the hope I can
return
these two values. ».
Don't be so complicated and make a SELECT statement at the end of
your
SP
and capture the result in a recordset.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail:
http://cerbermail.com/?QugbLEWINF
news:
[email protected]...
