Viewing Access Reports in ASP

Hey Fella's,

Gotta bit of an issue and I know several ways to fix it, but need some opinions. I have an Access 97 DB that my company uses. I have made it where they can view the Reports that I wrote in Access on the web. Basically this is what it does. They Login, get taken to the correct page, enter in the type of Report They want, the Critieria for the Report and so forth. At this point they submit the report and this is where my issue resides.

Here is what I've done so far.

First I created an ActiveX Control with the Snap Shot Viewer inside it. In This ActiveX Control I open access (Its Hidden), open the report with the Criteria I passed, then output the report in Snapshot format into a directory for just that user. After the output is successful i view it with the Snap Viewer. When the control closes it kills the file.
Ok this works great, havent had any problems with it yet.

but my problem is this..what if say 10 people were on the site doing report? We all know how much of a hog Access is. To open 10 different instances of Access is crazy. But i need to open Access to view the reports to output. But i was thinking about exporting all the reports to Crystal and doing it that way but I have almost 300 Reports to export..i really dont wanna go there.

so to put a handle on this i was needing different ways to run it.

Would a Windows Service for this be good? I am in the process as I type this in creating one that would work. I am passing parameters to an .xml file and it would be monitored by the Service and start the process on change..BUT Access would still need to be ran..

So fellow guru's.any suggestions? Crystal reports is about my best bet I think..The Data thats in the Access DB right now is going to be exported to SQL soon, when we get the server in. But even with Crystal I will have problems because most of the 300 reports have the data source inside the text box's that are on the form (ex. '=[Field1') rather than in the code and Crystal Reports wont read reports like that..at least that im aware of.

Thanks guys. Hope someone will some great insight for me on this.

Steve

Hello again Everyone,

Just thought id pass along a 'fix' for that issue above. I've seen people have viewed this but didnt reply, my guess is they dont know how to get around this problem. We'll I seem to have ran acrossed a Third Party control that helped me. Its ComponentOne's Report control. Its really easy to do and best of all it takes your access reports, puts the fields and everything in an XML document. With that XML document it tells the Report Viewer where to place the labels, textbox's, pictures and also the RecordSource or SQL String and Database connections and allows it to query the Access Database and populate the Nodes in the XML DOcument then the Report Control formats the page the exact way Access does. Coding this was actually 10x's easier than my custom ActiveX control i had done. PLUS, to top it off..Access Never has to be run again to see the reports once the first import of Access Reports were done into Report Design Viewer.

Kudos to ComponentOne for doing such a great job. The Suite was a big purchase but in the long run it has already paid for itsself.

Peace fella's

UnSkiLd

Hey UnSkiLd,

I'm pretty much in the same boat. We have a database that uses Access as the front end and SQL Server as the back end. My task is to allow users to access the reports from the web.

We have a lot of filters on the reports before the actually output. So what I did so far is to save the output as an .snp file and then embed the activex control on a web page and view the .snp file on the web. but that doesn't do the job. When the users view a report in Access they can actually choose the filters before printing it...however, with .snp i do not know how to add those filters...do i have to manually program that using asp? If so, then I have to create a new report using asp/html.

I read your note about ComponentOne's Report control and Crystal...how can those tools solve this problem.

Thanks

Very good question smile_bhai.

The way I got around the filters you would place is actually pretty nifty, but still requires that Access be open on the server each time someone wants to view a report, not very efficient. The way our reports are ran is this:

1. a form is open in the Access Database.
2. Choose the Start Date and End Date
3. print report (could be more criteria depending on the report - but for example ill just use this)

So this is how I went about it when I wanted to view the information on the Web.

Say the user wants to see his billing info for all of November

1. He would choose the 'Billing Report' option on the website. Two text box's would appear for start date and end date.
2. After the submit is pressed Access 97 is opened on the server side. The server side Code will have all the criteria you would need to do the report - ReportName, FormName, StartDate, EndDate.

On the Server Side:
Open Access Database
Do a DoCmd.Open FormName (which would be BillingReport)
Do a Focus on the StartDate text box in the Form
Populate StartDate based on the Date the user inserted
Do a Focus on the EndDate text box in the Form
Populate EndDate based on the Date the user inserted

Once the report comes up - Export it into a snapfile, or pdf.

Ok, I forgot to tell you this, when you export you need to have a unique filename and a set folder to place these in. Think of this ahead of time. Filenames are unique based off of Username + Hour + Min + Second. using this formula it will never be duped.

In the Server Side Code where you specify the Export Command to Access, just specify the unique filename and the designated folder you want.

Once its in this folder and the Report is fully exported Go to the page where the SnapViewer is on, set the filename of the SnapViewer to the Folder and Filename that you just exported, use the Session() to get this info.


Ok, hope this helps alittle, I appologize for rambling. I am still trying to get this easier and when I do i'll post it. But the above is how you get around the filter problem. That i've found anyway.

As far as ComponentOne and Crystal - If you didnt want to remake all your reports from Access to something else, I choose ComponentOne. It converts all your Access Reports into an XML file and it has its own little viewer that parses the data and views the reports.

However, I still have problems doing complex formulas. And im working this out now. So its not perfect but it beats rewritting over 300 reports. And the best part..Access 97 doesnt have to be running in the background.

have a great day. Let me know if this helps or not, I'll try again.

UnSkiLd

This may be late but IU shall shed some of my aggravating and excruciating pains with access.

I have a web site that displays reports from access in PDF. First crystal reports with .NET sucks the big one, dont even go there.

Second, you cannot automate access within a service. Its impossible due to the way they designed access. although you might be able to abuse remoting to get another instance to turn it on, which would work if it runs in its own process.

The web site: I create a remoting object that houses access. A web page calls my web service. The service then instantiates a remote object that runs with singleton and queue patterns. I then use DoCmd to open and print the report to a pdfdriver. www.pdf995.com. Then I open the file as a byte array for serialization and return it. Its real easy to display that on the web.

I made it simple but its aggravating, access is finicky and an absolute poor job by microsoft. I even think the word automation server is more stable. There are tons of bugs. You must have the access instance visible in order to manipulate reports. And worse I also tried moving it to a service but it bombed every time. I think it might be do to a threading model imposed.

well if you need help
nick

Can you explain your solution in more detail? It sounds like exactly where I need to go!

From a HMTL web page, I need to display an Access 2000 report that is based on a query which needs to use a variable on the HMTL web page as criteria, preferably without making Access visible to the end-user.

UnSkiLd, Just needed slightly more help on your posted reply below. I am working a similar issue and tested your solution below only to get an "OLEserver not installed" error when I manually followed your steps below with MS Access.

When I get to OfficeXP I plan to use XML Web Reports, however I need to have a work around while still using Office 97.

Tell me how you code for Saving or exporting the executed report as a snapfile or pdf...when I tried this within Access I got the error that something was not installed on the server. Please let me know of anything that needs to be running on the server for your solution to work.

Thanks,