Results 1 to 4 of 4

Thread: Report Queries Back End vs Front End
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Location
    New York, NY
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Report Queries Back End vs Front End

    Hello All~

    I created and administer an MS Access DB. Back End sits on a Network Drive, 20+ End Users have the Front End on their respective Desktops. The Application has a Reporting Tool where the user has the option of selecting and running about 20 or so different reports.

    Currently I keep the queries for these reports with the front end -- the back end only has the tables. Is there a best practice here? If moved the queries to the back end, I wouldn't have to issue a new front end every time I change or add a report, but I wonder if there are downsides that I'm missing?

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Posts
    459
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Report Queries Back End vs Front End

    Your back end should only contain tables, no queries or reports.

    even if you had the reports in the BE you would need to release a new FE every time in order to link to the the new report.

    In terms of queries, a method i use fairly successfully is to have a table with all my queries in it, on the FE I have combo listing them where the user selects a query and runs it, I have one 'shell' query that I update the definition in VBA and display the results to the user. This generally opens in read only mode (I have a flag on the table allowing them to be read/write) but generally wouldn't give FE users write permission to a query. Happy to share a stripped down version of this for info.
    Last edited by stumac; Oct 7th, 2019 at 12:35 PM.

  3. #3
    Board Regular
    Join Date
    Sep 2012
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Report Queries Back End vs Front End

    Another possible solution would be to add a version check.

    On the backend I have a table with updates. One column with the update description and one column with the update date.
    In the front-end there is also a table in which I place the most recent update date.

    The date in the front-end has to match the newest date on the back-end. If it does not, then the database does not open with the regular form, but it opens on a form that warns the database is out of date. This form also includes instructions from where and how to replace the database.

    The back-end must be copied to a local drive. To make sure everyone does so, I have created a query that checks the database is not running from the shared drive. If the database is opened from the shared drive, then a form will open explaining them to copy the database to their local drive.

  4. #4
    Board Regular
    Join Date
    Oct 2008
    Location
    New York, NY
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Report Queries Back End vs Front End

    Thanks for the suggestions guys -- I do a version of both of your ideas -- appreciate the feedback.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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