Thomas said:
Anyone know how is the best way to do this? Can't use
just a pass-through-query because parameters are required.
Thanks in advance to anyone that helps!
Regards
Well, you can use a pass through query, however you have to programmatically
define the query and execute it. It's not trivial and it took me a while to
understand.
I suggest you look at "MS Access Developer's Guide to SQL Server" by Mary
Chipman which has a chapter which explains this in great detail.
Executing a s.proc from Access isn't too bad using ADO.
Below is an extract from a module I've recently written to execute a s.proc
to insert a new record; I hope you find it of use.
Hope this helps.
Chris Strug.
Dim cmd as ADODB.Command
Dim prm as ADODB.Parameter
Set cmd = New ADODB.Command
'The name of the stored procedure to execute.
stcmdtext = "procContainerInsert"
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = stcmdtext
.CommandType = adCmdStoredProc
'Define the parameters for the S.proc and attach them to the
cmd object.
Set prm = New ADODB.Parameter
prm.Name = "ContainerNo"
prm.Type = adChar
prm.Size = 11
prm.Value = NewData
prm.Direction = adParamInput
.Parameters.Append prm
Set prm = New ADODB.Parameter
prm.Name = "ContainerSize"
prm.Type = adChar
prm.Size = 2
prm.Value = stContainerSize
prm.Direction = adParamInput
.Parameters.Append prm
Set prm = New ADODB.Parameter
prm.Name = "ContainerType"
prm.Type = adTinyInt
prm.Value = iContainerType
prm.Direction = adParamInput
.Parameters.Append prm
Set prm = New ADODB.Parameter
prm.Name = "ReturnCode"
prm.Type = adInteger
prm.Direction = adParamOutput
.Parameters.Append prm
Set prm = New ADODB.Parameter
prm.Name = "ReturnDescription"
prm.Type = adVarChar
prm.Size = 100
prm.Direction = adParamOutput
cmd.Parameters.Append prm
.Execute
End With
