Report Queries Back End vs Front End

CPGDeveloper

Board Regular
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
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
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top