Access Reports using ASP

Hi people,

I know that the question has come around sometimes:
How to open an Access Report using ASP and export it to for example
RTF.

I'm trying to implement the first method of David Cline:

http://www.15seconds.com/issue/981216.htm

The first section describes how it should be done, and it sounds good
to me.

Way of working:

1. open Access object
2. open the needed database
3. open the report fromthe database
4. open the query that belongs to the report
5. fill the parameters from the query from a HTML form, if the params
are not filled, display the HTML form
6. execute the query with params, so the report gives the correct data
7. export the report to RTF format

every step works in this process, except step 6. Dave Cline's example
states a:
qry.execute

then I get an:
Error Type:
DAO.QueryDef (0x800A0BF9)
Cannot execute a select query.

This is quite logical, because the query is a select query.

If I leave out step 6, I get dialog popupboxes where I need to fill in
the parameters for the query, and I don't want that.

Anyone have an idea what I should change?
(I know this isn't the most nicest solution for the server, but it's
only for inhouse use)

some code snippets:

strReportName = "frmPOST_DATUM"
blnRunReport = True

'--- create an instance of Access --
set mobjAccess = createobject("access.application.11")
mobjAccess.OpenCurrentDatabase
"C:\Inetpub\wwwroot\kempen\database\post.mdb"
mobjAccess.Visible = False

'--- the DoCmd object is the easiest way to open a report --
Set MyDoCmd = mobjAccess.DoCmd
MyDoCmd.OpenReport strReportName, 1  '1=design, 0=normal, 2=preview

'--- create a reference to the now open report --
Set rpt = mobjAccess.Reports(0)

'--- Open the Query the report is based upon –
Set qry = mobjAccess.CurrentDb.QueryDefs("qryPOST_REPORT_DATUM")

'--- loop through the queries parameters --
If qry.Parameters.Count > 0 then
Response.write "<form action=""report.asp"" method=""post"">"

For Each param In qry.Parameters

'--- to print the report make sure we have all parameters filled in --
If request(param.name) <> empty then
param.Value = request(param.name)
Else

'--- we have an empty parameter do not run report –-
blnRunReport = False
response.write "ja echt?"
End if

'--- provide the text fields for parameter input --
Response.write param.name & " <input type=text name=""" &_
param.name & """ value=""" & request(param.name) & """>" & "<BR>"
Next
Else
Response.write "No Parameters Found for this Query:" & qry.Name &
"<BR>"
End if

qry.Execute

'--- export the report to the selected format --
MyDoCmd.OutputTo 3, strReportName, "Rich Text Format (*.rtf)",
PATH & "\www\reports\report.rtf"
'--- Close the Query --
qry.Close

'--- close the report --
MyDoCmd.Close 3, strReportName, 2 '2=saveno 3=Report

'--- clean up objects used --

It goes wrong at the qry.Execute
I know that you can't execute a select query, and need a recordset for
that.
I just want to get rid of the popup dialogs for filling in the
parameters.

Any ideas?

You can run into lots of problems opening instances of Access directly from
asp pages.
While it might work, if you ever get any kind of concurrency on your web
site your server
could be in trouble from the instances of Access opening and closing.

We have been selling a solution to run Access reports over the web for a few
years now you can download
our eval and try it out at:

RPT Software
http://www.rptsoftware.com

Most people develop web sites to produce reports in PDF format, with Excel
being second favorite choice
and RTF probably third.

HTH,
Mark Andrews

PS: For more details just send me an email to the support email at RPT
Software.

"Joris Kempen" <joris.kempen@groupsupport.com> wrote in message
news:ac799ae6.0412220842.5d12fb29@posting.google.com...
> Hi people,
>
> I know that the question has come around sometimes:
> How to open an Access Report using ASP and export it to for example
> RTF.
>
> I'm trying to implement the first method of David Cline:
>
> http://www.15seconds.com/issue/981216.htm
>
> The first section describes how it should be done, and it sounds good
> to me.
>
> Way of working:
>
> 1. open Access object
> 2. open the needed database
> 3. open the report fromthe database
> 4. open the query that belongs to the report
> 5. fill the parameters from the query from a HTML form, if the params
> are not filled, display the HTML form
> 6. execute the query with params, so the report gives the correct data
> 7. export the report to RTF format
>
> every step works in this process, except step 6. Dave Cline's example
> states a:
> qry.execute
>
> then I get an:
> Error Type:
> DAO.QueryDef (0x800A0BF9)
> Cannot execute a select query.
>
> This is quite logical, because the query is a select query.
>
> If I leave out step 6, I get dialog popupboxes where I need to fill in
> the parameters for the query, and I don't want that.
>
> Anyone have an idea what I should change?
> (I know this isn't the most nicest solution for the server, but it's
> only for inhouse use)
>
> some code snippets:
>
>  strReportName = "frmPOST_DATUM"
>  blnRunReport = True
>
> '--- create an instance of Access --
>  set mobjAccess = createobject("access.application.11")
>  mobjAccess.OpenCurrentDatabase
> "C:\Inetpub\wwwroot\kempen\database\post.mdb"
>  mobjAccess.Visible = False
>
> '--- the DoCmd object is the easiest way to open a report --
>  Set MyDoCmd = mobjAccess.DoCmd
>  MyDoCmd.OpenReport strReportName, 1  '1=design, 0=normal, 2=preview
>
> '--- create a reference to the now open report --
>  Set rpt = mobjAccess.Reports(0)
>
> '--- Open the Query the report is based upon -
>  Set qry = mobjAccess.CurrentDb.QueryDefs("qryPOST_REPORT_DATUM")
>
> '--- loop through the queries parameters --
>  If qry.Parameters.Count > 0 then
>  Response.write "<form action=""report.asp"" method=""post"">"
>
>    For Each param In qry.Parameters
>
> '--- to print the report make sure we have all parameters filled in --
>      If request(param.name) <> empty then
> param.Value = request(param.name)
>     Else
>
> '--- we have an empty parameter do not run report --
> blnRunReport = False
> response.write "ja echt?"
>      End if
>
> '--- provide the text fields for parameter input --
>    Response.write param.name & " <input type=text name=""" &_
> param.name & """ value=""" & request(param.name) & """>" & "<BR>"
>    Next
>  Else
>    Response.write "No Parameters Found for this Query:" & qry.Name &
> "<BR>"
>  End if
>
>      qry.Execute
>
> '--- export the report to the selected format --
>    MyDoCmd.OutputTo 3, strReportName, "Rich Text Format (*.rtf)",
> PATH & "\www\reports\report.rtf"
> '--- Close the Query --
>  qry.Close
>
> '--- close the report --
>  MyDoCmd.Close 3, strReportName, 2 '2=saveno 3=Report
>
> '--- clean up objects used --
>
> It goes wrong at the qry.Execute
> I know that you can't execute a select query, and need a recordset for
> that.
> I just want to get rid of the popup dialogs for filling in the
> parameters.
>
> Any ideas?