Solved How can I restrict the front-end user from editing the queries?

[Khurramshaikh47]

Hi,
I have shared my Microsoft Access database by splitting BE and ACCDE files. The shared ACCDE file is working fine. But I don't want any user to make changes in any Query.

Can anyone guide me on how I can restrict the users of ACCDE files to edit my queries?

Thank you

 

[isladogs]

The only foolproof way is to use SQL in your VBA code instead of saved queries.
However, there is a lot that can be done to make it harder for users to alter your database.
See my article

Improve Access Security 2

This article explains how to apply various security measures to make your databases as secure as possible. An example app is used to illustrate the possible methods available

www.isladogs.co.uk

 

[ebs17]

What reason is there to change queries and thereby endanger the functionality of the application?
A frontend updater could be used to level out changes and slow down the urge to play.

 

[theDBguy]

Furthermore, this may not be a concern, but users can also create new queries in an ACCDE.

 

[Pat Hartman]

Management should instruct the users that damaging company property is a firing offence. And then enforce the rule.

I also use a batch file to open the Access apps which downloads a new version of the FE each time the user opens the app. The app does two things when it opens. I checks that the version matches the FE version and it also checks to see if this copy had been opened before. If not, it updates a table that logs the open and continues to the switchboard. Logging the open prevents the user from reopening his local version.

 

[isladogs]

My main focus for many years was on creating databases for use by school staff (both teaching & non-teaching)
It isn't practical in that kind of environment to fire people for causing unintended harm to database apps

From many years experience, the vast majority of users will have no interest in modifying a database (or the time to do so)
Of the small minority tempted to try, very few of those would try to do so for any malicious or destructive intent.
I found the worst offenders for adding/deleting/editing queries were those users with intermediate skills in Access who just wanted to try something and see if it worked. Sometimes because they thought they could do it better.
When it didn't then work, they were the first to complain.

So we locked down our databases as securely as possible giving users no means of accessing the queries or tables

 

[Khurramshaikh47]

What reason is there to change queries and thereby endanger the functionality of the application?
A frontend updater could be used to level out changes and slow down the urge to play.

How can I get a front-end updater?

 

[ebs17]

AutoUpdate Front End Methods?
Front End Auto Update

Or look for it yourself: There are several solutions.

 

[gemma-the-husky]

It's actually useful sometimes that users can change queries as it adds another way to provide support.

 

[ebs17]

I would say that the developer has done a bad job when users start creating or modifying queries themselves.
If the user is possibly more skilled than the developer, things become critical.

 

[Pat Hartman]

It isn't practical in that kind of environment to fire people for causing unintended harm to database apps

Since we all lock down the databases to some level in order to prevent accidents, "Unintended" does not apply. If they cause damage, they did it by breaking into the db. That shows intent.

If users need to do additional types of analysis, it is easy enough to export data to Excel and allow them to have at it.

 

[Khurramshaikh47]

AutoUpdate Front End Methods?
Front End Auto Update

Or look for it yourself: There are several solutions.

Thank you ebs to guide me.
I am trying this solution.

 

[Pat Hartman]

I use a simple, four line batch file. The batch file is stored on the server with the master copy of the FE. The batch file is run from a shortcut on the user's desktop.

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb

Line 1 makes a directory which allows me to control where the FE is downloaded. No error is raised if the directory already exists so it works the first time when it makes the directory and every other time also.
Line 2 deletes the local copy of the FE. Again, it works whether the FE is present or not
Line 3 copies the new copy of the FE to the specified directory. This will fail if the FE cannot be found
Line 4 opens the local copy of the FE. This will fail if the FE is not present

Just change all the names and use your own.

 

[Khurramshaikh47]

I use a simple, four line batch file. The batch file is stored on the server with the master copy of the FE. The batch file is run from a shortcut on the user's desktop.

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb

