Passing parameters to Stored Procedure in Access

[Drew]

I have built a form and I have built a report, now I need to use the form to
collect 2 parameters which will then be passed on to a SQL Server Stored
Procedure. Is there anyway to do this with Access? I am using Access 2002
(XP).

Thanks,
Drew

 

[Guest]

hi,
the parameters must be in SQL Server stored procedure.
there is no way to past them if the procedure is not
looking for them.
you may have to re-write the procedure to include the
parameters.

 

[JohnFol]

Yes you can, but the implementation depends upon you using ADO / DAO /
a.n.other

 

[Douglas J. Steele]

You need to use VBA code that rewrites the SQL of the query before you run
it:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("MyPassthroughQuery")
strSQL = "EXEC Update_Allocation_Type @AllocationNM ='" &
Me.txtAllocation & "', @Parameter2=" & Me.txtValue
qdfCurr.SQL = strSQL
qdfCurr.Execute dbFailOnError

Alternatively,

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim rsCurr As DAO.RecordSet
Dim strSQL As String

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("MyPassthroughQuery")
strSQL = "EXEC Update_Allocation_Type @AllocationNM ='" &
Me.txtAllocation & "', @Parameter2=" & Me.txtValue
qdfCurr.SQL = strSQL
Set rsCurr = qdfCurr.OpenRecordset

Note that this is DAO code. By default, Access XP doesn't have a reference
set to DAO. If you haven't already done so, you'll need to go to Tools |
References while you've got the VB Editor open, scroll down until you find
the Microsoft.DAO 3.6 Object Library and put a check mark beside it.

 

[Drew]

Either ADO or DAO works for me... I currently have an ADP that I am using
for reporting. Would this work?

Thanks,
Drew

 

[Drew]

I have already written the stored procedure and it does have parameters.

Drew