ms access reports to web

[Shawn Anderson]

I have a customer that brought me a MS Access program that he wrote and wanted it moved to web based. His program consists of a single, large data entry screen that connects to about 9 tables and about two dozen fairly complex reports.

I decided for now to just continue to use his MDB file. Using PBCC and the Ajax approach, I've redone his data entry page.

I'm now at his reports. I really don't want to have to re-write all of his reports from scratch to make them display on a web page. What I'd like to do is somehow call his reports from my PBCC program, or even a command line program triggered by my PBCC program on my web server to create a report and return it to HTML or ideally, a PDF file.

Is there some other/better way that I can re-use his report designs?
Is there some existing tool that will do this?

[Joe Byrne]

Shawn,

Off the top of my head, I would think it'd possible to create a VBA script to generate the reports. Then you could pre-define the output to a PDF print driver. The tricky part (beside timing) would probably be defining the print-output name so you could find it. Once the PDF report was generated, you could do a fairly simply web page that did an immediate redirect to the PDF file name. Assuming the viewer has a default PDF reader installed, the report would be displayed in the web browser.

[Shawn Anderson]

Thanks Joe. I have some questions:
Would the script have to be done in the Access file?
How could that be triggered externally?
Would access need to be installed on the web server?

If all that could be done and I could force the PDF output to someplace I specify, the rest would be cake.

I found this http://www.acc-technology.com/
which is close and this: http://www.christiansteven.com/main_mars.htm
which I think is exactly what I need... for $1000!!

I'm kicking around trying to do it in VB6 and calling the VB program from PBCC.

[Joe Byrne]

Shawn,

I only dabbled with VBA for a short while, but I seem to remember that as long as office was installed, you could call a VBA script from the command line. I think you can download the VBA runtimes separately from Office too. I'm also pretty sure (99%) that Access has to be installed on the web server since parts of the script will make calls to the Access runtime.

Heck with the $1,000.00 Get the enterprise edition for $8,000 +

[Fred Harris]

What I'd like to do is somehow call his reports from my PBCC program, or even a command line program triggered by my PBCC program on my web server to create a report and return it to HTML or ideally, a PDF file.

This is really just off the top of my head too Shawn, but I think you could 'drive' Access through PB's COM facilities and, (just guessing) these routines should directly make accessable in the COM calls the result from contained reports. I've never used Access that way with COM, but perhaps someone else here has. I know it would eat up a lot of time figuring it out.

[Knuth Konrad]

Fred, that would mean that he has to install a (licensed) copy of Access on the webserver. And each call to the page would load another Access instance into memory.

AFAIK, VBA Makros inside a MDB are not accessable from "the outside" without the involvment of Access itself.

Just had that today: Created a VBA function, used that in a query. Worked like a charm from within Access, but gave me an error when called from an ASP page via ADO.

[Michael Mattias]

Just a thought, but aren't there already some commercial products to generate reports from a database (eg Hummingbird, Crystal Reports), and might not one or more of those produce "web ready" (HTML?) output with minimal effort required to convert the VBA scripts?

[Fred Harris]

Knuth,

