Report Queries Back End vs Front End

CPGDeveloper

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

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
160
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
122
Thanks for the suggestions guys -- I do a version of both of your ideas -- appreciate the feedback.
 

Forum statistics

Threads
1,084,737
Messages
5,379,519
Members
401,610
Latest member
MaBend

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top