Drew Wutka
DWUTKA at marlow.com
Tue Sep 23 21:56:48 CDT 2003
Quick question on the reports. Do they prompt for criteria through query expressions, have no criteria, use code for criteria, or do they get criteria from forms? I ask this, because I designed the WolfWeb on my own time, to make running Access reports from the web a MUCH easier job. When designing it, I had a database we have in work, in mind. It's called Glovia Reports, and has lots of reports (about 900), which were developed by multiple developers. They all used Expressions in their querries to ask the users for criteria. I was asked to make several of these reports 'web based', and I did so for a handful of them. Making them web based took anywhere from 2 to 8 hours. The ones that just 'ran', I was able to just write a little bit of code, modify the page to provide a link to run the report. But several of them asked the users for criteria. The problem was, since those querries prompted for criteria, the calling thread would 'pause' while the prompt was displayed. Not good for an ASP/VB .dll, that couldn't 'see' the prompt. So for the few 'web based' reports I converted, I modified the querries (which was a complete pain, the reports usually had a MINIMUM of 5 or 6 querries behind it (one query using the next, and so on.). I changed the querries to get their criteria from public functions that retrieve global variables. Then I made a public function that would 'set' those global variables. Then I went and developed the ASP pages that would ask for the appropriate criteria (and a report format), then when the user clicked the run report button, the VB .dll would open the database, set the globals from the user's input, and then kick back the report. While driving home from work one night, I thought to myself, 'Self, there has got to be a way to run all of those reports directly from Access, without having to convert them....'. Then lightning struck. A multi-threaded VB application, that ran the Access database in one thread, and watched for criteria in another. Sure enough, I did it. Works great too. Right now, we are running 2 databases, for reports (the second one only has about 100 reports), through my WolfWeb project. (I had my agreement with work modified so I could work on it for my own benefit (along with doing contract work on the side, cause technically my original agreement set my work to own everything I coded.....ack), as long as they got copies of what I did for free.). It's been running for about a year now (maybe 9 or 10 months, I think I put it in place here last December), and the only issue is something I already have solved, but haven't bothered to put in place. Here's how it works. The user clicks the link for the database they want to run a report for. Then they get a combo box with a list of all the reports in the database, along with a combo box to select the format they want the report outputted too. Once they make their selections, and click the button, the system goes and starts running the report. Whenever a prompt appears, the end user gets an HTML representation of that prompt (which is pretty dead-on, but it uses HTML Styling...which requires IE 4.0 and up (not sure what version of Netscape is required). They fill in the HTML based dialog boxes (just as they would in Access), click the Okay, or whatever button, and that interaction is then mirrored to the Access database. It handles every sort of dialog box too. Errors, ODBC logins (and it creates password masks for fields that require it), etc. I even announced on here when it was ready, and setup a demo on my site, until someone said to setup Northwind as a demo. Then the walls came crashing in. Northwind uses forms to ask for criteria. Guess what, Access forms don't act like windows....NOT EVEN CLOSE. It's actually pretty odd how they work. The Access form itself has an hWnd, a few to be exact. There is a separate window for a few things, like subforms, headers, footes, etc. The really odd part, though, is that the controls are NOT separate windows....at least not all of them. The active control, and ONLY the active controls shows up as it's own window (with an hWnd). When the focus moves to another control, that window is now the new control, that has focus. It's just plain bizarre. So, since Northwind uses forms for criteria, I stopped working on the WolfWeb. Then another project hit my lap. (I know this is a long thread, but I like telling this...grin). It was for a buddy of mine, that I do various development stuff for. His name is Ken. Ken had a company that wanted a form from one of his databases, on the web. It was a decent form, about 30 to 40 controls, and they wanted the web just like his form (or so I thought.). I had already started working on handling Access forms with the WolfWeb, so I grabbed that code, and finished it for his little project. It wasn't setup directly to work in wolf web, but what it did, was create an exact (well relatively exact) HTML version of the form. When the form opened, it saved the HTML version as a text file. Worked pretty good. But apparently, the company that wanted it didn't have money for development at the time, so the project was put on hold (that was over a year ago). Then shortly after my apartment fire, they told Ken they had the green light, and were getting the PO approved. Unfortunately, I was still in the process of getting settled into my new place, so I ended up putting them on hold for a while. When things finally settled down, I blew the dust off that code, and created the HTML form. It had to be tweakded though, because Ken's form had combo boxes with a MASSIVE amount of data behind them (getting close the the 64k limit of them). It took 30 seconds to download the page at 384k. Not good. So we came up with some 'filters' that the users would select before the page was loaded, that vastly reduced the amount of combobox data we were dealing with. Then the kicker came. It turned out that they actually had a 'written' form, which looked NOTHING like Ken's form. Ken had put their written form in .pdf format, and THAT is what they wanted to use. Go figure. The 'new' form actually was pretty easy to replicate with just HTML tables, so I scrapped the Access form, and built the entire thing with just plain old HTML. It actually turned out WAY better then the Access form, because I didn't have to use Styling to force everything in place, I was able to use HTML tables, so everything just fit into place naturally. (Ken told me when they say the new look, their jaws dropped.....just the kind of thing a developer likes to hear! <grin>). Anyhow, I told you all that, because the WolfWeb is just a hair's breadth from being about to display (and allow interaction) between a web user and an Access database. It's not completely perfect. I would have to create something to handle tab controls, and images, but as of right now, I can easily hand normal colors, text boxes, labels, combo boxes, listboxes, checkboxes, radio buttons, and command buttons. So, if this sounds interesting to you, let me know. We can talk about getting you a working version of WolfWeb (I'm very flexible, as Mike Mattys...grin), I'd be glad to help out with web development if you need it. But if the capabilities of the WolfWeb sound like it might give you a jump start, feel free to give me a holler. Drew -----Original Message----- From: David Emerson [mailto:davide at dalyn.co.nz] Sent: Tuesday, September 23, 2003 5:44 PM To: accessd at databaseadvisors.com Subject: [AccessD] Web based Project Group, Over the past 3 years we have developed a database in Access 97 that basically accepts user input, runs some basic calculations and prints reports. There are about 50 screens and 230 reports. The users (about 20 - could rise to about 100 in two years) are all on stand alone machines spread throughout the USA. They are the only ones that would normally access their client records. Currently there are about 5,000 client records in total but this could rise in the next two years to 50,000. My client now wants to put it onto the web so that updates can be done easier and in one place (rather than needing to be distributed each time). Users can then access it through the Internet, add/change their client data and run off their reports. This is not a big corporate so budget is an issue. The client also doesn't have an IT department so the maintenance of any web server would be outsourced. I only have experience in Access (97/XP) and SQL2000. I have not done anything with web based databases. My questions - 1) What options for an interface do I have using an Access or SQL back end? 2) Is there another option for the BE that might be better? Regards David Emerson DALYN Software Ltd 25b Cunliffe St, Johnsonville Wellington, New Zealand Ph/Fax (877) 456-1205 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com