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

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

 

[Pat Hartman]

btw: How do you hide tables in BE? If someone has BE PWD, will he be still able to access BE hiden tables?

Use the option in the navigation pane. It is just one more hurdle that someone trying to get into the database has to overcome. As we've all said many times, no Access file is ever truly secure. Tht FE is just code so that is technically intellectual property but if you are not selling the app, that really doesn't matter because it's not yours anyway. It belongs to your employer or your client unless your contract says that you own the source code. You just want to do your best to prevent accidental modifications. But if you need security for the data which is far more valuable, then you need to be using a server based RDBMS like SQL Server.

@gemma-the-husky The app is not open. The shortcut runs the batch file. Then the batch file overwrites the old FE and opens the new FE

Inside the FE, I check the version by comparing a local table to a server table. I also ensure that the FE is running locally and not from the server. The version test is frequently done by the installer app that many people prefer to the batch file but that relies on the user always opening the installer app or in my case, using the shortcut instead of opening the FE directly. Sometimes, the user notices the file in the folder and rather than clicking on the installer app or shortcut, they just open the FE directly and so, although I don't prevent that by the check, at least I ensure that they open the correct version of the FE and they don't open the one on the server if they happen to come across that.

Unless you hide the FE somehow when you distribute it, you can use the same trick with the FE and just rename its extension it to prevent it from opening with a double-click. If you do that, you have to change the last line of the .bat file so that it opens Access and passes the file name of the FE as an argument.

 

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

 

[Pat Hartman]

No. I try not to send out updates mid-day unless they are new features. If I have to update something on an emergency basis, I have to get everyone to shut down for a couple of minutes and then reopen the app.

 

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

 

[Pat Hartman]

