stored proc in Access

  • Thread starter Thread starter Leonard Poon
  • Start date Start date
L

Leonard Poon

Can we create stored procedures in Access project? I heard that we can do it
by ADOX programming. Is there any way we can create an Access web-page
report by making use of the returned recordset from those stored procedures?
I guess it'll be very inconvenient to embed the stored procedure codes
inside the programming statements. i.e. many single-quotes and
double-quotes.

Leonard
 
If using Access 2000 onwards, just create an Access Data
Project and it will allow you to create stores procedures
on the server from within the client project. Similar to
creating tables + queries in a normal Access database.
 
Thanks.
Now, I want to create reports. The data is based on the recordset that
returned by my parameterized stored procedures. The values of those
parameters are decided by users at runtime.

Is there any way I can learn about this on web?

Leonard
 
Leonard:

While you can base an Access 2002 or 2003 report on a record set, it is not
recommended. (Access 2000 doesn't support record sets as the basis for a
report.)

To start with the design and layout you might want to simply create a view
that provides sample data to use for that step. The set up the sproc as the
record source.

If you have a sproc that you need to run as the record source for your
report and supply parameters at run time, there are a couple of ways to do
this:

1.) Use a form to gather the parameters from the user. Then use the Input
Parameters property of your report to gather the parameters from the form to
supply to the sproc. (See the NorthwindCS sample database that comes with
Access and the Sales by Year report example)

2.) Use the Exec SQL command and supply the parameters and sproc as the
record source at run time (Access 2002/2003 only, not support in Access
2000). As in:

Dim strRecordSource As String
strRecordSource = "Exec [Sales By Year] '01/01/1996','07/31/1997'"
Me.RecordSource = strRecordSource

3.) You can also use SQL temp tables to store the output of your recordset
and tie your report to that data. Doing this is a bit more extensive, but
there's an explaination of how to do it on our web in the Code and Design
Tips area under the Reports section.
 
Back
Top