Not opening two of the same versions of the Front End file?

[Marshall Brooks]

In a previous post, I mentioned that I had used to have the FE on a server for users to download and some users would open it from there. Someone replied that two different users opening the same front end was bad news, so I added code to only allow the database to be opened from the local users folders.

Today, I didn't notice that the database was already open, so I clicked a shortcut to it and was surprised when it opened a second instance of the front end. (If I double-click a Word file or a PDF file, it won't open it twice, it just takes me to the already open file. And Excel won't even open two files with the same name, even if they are in completely different directories).

Questions:

• Is this a potential problem? The previous post said TWO users opening the same FE was a problem. This is one user (me) opening two copies of the same FE (same file, same location).
• Is there a way to prevent this and have it basically only open instance of a given file? (From the same location - in theory I "might" want to open two files with the same name from different directories.) Even if it isn't a problem, I'd prefer to prevent it!

Thanks as always!

 

[Pat Hartman]

Access databases can be shared "out of the box". You have to make them not sharable if you need to. If users are inclined to do this, the best method to prevent it is to have your shortcut always open the file in exclusive mode. The FE should never be shared so this is safe to do permanently.

 

[Marshall Brooks]

@Pat Hartman - Thanks!

have your shortcut always open the file in exclusive mode

Didn't know how to do that, but I found a link, but some problems - if I read it correctly: https://www.access-programmers.co.u...ccess-database-only-in-exclusive-mode.280953/

• Currently, I have the FE on my desktop and a shortcut to in in the Quick Access toolbar. I might open it from either one. If I read correctly, adding /excl after the shortcut would prevent me from opening it from the shortcut and the desktop, but I could still open it twice from the desktop. There is a shared mode setting in Access which would prevent me opening ANY database in shared mode, which I was going to say is probably fine for me, but we do have some other groups using non-split databases, which I suspect would result in "The database has been placed in a state by Marshall Brooks which prevents it being opened or locked" - which the other group would probably frown upon.
• Most of our users have the database on the desktop or a mapped drive and open it directly without a shortcut. Telling them to change a setting in Access under Advanced won't work well, if that were an option, which I'm not sure it is.

But I don't see any mention of how to lock the FE file itself ...

 

[Marshall Brooks]

Also - adding \excl or '\excl' after the shortcut gave me an error about a command line option that Access doesn't recognize and then it opened anyway ... ???

 

[Pat Hartman]

Most of our users have the database on the desktop or a mapped drive and open it directly without a shortcut. Telling them to change a setting in Access under Advanced won't work well, if that were an option, which I'm not sure it is.

I don't let them do that. I make them use a shortcut which downloads a new version. An easy way to do this is to add a table in the FE and increment a column in the table when the user logs in. If the value is > 1, then the user is reusing an existing copy of the FE rather than downloading a fresh one so you can exit.

As I said, the FE should NEVER be shared so it is quite OK to simply set the FE to alway open in exclusive mode.

 

[Marshall Brooks]

so it is quite OK to simply set the FE to alway open in exclusive mode.

But there is no simple way to do this, other than adding the table and incrementing the column, which would then exit rather than going to the version that is already open and therefore would be likely to confuse the user. (I guess I could display a message box "Database is already opening. Clicking OK will exit this copy."

 

[Pat Hartman]

 

[Marshall Brooks]

But, if I understood correctly, that affects ANY database, not just the FE, and each user would have to set that themselves (and possibly on each server for Citrix users)?

Or am I missing something?

 

[Pat Hartman]

You're right. That's about as dumb as it gets.

But, it still shouldn't be a problem on the user's computer.

I don't have a network to test on so you will need to test this to see how it impacts the BE. Does this setting on the FE prevent multiple users from sharing the BE?

If the setting causes th BE to be locked also, then you need to add code in the FE that looks to see if there is another copy of the FE open on "this" computer. I don't have the code handy but if you search here, you should find it or perhaps someone will post it for you.

 

[gemma-the-husky]

FWIW, I sometimes get support calls when users try to use the master install copy on the servers. I have a startup ini file that points to the correct backend database, and the master version on the server doesn't have it's ini file. Users tend to show you that nothing is fool proof.

 

[Marshall Brooks]

I'm thinking I am being overly-paranoid - which doesn't mean people AREN'T out to get me.

I can see User A and User B both having the same FE open on the same network causing an issue. They select another record and the front end jumps for the other user.

I have different front ends that edit different tables in the backend and it's never been an issue (that I'm aware of) so far.

