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.

 

[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.

 

[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.

 

[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?

 

[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.

 

[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?

 

[Babycat]

Changing BE extension looks great trick.
btw: How do you hide tables in BE? If someone has BE PWD, will he be still able to access BE hiden tables?

Thanks,

 

[gemma-the-husky]

@Pat Hartman

Can you just clarify. You can't run the update batch file process from inside the database can you?

I know if you are using mydbs_v13 you could collect mydbs_v14. (which is what I do sometimes), but then users have to manually edit the desktop shortcut.

But if you are using just mydbs which internally knows it is v13, you can't delete or rename the current dbs if it is runnnig, and then copy a new mydbs (internally set as v14). If I do it this way, I give the users a batch file to copy the new mydbs and overwrite the old one, but they have to do that while the current mydbs is closed.

how do you manage it?

 

[CJ_London]

much the same as a batch file I provide a shortcut on the users desktop that opens a small db on the server that just runs some code to compare the FE on the users machine with the FE on the server and if different versions it deletes the users FE and copies across FE on the server before opening the newly copied file, otherwise it just opens the original FE on the users machine. And then closes itself.

 

[theDBguy]

Changing BE extension looks great trick.
btw: How do you hide tables in BE? If someone has BE PWD, will he be still able to access BE hiden tables?

Thanks,

There are two ways to hide tables. If the user knows how to unhide them, then they can access the hidden tables once they get the BE password. In fact, just knowing the names of the hidden tables could be enough to access them.

 

[gemma-the-husky]

@Pat Hartman
Thanks for that. That's what I do pretty much. I thought you might have come up with a way to achieve this with a running database.

 

[theDBguy]

I thought you might have come up with a way to achieve this with a running database.

Just curious, are you aware of the FE updater by Bob Larson? It uses the running database to create the batch file and runs it.