Report Queries Back End vs Front End

[CPGDeveloper]

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]

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.

 

[petertenthije]

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]

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

 

[rpmazur]

I know I am extremely late to this game....lol. I found this while looking for another answer and thought I'd chime in.

I usually create my front end also on the network but whenever I am referencing things on the network, I use the universal naming convention (UNC). When I link my tables I use \\Server1\Directory\SubDirectory\database_be when linking the tables instead of using a mappped drive letter.

I will put my front end access database in another directory that is also accessible to the end user, but separate so they can't inadvertably mess with the raw data without going through the BE.

I will also create a shortcut that uses the UNC properties for the location (again, this avoids having to use mapped drives that may or may not exist). I then send that shortcut to the end user.

This also eliminates having to make sure you send the updated FE to all users. You can make a copy of the FE for development and testing purposes (and using the UNC associations allow you to still access the BE). Then just copy the updated forms, queries and reports to the production FE and paste them.

 

[stumac]

I know I am extremely late to this game....lol. I found this while looking for another answer and thought I'd chime in.

I usually create my front end also on the network but whenever I am referencing things on the network, I use the universal naming convention (UNC). When I link my tables I use \\Server1\Directory\SubDirectory\database_be when linking the tables instead of using a mappped drive letter.

I will put my front end access database in another directory that is also accessible to the end user, but separate so they can't inadvertably mess with the raw data without going through the BE.

I will also create a shortcut that uses the UNC properties for the location (again, this avoids having to use mapped drives that may or may not exist). I then send that shortcut to the end user.

This also eliminates having to make sure you send the updated FE to all users. You can make a copy of the FE for development and testing purposes (and using the UNC associations allow you to still access the BE). Then just copy the updated forms, queries and reports to the production FE and paste them.

It sounds like your users all use the same copy of the FE, this isn't recommended and can cause various problems - the worst being corrupting the data in the BE. A common version of this is to give the user access to a shortcut that runs a batch file which copies the FE locally and then opens it every time. This can be bypassed if the user opens access first and opens the application from the recent files listed. That's why I have a check on opening of the FE that it's the latest version the user is opening.

I agree with always using UNC v mapped drive letter, although I remember issues with this in certain VBA scenariosany years ago.

 

[rpmazur]

It sounds like your users all use the same copy of the FE, this isn't recommended and can cause various problems - the worst being corrupting the data in the BE. A common version of this is to give the user access to a shortcut that runs a batch file which copies the FE locally and then opens it every time. This can be bypassed if the user opens access first and opens the application from the recent files listed. That's why I have a check on opening of the FE that it's the latest version the user is opening.

I agree with always using UNC v mapped drive letter, although I remember issues with this in certain VBA scenariosany years ago.

Stumac - I hear what you are saying with corruption, and although it's been a while and don't remember exactly where to go, I did set BE to exclusive mode (I believe is the term) which locks the database from other users. My understanding of that mode was that two people could not access the same record at the same time.

Sadly, I am not as sophisticated as you are with my applications in using VBA, I mainly create macros either "globablly" within the FE itself or specific to the form or report that I am using.

If I am wrong, please enlighten me as I am always looking to further my knowledge and I also have to say (knocking on wood) I haven't had corruption......yet....lol