Pass thru query, SP and parameters

[croydon]

I am working on an Access 2003 database with a SQL Server 2005 back end.

I need to run a SQL Server Stored Procedure that requires one input parameter (to run a process) and an output parameter to show whether the process was successful.

I have created the SP and pass thru query but I cannot seem to code the VBA to make this work successfully. The code I am using is (derived from another thread):

Dim qdef As DAO.QueryDef

Set qdef = CurrentDb().QueryDefs("qrySendBatch")
qdef.sql = "exec sp_SendBatch " & list15.value
qdef.CreateProperty.Properties.Refresh

Any help would be appreciated.

 

[PHV]

Have you tried to play with an ADODB.Command object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JerryKlmns]

Another way is to

Dim cnn As ADODB.Conection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Conection
cnn.ConnectionString= "..."
cnn.open

'Using rst default values
Set rst = new ADODB.Recordset
Cnn.sp_SendBatch list15.value, rst
...
'Define your own
'Set rst = new ADODB.Recordset
'rst.CursorLocation=adUseClient
'rst.LockType=adLockOptimistic
'Cnn.sp_SendBatch list15.value, rst
...

That way you pass values to parameters using their ordinal position. If you skip one, you are using its default value

i.e. (three parameters)
Cnn.sp_SendBatch list14.value, ,list15.value, rst

 

[croydon]

Thanks for the replies.

JerryKlmns, if I were to run this from within SQL Server, the command would be:

EXEC sp_SendBatch @Batchid, @Status OUTPUT

Using your method, do I need to specify the second parameter as Output, and how to I access the value that is returned? Thanks.

 

[JerryKlmns]

My post was reffering to this article

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/namedparameters.asp

where I think for output parameters you do need the ADODB.Command object ...

 

[croydon]

I now have this working using ADODB. For anyone that's interested, my code is:

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim Para As ADODB.Parameter
Dim intResult As Integer

conn.ConnectionString = "driver=SQL Server;server=SERVUK;Database="Claims;"
conn.Open

cmd.CommandText = "sp_SendBatch"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = conn
Set Para = cmd.CreateParameter("@BatchId", adInteger, adParamInput, 4, List15.Value)
cmd.Parameters.Append Para
Set Para = cmd.CreateParameter("@Status", adInteger, adParamOutput)
cmd.Parameters.Append Para
cmd.Execute
intResult = cmd.Parameters("@Status")