My guess is that you have probably forgotten about ten times more about web based programming than I ever knew (I'm basically a Windows programmer), but wasn't Shawn talking about some sort of CGI program that interacted with the 'outside world', and his CGI program - running on the server - accessing an Access *.mdb file also on the server? In other words, the user clicks a button which is eventually posted back to the CGI program, then the CGI program connects to Access as a COM object (not through Ado but actually driving Access as the COM object it is), and in that way executing COM calls that would generate the already made reports Shawn alluded to, then through COM the results of the reports would be made known to the COM client (Shawn's CGI program), which could send the data back to the end user's brouser? Please forgive me if I'm being really dumb here, as I'm just beginning to learn internet style programming!

Fred

[Shawn Anderson]

Fred's description pretty close to doing what I need.

Michael:
I have the server version of Crystal Reports, which would be great but I don't think I can use the pre-existing Access reports and port them to Crystal. If I could, that would be the best deal. I did find this: http://www.microtools.us/accesstocr1.aspx
It would be worth $149 to me if that works.

I don't have a problem putting Access on the Server if that saves me from re-doing all the reports, but I'm thinking that that won't solve my problem. The calls to Access would only be for the reports. The actual data input/output is done with PBCC and SqlTools.

I know what I want can be done (from the MARS software example) but I don't know if I have the expertise to make it happen. I've never done COM before.

I think VB6 can natively run Access reports. I found this: http://visualbasic.ittoolbox.com/gro...ing-vb-1657772
I might play with that. I could call a VB program to run the report but I'm afraid it just shows it on the local computer with no ability to save it to a file or STDOUT it back to the web.

Thanks all.

[Brad D Byrne]

what if you were to simply convert the access files to MySQL?

google search "convert Access to MySQL" gives 300k hits,

a tutorial,

How To Convert Access or Excel to MySQL

http://www.webthang.co.uk/tuts/tuts_...rt/convert.asp


MyDbConverter(access to mysql converter) 2.0

http://www.gold-software.com/download8264.html

[Shawn Anderson]

I could convert but that wouldn't help my problem. I want to NOT have to redo all the reports, which I would still have to do if I moved to a SQL server.

I'm investigating automatically converting just the reports to crystal (via 3rd party software).

[Fred Harris]

what if you were to simply convert the access files to MySQL?

One can certainly put data in any DB one has access to, and wizard type interfaces certainly can aid the transfer, but I think the point of Shawn's post was whether it was possible to just simply use already existing/functioning code as part of an Access *.mdb file and get access to that and its results from some other application such as Asp.net, classic Asp, PowerBASIC CGI, etc. I just dumped the interfaces using the COM Brouser for...

' ------------------------------------------------------------
' Library Name:      Microsoft Access 9.0 Object Library
' Library File:      C:\Program Files\Microsoft Office\Office\MSACC9.OLB
' ------------------------------------------------------------

...and the myriads of interfaces come to over 4500 lines of data. Its my guess that just about anything you can do with you mouse/keyboard while sitting in front of an open *.mdb file containing tables, queries, modules, reports, etc., can be done using COM driven code from PowerBASIC. I can't recall ever seeing any demos in Source Code for that. I suppose the code would be highly dependent on the particular report. However, I'm pretty sure it could be done. If a person had some ongoing need for that sort of thing it would probably be worthwhile figuring out how to 'cookbook' it.

What got me thinking about it is I remember when PB 7 first came out, I connected to a Microsoft Access database and was able to read data out of tables and so on. And I don't just mean using ADO, but rather 'driving' Access itself as a COM object. I never really persued it further cuz I never had any need for anything like that, as I use ODBC direct for all my DB access and I don't use Access's report making facilities.

[Paul Franks]

Here's a way to print Access reports to PDF, with automation interface (vbscript, which can easily be converted to PB). It uses RedMon and GhostScript for the PDF conversion bit, but I should think any of the free PDF "printers" would do as well, such as tinypdf

http://www.thescripts.com/forum/thread204362.html

FWIW, there are probably dozens of technical approaches to this problem, but you will run into legal limitations depending on how you approach it. I don't think M$'s license will allow you to just plunk a full copy of Access on a web server. If you generate the PDFs periodically on a desktop, using a scheduler, then move them to the web server, you should be OK.

[Knuth Konrad]

I think VB6 can natively run Access reports. I found this: http://visualbasic.ittoolbox.com/gro...ing-vb-1657772
I might play with that.

No it can't. Same way as descibed above. VB creates an instance of Access (COM object) and remote controls it. This can be done with PB as well, which again leaves you with the need to have Access installed on that machine.

You can't access Access reports without the involvment of Access itself. ADO doesn't do the job (why should it, after all, it's meant to be a generic database layer). In the short run, using the Access object to get hold of the reports might be the easiest/fastest solution. But my bet is that in the long run it hinders further development.

I'd really go the way to replace the Access reports with something more generic (HTML).

[Shawn Anderson]

Knuth,
thanks for setting me straight before I spent a lot of time on that.
I wonder how these guys do it?:

http://www.christiansteven.com/main_mars_alt.htm

My plan now is to convert the access reports to crystal reports (there are several 3rd party products that do that) then use a command line tool to run the reports on demand.

http://www.crystaldesk.com/CrystalCommand.htm

This will cost (my customer) a few hundred dollars in extra software but still cheaper than time to manually convert everything.

[John Reinking]

Don't discount the MS Access Snapshot Viewer. You will need at least the MS Access runtime installed, which is 100% free, and a macro, or automation script, to create a .SNP file of your Access report. Embed MS Access Snapshot reports into HTML.
This link refers to MS Access 97, but the technique should work with any current Access version.

[Shawn Anderson]

I looked at snapshot viewer, but didn't know how to run it on demand.
You link shows how to do just that in a batch file. I'll still have to have Access installed on the sever, which I guess is OK...

[Joe Byrne]

I'll still have to have Access installed on the sever, which I guess is OK...

You're going to have to install Access someplace that is accessible from your web server anyway. There is no getting around that part.

[Shawn Anderson]

John, I think that idea will work for me.

I just have one problem now... I've created a macro in access to output the snp file to a specific folder, and that all works. My problem is that I can't get the macro to run using the /x command line option. I'm sure my syntax is correct. I believe the problem is that there is a startup form that runs automatically when the database is opened. No matter what I do, I can't turn that feature "off" (I remember now why I hated access).

I click tools/startup and choose NONE for display form/page.
I click OK and that box closes.
I click tools/startup again and it is still in there.

What's up with that?

[Shawn Anderson]

ok, I just did a test mdb and for some reason using /x to run a macro from the command line isn't working.

here is my batch file:
start /wait "C:\Program Files\Microsoft Office\Office10\msaccess.exe" "c:\html\agview\scoutingContracts07.mdb" /X "webCompleteFieldList"

it opens the database ok but the macro never runs. If I name the macro autoexec it does work.