Report Queries Back End vs Front End

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
132
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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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:

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
207
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.
 

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
132
Thanks for the suggestions guys -- I do a version of both of your ideas -- appreciate the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,304
Messages
5,486,070
Members
407,531
Latest member
WalterR01

This Week's Hot Topics

Top