473,241 Members | 1,545 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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_DAT UM")

'--- 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?
Jul 22 '05 #1
1 2913
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" <jo**********@groupsupport.com> wrote in message
news:ac**************************@posting.google.c om...
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_DAT UM")

'--- 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?

Jul 22 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Teri Welch | last post by:
Hello, We maintain a VB6 front-end application using an Access 2000 database. All code and forms are in VB6. The program also uses several queries/reports defined in Access. For corporate...
3
by: Gheaci Maschl | last post by:
Hi all! I would like to have your opinion about my problem and my proposal how to solve it: Ingredients: - BTriev database - Crystal Reports - maybe MS Access - Liinos6 (small ERP software)
7
by: dog | last post by:
I've seen plenty of articles on this topic but none of them have been able to solve my problem. I am working with an Access 97 database on an NT4.0 machine, which has many Access reports. I...
11
by: Grasshopper | last post by:
Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on...
16
by: cyranoVR | last post by:
This is the approach I used to automate printing of Microsoft Access reports to PDF format i.e. unattended and without annoying "Save As..." dialogs, and - more importantly - without having to use...
6
by: Bob Alston | last post by:
I am looking for Access reporting add-in that would be easy to use by end users. My key focus is on selection criteria. I am very happy with the Access report writer capabilities. As far as...
13
by: salad | last post by:
Hi Guys: I was stuck. I needed to send a report to a file. My beautiful report(s) in Access were going to require loss of formatting with RTFs, a PITA in WordMailMerge, sending it as a text...
3
by: Parasyke | last post by:
I see a lot of jobs advertized for people knowing Crystal Reports. I tried to use this platform 6 years ago and found it awkward and difficult. I find Access, using ODBC, to grab data and build...
16
by: JoeW | last post by:
I'm utilizing a database that I created within MS Access within a program I've created in VB.NET. I am using the VB front end to navigate the information, but want to be able to print a report,...
0
by: Mark Gold | last post by:
Hi! We have a VB application using Crystal Reports 6 that has worked successfully on hundreds of systems for over 10 years. Now, on one network, the application and access database does not close....
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.