Results 1 to 5 of 5
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Lightweight Report Options? (2000/any)

    Our Access guru recently created a database that generates a bunch of highly customized Access reports, programmed through Macros. Due to the large back-end, it takes 7 seconds to open the database, and 30 seconds or more to churn through the dozens of megabytes of data in the linked tables to generate even one report. Now, home access has been requested, and I have been thinking about how to provide reasonable performance over a DSL (384 kbps connection) or cable connection.

    Since I cannot change the back end from Jet to MSDE, the obvious approach appears to be to have all the processing done by a web server running ADO against the DB. My problem is that we don't have the resources to actually code the reports as web pages. Instead, I am thinking along the following lines:

    (1) ASP code makes the connection to the DB
    (2) ASP code runs numerous "stored procedures" (Access queries) culminating in a Make Table query
    (3) ASP code runs OutputTo to generate a downloadable facsimile of the Access report, such as a Snapshot or RTF file
    (4) ASP returns a page that gives a link to the freshly minted file

    My confusion is as to step 3. I created a function in the database that creates a Snapshot with the file name of my choice, but I don't know how to run it from the ASP page. Things that don't seem to work for me include:

    (1) Using the function call as the .CommandText
    (2) Putting the function call in a select query. Running the query interactively in Access yields the interesting run-time error "you can't carry out this action at the present time" on the critical line.

    Do I need to install Access or a run-time on the web server and use Automation? TIA for all suggestions.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Lightweight Report Options? (2000/any)

    I'm afraid I can't help you in your specific request, but I can throw a couple of other alternatives at you.

    First of all, as to the time it takes to open database and run reports. Is the frontend database local to each workstation? Secondly, have you established indexes on all fields that are likely to be part of a report grouping or selection criteria?

    As to home access to the database. Have you thought of using Windows Terminal Services (available with NT or Win2000) or of using a Citrix server. Both of the solutions set up individual host sessions, so a person making a connection is running Access on the host server and only getting screen updates transmitted back to them.

    BTW, even if you could convert to MSDE, it has a max of something like 5 concurrent users, so I don't think you would want to use it. It really is there as the first step towards converting to SQL server.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Lightweight Report Options? (2000/any)

    > Is the frontend database local to each workstation?
    No, it changes frequently enough and is used infrequently enough that it lives on a file server.

    > Secondly, have you established indexes on all fields that are likely to be part of a report grouping or selection criteria?
    I have no idea. Would these indexes need to be in the front-end DB or in the DB containing the tables to which the front end links? The latter is part of the accounting system and must remain inviolate, so doing anything in there is out of the question.

    Although I am not very familar with Citrix, I don't think we can get a new server into the budget right now...but I'll keep it in mind for the future. Thanks.

    > BTW, even if you could convert to MSDE, it has a max of something like 5 concurrent users
    This is not enforced as a license limitation; MS states that MSDE is "optimized" for 5 or fewer concurrent users. Compared with Jet, I bet the occasional 6th or 7th concurrent user still would have a better experience. But we will find out next Spring when the accounting system converts.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Lightweight Report Options? (2000/any)

    > Is the frontend database local to each workstation?
    No, it changes frequently enough and is used infrequently enough that it lives on a file server.

    This explains why it takes so long to open the database, and it will never get better. I'd suggest moving to the frontend if this is an issue. For my clients, I create a simple .bat file that copies the frontend from the server to the local drive. I then put this in each users Startup folder, so it executes each time they start their machine.

    ---------------------------------------------------------------
    > Secondly, have you established indexes on all fields that are likely to be part of a report grouping or selection criteria?
    I have no idea

    The indexes are part of the table configuration on the backend. The general rule of thumb is that you need to index every field that routinely is used as a selection critieria for a query or a sort sequence for a report. So if you are routinely creating mailing labels in zip code order to get bulk mailing rates, you should have an index on zipcode. You have to determine what is "routinely used" for your situation.
    -----------------------------------------
    > BTW, even if you could convert to MSDE, it has a max of something like 5 concurrent users
    This is not enforced as a license limitation; MS states that MSDE is "optimized" for 5 or fewer concurrent users.


    From your description, I just assumed you had considerable morely more than just 5 or 6 users.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Lightweight Report Options? (2000/any)

    I guess I'm blind... MS has a KB article on generating a Snapshot with ASP: HOWTO: Use ASP to Create an Access Report Snapshot and Display it on a Web Page (Q259805). What is most interesting is this admonition:
    <hr>The following steps describe how to use Active Server Pages (ASP) to create a snapshot file on the server, and then display it in the Snapshot Viewer ActiveX control on a Web page. In this sample, the snapshot file is created by automating Microsoft Access on the server. It is important to note that there are limitations and caveats to server-side Automation of any Office application.

    Therefore, before investing in a solution that uses server-side Automation of Microsoft Access, you should read the following article in the Microsoft Knowledge Base:

    Q257757 INFO: Automation of Office from an NT Service Is Not Recommended or Supported<hr>
    But, but, but... I wonder whether Microsoft's position is changing with Office .Net?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •