Passing parameters to Stored Procedure in Access

D

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
 
G

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.
 
J

JohnFol

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

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.
 
D

Drew

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

Thanks,
Drew
 
Top