Access Reports calling SQL Server Stored Procedures

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to access reporting.

I am develping an application with Access 2K as front end (GUI) and SQL Server 2K as back end to hold the database.

I need a VBA code sample (or URL) to do following things:
1. Execute a stored procedure on SQL 2K
2. Get ADODB recordset and assign to report
3. Open report in preview mode

Please suggest me, if there are any other ways to do this.

Thanks in advance.
 
You can execute a stored procedure in and Access MDB by using a Pass-Through
query. Is there a reason why you think you need an ADODB recordset with a
report rather than a resultset returned by a table or query?

--
Duane Hookom
MS Access MVP


Kumar said:
I am new to access reporting.

I am develping an application with Access 2K as front end (GUI) and SQL
Click to expand...
Server 2K as back end to hold the database.
 
In Access 2000, you can't assign a recordset to a report. It doesn't work.
In Access 2002 and 2003 you can, but it won't work effectively if you use
grouping and sorting in the report.

If you are using an MDB with linked SQL tables, as Duane indicated, use a
stored pass through query which as in:

[Sales by Year] @Beginning_Date = '7/1/96', @Ending_Date ='1/31/97'

as the SQL to a saved query. You can simply change the query's sql to
adjust the parameters. (Which is much easier to do using DAO rather than
ADOX, simply add the reference to DAO to the Db.)

If you are using an ADP in Access 2000 and you have input parameters, they
must be set in the Input parameters property of the report. The input
parameters can not be effectively changed at run time in Access 2000, so the
best way to deal with that, especially in A2000 is to use a form and have
the report's input parameters reference the form fields. (See the Northwind
ADP sample db Sales By Year example report).

If you need set the parameters at run time via code in an ADP in Access
2000, the answer is its a pain to do so, especially in a multi-user
environment; however if you look on our web in the Code and Design tips area
in the reports section, there's an example of a way to do that as well.

If you are using an ADP, the best thing you can do is to upgrade to Access
2003; Access 2000's ADPs were under developed and a pain to work with in
general.
 
Back
Top