I'll be offline until 2024, but I'll check back then. Merry Christmas and Best Wishes for 2024 to all!

 

[Pat Hartman]

I can see User A and User B both having the same FE open on the same network causing an issue. They select another record and the front end jumps for the other user.

Why are you allowing this to happen? Users should NEVER be opening a FE on the server. That you can check also.

When two users have the same FE open, the data doesn't get confused. Access manages the data by using the .laccdb opened by the BE. It is the FE that can become confused. If userA changes some setting like sorting a subform and then saves the changes. UserB will see UserA's sort seq when he opens the form

 

[gemma-the-husky]

If you use a local table inside the front end, then if two users share the front end, they can overwrite and interfere with each other by changing the data in the local (shared) table.

I think that's perhaps one of the biggest issues to understand as it affects the way you have to think when you develop a database.

 

[Minty]

I have code that checks a database is being opened in a C:\ based folder, if not it knows it's on a network and just closes down after putting a message up.

 

[johar123q]

Yes, you need to give each user their own copy. Sharing a single file is just asking for trouble. Apart from the types of error messages you're getting you also risk data corruption.

Is there a reason not to give each user their own copy? If it's a question of updates, you can easily handle that by just creating a batch file to copy the front end each user's machine and running it as a part of each user's logon script, so each user gets the latest copy whenever they log on to Windows.

Or you could have a script that just pushes the latest copy to each user at a at a certain time each day before business hours, to handle the situation of the user not turning off their machine at night.

 

[Solo712]

FWIW,
@1 - Using the same FE copy from the server by more than one user is a potential problem, eg. if the app settings are manipulated or customized by the users via FE tables. At any rate, I would discourage using shared FE as it defeats the very purpose of a split database. Restrict the direct access to the FE file on the server. Create an app distributing the FE to the local machines.

@2 - As noted the exclusive access to the database affects both FE and BE and therefore is not a solution. The best practice is to adhere to the design of the Access database which assumes client local machine for the FE.

 

[Thales750]

KenHigg made a great frontend version control.
We used used it for hundreds of updates over 5 years with dozens of users. It performed flawlessly.
KenHigg's Version Control

 

[The_Doc_Man]

The biggest danger in sharing an FE file is file locking at the record level, which Access has to manage via entries in the .LDB/.LACCDB file, but which is itself a file that is ALSO subject to Windows file-level locking as well. Things can get quite hectic during lock management exchanges. A lot of problems come about when you get lock collisions (conflicts) on the same physical block, which potentially leads to a failed update where two users try to add records at the same time. And they WILL collide in that case - it is inevitable given the method of memory allocation and garbage collection used by Windows programs.

 

[Thales750]

The biggest danger in sharing an FE file is file locking at the record level, which Access has to manage via entries in the .LDB/.LACCDB file, but which is itself a file that is ALSO subject to Windows file-level locking as well. Things can get quite hectic during lock management exchanges. A lot of problems come about when you get lock collisions (conflicts) on the same physical block, which potentially leads to a failed update where two users try to add records at the same time. And they WILL collide in that case - it is inevitable given the method of memory allocation and garbage collection used by Windows programs.

Why would anyone ever share a frontend? I cant think of a single example supporting it.

 

[The_Doc_Man]

Who said anything about "supporting the sharing of a front end"? When you get "independent thinkers" as users, they don't want to be bothered by having to download a front end and I wasn't allowed by the Navy to build auto-download scripts. So the users who can't be bothered to download a copy of the FE to their local folders just open the "master" FE directly. I eventually realized that the only way to plug the hole was to check whether the FE was running in the same folder as the BE and to disallow that, forcing a nasty pop-up message followed by an APPLICATION.QUIT operation. Took a few tries because of complaints and such, but eventually they got the message.