On the Fly...Use SQL Stored Proc as recordsource of report

[ssecca]

Is it possible to call a SQL Stored procedure and use it as a recordsource in a report. I am using Access(2002). I am still more fimilar with DAO than ADO, but with some help will try either or any ideas.

 

[dhookom]

Assuming this is a standard MDB rather than an ADP, you could create a function that would change the SQL property of any saved query.

Function ChangeSQL(pstrQuery as String, _
pstrSQL as String) as Boolean
CurrentDb.QueryDefs(pstrQuery).SQL = pstrSQL
ChangeSQL = True
End Function

Save the function above in a new, standard module and don't name the module the same as the function. You can then use the function prior to opening the report.

Dim strSQL as String
strSQL = "EXEC spMySP " & Me.EmpID & ",'" & _
Me.txtStartDate & "', '" & Me.txtEndDate & "'"
If Not ChangeSQL("qsptMyPassThrough", strSQL) Then
msgbox "had a problem"
End If
DoCmd.OpenReport "rptBasedOnPTQuery", acViewPreview



Duane
MS Access MVP