It's just an extra layer of complication. I like simple Every loader I have examined is overly complicated and usually requires modifications to the FE. I haven't looked at this one specifically that I remember. If the loader creates the batch file, you must need some separate distribution process to start it off. Is this the method you use? Using the batch file (your's can be as complicated as you want.) If you want to include version checking. etc. then you can use VBScript which lets you easily access data in Jet/ACE. The .bat file or the script both allow you to do the initial distribution using an email with a shortcut.

 

[Khurramshaikh47]

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.


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.

Hi, Pat
I have created FE (ACCDE) file on my Local Disk (D
and then created a batch file with .bat extension please check the screenshot below:

Then I created its shortcut on the server location where my backend file and a copy of front-end file is already exist (I also created shortcuts to the front-end users locations):

Then I Run the shortcut:

Now I have changed the color of a button of my database form (.accdb file) on my Local Disk (D

Now I save as and replace my ACCDE file in my Local Disk (D

But No changes are updated on a single distributed FE files, I've checked the file on the users computers and server as well:
Screenshot of FE file from users desktop:

I've tried to open the updater again and again. But nothing is updating on users' front-end files. Until I copy the updated ACCDE file from my Local Disk to the server and users' desktops manually.

 

[Pat Hartman]

The .bat file needs to be stored on the server. You can place it in the directory where the master copy of the FE is stored. Do NOT store the .bat file in the user directory. You seem to have swapped the .bat file with the shortcut. The shortcut is installed on the LOCAL drive. The .bat file lives on the Server so that if you need to change it, you can change it without having to visit every single user computer!!! The shortcut simply runs the .bat file so it would never need to change.

Did you send a copy of the shortcut that opens the .bat file to each user?

Developers should ALWAYS have extensions displayed. You should never have to guess what the extension is for any file.

 

[Pat Hartman]

Thanks for the like but we are not children here. Likes are totally useless when it comes to helping people who find this thread later determine what answer solved the problem. So, when you have a solution to a problem it is always more helpful to say, in words, what it was or if one specific comment helped more than others, point that out.

 

[Khurramshaikh47]

The .bat file needs to be stored on the server. You can place it in the directory where the master copy of the FE is stored. Do NOT store the .bat file in the user directory. You seem to have swapped the .bat file with the shortcut. The shortcut is installed on the LOCAL drive. The .bat file lives on the Server so that if you need to change it, you can change it without having to visit every single user computer!!! The shortcut simply runs the .bat file so it would never need to change.

Did you send a copy of the shortcut that opens the .bat file to each user?

Developers should ALWAYS have extensions displayed. You should never have to guess what the extension is for any file.

Ok Pat now I will show the extension of my files.
Actually I have saved the master copy of FE on my local disk and copy it to the server. You mean I have to save the .bat file to the server and then share its shortcut to my local disk and to the users as well?

 

[Khurramshaikh47]

Thanks for the like but we are not children here. Likes are totally useless when it comes to helping people who find this thread later determine what answer solved the problem. So, when you have a solution to a problem it is always more helpful to say, in words, what it was or if one specific comment helped more than others, point that out.

I am sorry I was reading your reply and understanding it.

 

[Pat Hartman]

Not sure why this is so hard. Again.
1. The current distribution version of the FE is stored on the server. Unless your PC is properly backed up, then you need to also store your design master on the server. It is too dangerous to have the design master stored only on your Local PC.
2. The BE is stored only on the server. You need a testing copy and that should be stored on your local PC.
3. The .bat file is stored on the server.
4. The master copy of the shortcut is stored on the server.
5. Create a shortcut that runs the batch file. Store it on the server
6. You distribute the shortcut to each person who will be using the application with instructions to save it on their local drive (not sure why you are using D (something is amiss here). The local drive is always C. Use email to distribute the shortcut.

When the user wants to run the application, he double clicks on the shortcut.
The shortcut runs the batch file from its server location
The batch file executes the four instructions.
The FE opens on the client PC.

YOU are the developer. Are you also a USER of the application? If so, then you also need the shortcut when you want to open the production version of the application. When you want to work on your development version, you open it directly. You would NOT EVER use the shortcut since the shortcut would overwrite your changes and you would be very unhappy.

 

[gemma-the-husky]

Updating programme versions is a tricky thing.

fwiw, I use a UK government payroll (free for small companies). Download and install. When they release a new version, the software asks you whether you want to install the new version. If you accept, it closes your current payroll, and downloads an updater to make the required changes. It's still not clever enough to tell you when it's done, and then restart the service. You have to guess it's probably finished and restart it yourself. And that's the government! (well, the UK Inland Revenue Service).

So my updater is a bit like that. Does 95% of it, but you still have to do a bit yourself, and I find it's hard to rely on users understanding anything about computers. A lot of them don't understand a folder structure, for instance. They often don't realise their file extensions are hidden for known file types, so can't tell the difference between a database file, and a locking file, and could easily delete the database file. They try searching for the database, and find the master copy on the server, which fortunately won't work because there are no configuration files in that folder on the server, but they phone for support because of the errors, and you wonder what made them not follow the simple instructions.

They might not show full details of files in their file explorer (if they understand what you mean by file explorer), so they can't even tell you there's a big one and a small one. (database file and locking file). You would think Access would be able to prevent users accidentally deleting or even messing with the back end, wouldn't you, but I always have it in mind that a user might just delete the back end.

Sorry, we are a fair way away from the thread title, and I'm just letting off a little steam.

 

[Pat Hartman]

Access isn't a file manager, nor can it be. It isn't omniscient. Your app doesn't run 24/7, nor does Access itself. It cannot control anything that is happening on a different PC under any conditions. Windows security prevents that from happening. Your VBA code simply runs as subroutines to MsAccess.exe's code. It doesn't have the necessary resources to do anything beyond what MSAccess.exe allows it to do.

However, Windows, CAN keep the user from deleting the BE if the permissions for the file are set up correctly.

 

[Khurramshaikh47]

Not sure why this is so hard. Again.
1. The current distribution version of the FE is stored on the server. Unless your PC is properly backed up, then you need to also store your design master on the server. It is too dangerous to have the design master stored only on your Local PC.
2. The BE is stored only on the server. You need a testing copy and that should be stored on your local PC.
3. The .bat file is stored on the server.
4. The master copy of the shortcut is stored on the server.
5. Create a shortcut that runs the batch file. Store it on the server
6. You distribute the shortcut to each person who will be using the application with instructions to save it on their local drive (not sure why you are using D (something is amiss here). The local drive is always C. Use email to distribute the shortcut.

When the user wants to run the application, he double clicks on the shortcut.
The shortcut runs the batch file from its server location
The batch file executes the four instructions.
The FE opens on the client PC.

YOU are the developer. Are you also a USER of the application? If so, then you also need the shortcut when you want to open the production version of the application. When you want to work on your development version, you open it directly. You would NOT EVER use the shortcut since the shortcut would overwrite your changes and you would be very unhappy.

Thank you so much Pat, It's working perfectly.

 

[Pat Hartman]

You're welcome.

 

[Babycat]

Not sure why this is so hard. Again.
1. The current distribution version of the FE is stored on the server. Unless your PC is properly backed up, then you need to also store your design master on the server. It is too dangerous to have the design master stored only on your Local PC.
2. The BE is stored only on the server. You need a testing copy and that should be stored on your local PC.
3. The .bat file is stored on the server.
4. The master copy of the shortcut is stored on the server.
5. Create a shortcut that runs the batch file. Store it on the server
6. You distribute the shortcut to each person who will be using the application with instructions to save it on their local drive (not sure why you are using D (something is amiss here). The local drive is always C. Use email to distribute the shortcut.

When the user wants to run the application, he double clicks on the shortcut.
The shortcut runs the batch file from its server location
The batch file executes the four instructions.
The FE opens on the client PC.

Hi Pat,

I distribute same FE to 2 different teams: R&D and purchasing team. They have to login in with given username & password, this login form has "remember me" feature which is store on local table.
If the FE is downloaded from server everytime user clicking on the shortcut, then"remember me" feature will no longer work.

Is there a good suggestion for this circumstance?

Thank you