Execute SQL Server 7 stored Proc from Access 97

The problem I have is how to execute a stored procedure on SQL Server 7 from Access 97 so that I can use the results returned as the record source for an Access 97 report.

The reason I am trying to achieve this is that I have a VB6 front-end that interogates an SQL Server database, allowing viewing and updating, making use of stored proc's. The employer insists that the reports are written using Access, and so I want to call the Access report from my VB program, which should cause no problems, but use the same stored procs that feed the VB app to feed the Access report.

I can create a pass-through query in Access that sends the SQL and returns results, but not worked out how to call the stored proc yet.

An alternative, if it is possible, would be to pass my ADODB recordset from VB to Access as the recordsource for the report, as this would mean the query is executed only once, and would be preferable.

Thanks in advance for any help.

Dave Seddon

In case anyone is interested, to call the stored proc, once the ODBC settings have been set on the pass-through query properties, simply enter the proc name in the SQL text window and run to view results.

To pass parameters, list the values to be passed after the proc name, eg. sp_MyProc Param1, Param2...

Not yet worked out how to pass the parameters from VB to the Access call of the stored proc (can do it from VB to the stored proc)