Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    130

    Unanswered: Accessing Reports in a different mdb

    WinXP A2K2. BE mdb 200 tables. FE mde 300 queries, 200 reports, 250 forms lots of vba. 5-10 users. Server Win2003. Works just fine. Periodically I add new application functionality to FE, post FE mde to server and existing FE updates itself. No problems. Want to add functionality where user can create their own A2K2 Reports. Problem will become when I update FE, their
    reports will disappear (obviously). Is there a way to link to reports in an mdb on the server. Any other way to accomplish this? If the question is unclear, I will explain further.
    thanks
    Winston

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what do you mean by user-defined reports?
    are we talking user-defined report layouts, or just user-defined selection criteria.

    selection criteria are simple:
    make a userprefs.mdb on all clients, link to it from the frontend.mde.
    store SQL in a table in userprefs
    update querydef.SQL in a saved query with the SQL from userprefs.
    their (fixed layout!) report looks at the (updated .SQL) saved query.
    ...and your front-end updates don't touch userprefs.
    each user has custom selection criteria whenever they want.
    if you are enforcing an install path there is nothing more do do, but if the users have the luxury of selecting where to install you will need to relink from code.

    if you plan to let the users run riot with query designer it becomes more difficult for them and for you. it would be nice for each user to link to a .mde on their own local machine containing only their own home-made reports. unfortunately, reality (at least A2K reality) is not like that: you can't link to reports in an external .MDB but you can still import ...import from code?? - i never tried code import of reports.

    the middle road (which i use): several different saved report layouts fed from their own saved queries (all in the front-end); unlimited number of user-defined selection criteria in userprefs with a field to find which query the SQL belongs to.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2004
    Posts
    130
    Yes, you are correct, the user defined query in the backend is fairley easy using stored sql. Not a big deal. The desire is for the user to make their own report layout and retain it when I update the FE. My only thought is as follows:
    1) User defines report within their copy of the FE.
    2) When user saves report, in VBA I export report def to server .mdb on the server. When they get new mdb, I import all reports from server .mdb into FE mdb.
    Only problem is that I use .mde as FE and user can't define report in .mde. Any suggestions around this issue?
    thanks,
    Winston

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry: never tried to persuade users to play with report design.

    random thoughts:

    a separate dedicated .mdb report deisgner, preferably looking at a read-only clone of the data ...save reportdef & querydef in the (local - see "last") .mdb and code-import both back to the .mde front-end (switching the query back to looking at live data at the same time).

    can the users survive with just query design -- you can "help" them with a query-design form to make sure the query is not updateable or pump the query return straight into XLS for collateral-damage-free decoration??

    last: why put custom user-defined reports on the server... they are probably client specific so maybe they belong on the client.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2004
    Posts
    130
    Thats a reasonable solution. Thanks. I don't need the users to play with queries. Have created a sql generator... select this but not that... works just fine - users like it. Can select which report layout after selecting subset of data. Your caution about non select queries was good. Principle is that user will create a report layout, flag it as user designed by a specific user and then import into mde for use within normal operations. Every time user selects their own reports it will import from server based mdb. Won't put it in BE mdb with tables, but rather another BE mdb specifically to reduce potential for corruption.
    thanks again,
    Winston

Posting Permissions

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