Line 1 makes a directory which allows me to control where the FE is downloaded. No error is raised if the directory already exists so it works the first time when it makes the directory and every other time also.
Line 2 deletes the local copy of the FE. Again, it works whether the FE is present or not
Line 3 copies the new copy of the FE to the specified directory. This will fail if the FE cannot be found
Line 4 opens the local copy of the FE. This will fail if the FE is not present

Just change all the names and use your own.

Thank you so much Pat, can you guide me on where should I put this code? in module?

 

[Pat Hartman]

It is a batch file. Open Notepad. Add the four lines and save it with a .bat extension. That makes it an executable so if you have to open it to edit it, either open Notepad first and pick the file or right-click on the file and choose edit. Save the file in the same directory as the master copy of the FE.

Create a shortcut and have it run the .bat file. Then send the shortcut to each user and have them save it on their desktop. If you want it to stand out, add a cool icon to the .bat file

 

[Khurramshaikh47]

It is a batch file. Open Notepad. Add the four lines and save it with a .bat extension. That makes it an executable so if you have to open it to edit it, either open Notepad first and pick the file or right-click on the file and choose edit. Save the file in the same directory as the master copy of the FE.

Create a shortcut and have it run the .bat file. Then send the shortcut to each user and have them save it on their desktop. If you want it to stand out, add a cool icon to the .bat file

Thank you Pat

 

[Khurramshaikh47]

I use a simple, four line batch file. The batch file is stored on the server with the master copy of the FE. The batch file is run from a shortcut on the user's desktop.

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb

Line 1 makes a directory which allows me to control where the FE is downloaded. No error is raised if the directory already exists so it works the first time when it makes the directory and every other time also.
Line 2 deletes the local copy of the FE. Again, it works whether the FE is present or not
Line 3 copies the new copy of the FE to the specified directory. This will fail if the FE cannot be found
Line 4 opens the local copy of the FE. This will fail if the FE is not present

Just change all the names and use your own.

Pat you didn't use ACCDE file extension in any line?
Let me share you the location of my files
Main DB in my Computer:
D:\HR\Employee Database\BTIEmployeeSharedDB\22-Nov-2022 (For Server Sharing)\BTI_Employee_Database_Testing.accdb

Backend File:
\\Book-dc\hr\BTI Employee Database\BTI_Employee_Database_Testing_be.accdb

Frontend File:
\\Book-dc\hr\BTI Employee Database\BTI_Employee_Database_Testing.accde

Please guide me to create bath file for my db.

 

[Pat Hartman]

No, the database is distributed in house and the users know better than to mess with it. The .accde does nothing to protect the database. it doesn't stop the users from opening the tables and updating the data directly. It doesn't stop them from creating their own macros or queriees, It only removes the source code. If you need to lock the database, you need to go far beyond just creating an .accde.

Please guide me to create bath file for my db

Really? You're not even going to take a shot at it?

md c:\SomeLocalDirectoryName
del c:\SomeLocalDirectoryName\BTI_Employee_Database_Testing.accde
copy "\\Book-dc\hr\BTI Employee Database\BTI_Employee_Database_Testing.accde" c:\SomeLocalDirectoryName
c:\SomeLocalDirectoryName\BTI_Employee_Database_Testing.accde

As I mentioned, the database needs to be downloaded to the user's local drive. Do NOT use a server location.

Also, keep in mind that if you use embedded spaces or special characters in path or file names, you MUST enclose the string in double quotes to isolate the offending characters.

 

[Babycat]

If you need to lock the database, you need to go far beyond just creating an .accde.

Hide Navigation menu, Hile acToolbarNo, Disable SHIFT key. Anything else, PAT?

 

[Pat Hartman]

I also hide all the tables in the BE and rename the BE with some non obvious extension like ".dbef" as long as no other app has claimed it. You don't want Windows to automatically try to open an app if someone double clicks on the BE so you would NEVER use something like .exe or .bat. However, When you link the BE, Access doesn't care what the extension is. You have to change the dialog to select * rather than just .mdb and .accdb extensions.