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!

 

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

 

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

 

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

 

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

 

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

 

[CJ_London]

was to check whether the FE was running in the same folder as the BE

I go one step further - I specify the location of the front end on a users machine - depends on requirements might be the users docs folder or folder off, perhaps the appdata folder or might be a specific folder on the C: drive. Then the app on open checks it is in the right folder and has the right name. If it isn't it closes with a suitable message.

This prevents users having multiple copies of the FE dotted around their computer.

 

[Marshall Brooks]

Need to back up a bit ...

I never mentioned sharing a front end. What I said was that if I had the FE open already and then clicked a shortcut to the same FE file that I already had open, I got two instances of the same FE running on my computer. The shortcut did not notice that the FE was open and shift focus to that like a shortcut to a Word document would do.

Somewhat annoying to me, but I don't know that myself (or another user, not AND another user) having two versions of the same file open would cause an issue.

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.

This was a hypothetical. User A and User B opening the same FE could be an issue, but is not allowed (any more - it was previously before I learned on here how to prevent it). Now, half our users use Citrix and they open the front end from their own mapped U:\ drive which I'm assuming would be considered on a server, but it is their own copy of the FE. I'm assuming this is not an issue and I don't know of another solution since they can't write to the Citrix desktop. We've been using it for a long time, but that doesn't necessarily mean it is safe.

I go one step further - I specify the location of the front end on a users machine

I do this also now. Has to be on the desktop for local users and the U:\ drive for Citrix users. A bit Draconian, but so far everyone is okay with it.

 

[Thales750]

Who said anything about "supporting the sharing of a front end"?

It wasn't a challenge. It was an actual question.
I've bee using KenHigg's version control for as long as I can remember. It eliminates all those issues, and it doesn't require scripts.

 

[The_Doc_Man]

A bit Draconian, but so far everyone is okay with it.

Sometimes, "Draconian" is the only method they understand.

 

[Marshall Brooks]

I've seen similar to KenHigg's version control before and know other admins that use similar. Works well, but requires two database files.

I use a variation of Bob Larson's file - https://btabdevelopment.com/free-access-tools/ - does need to create a script, but it creates the script itself and deletes the script on startup.