Stored Procedures

[Netalie]

Hi,
i would like to know how can i use stored procedures in
sql server through access:
- How can i defined a report that will display records
using a stored procedure?
- How can i execute a stored procedure (sql server) from
access ( ihave linked tables to sqls.)?

Thanks

 

[Douglas J. Steele]

You can execute stored procedures using pass-through queries. The SQL of the
query would be something like:

EXEC Get_Allocation_Type

Note, though, that you cannot get pass-through queries to recognize
parameters. Should you need to pass parameters to your stored procedure, 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 & "'"
qdfCurr.SQL = strSQL
qdfCurr.Execute dbFailOnError

 

[Guest]

Thanks,

How can i set the record source of a report to display the
results retured form that procedure?