Multi user run of a db application (1 Viewer)

JPFred

Registered User.
Local time
Today, 15:48
Joined
Oct 29, 2017
Messages
47
I have written an access application and would like to have accessibility to the database on a network server in a read only mode.



I know how to auto-run a form but would like the main access options inhibited and allow multiple users to access the database application from laptops having the database on a network server.

The main form for the students is startup in a read-only mode

Main form for the librarian is startup in a read-only mode
Main form for the librarian is in editor in an update mode


Each read-only form has multiple options depending on the user.
Each update form has multiple options but can only be accessed by one user.




Any help would be appreciated.


:rolleyes:


P.S. All workstations has an Access license
 

June7

AWF VIP
Local time
Today, 11:48
Joined
Mar 9, 2014
Messages
5,423
First of all, database must be split.

User level security is not easy with Access. I think platforms like Oracle and SQLServer can control accessibility like this.

In Access, can disable menus, ribbon, Navigation pane, distribute frontend as executable, design forms to be non-editable. But if users have knowledge of backend location they can likely access it directly.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:48
Joined
Feb 19, 2002
Messages
42,973
Beyond the recommendation to split each application into two parts, is the recommendation that each user have his own personal copy of the FE. Only the BE (data) is actually shared.

There are two common methods of distributing the current copy of the FE.
1. Use a custom application that determines if the user's local copy of the FE is the newest version and if not, download a replacement from the server.
2. Always download a new copy of the FE (this is the method I use) by having the shortcut run a .bat file that copies the FE from the master copy on the server to a directory on the local PC and then opens it.

The current version of Access will allow multiple users to open the same copy of the FE and use it at the same time. HOWEVER, Access WILL NOT support multiple users if any of them modifies any objects. So if you have code that makes tables or saves filters on forms or modifies in any way, any object, the FE cannot ever be used by multiple concurrent users.
 

JPFred

Registered User.
Local time
Today, 15:48
Joined
Oct 29, 2017
Messages
47
Thanks for the input. I was thinking about doing the download option and still may go there. The non read-only users would only be allowed after hours.


That being said I could go either way. Thanks for the input and solution options.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 15:48
Joined
Apr 27, 2015
Messages
6,286
Always download a new copy of the FE (this is the method I use) by having the shortcut run a .bat file that copies the FE from the master copy on the server to a directory on the local PC and then opens it.

Besides reduced bloat, are there any other substantial benefits to this method? I am considering going to this method but the one issue I have is the “Enable Content” banner I get whenever I push an update out.
 

June7

AWF VIP
Local time
Today, 11:48
Joined
Mar 9, 2014
Messages
5,423
Because users downloading Access files was not an issue in our office, set each user's Access application Macro settings to Enable all macros. So the new frontend copied down did not get the "Enable Content" roadblock.
 

isladogs

MVP / VIP
Local time
Today, 19:48
Joined
Jan 14, 2017
Messages
18,186
My approach is to ensure all FE files are located in the same location on each user's workstation. The network team need to run a registry script to set the location as trusted on each user's workstation. This only needs to be done once and normally happens automatically at next login.
No need to re-run the script for each update as the location is already trusted
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:48
Joined
Feb 19, 2002
Messages
42,973
Besides reduced bloat, are there any other substantial benefits to this method?
It is pretty easy to implement. All you need to do to push out a new version is to back up the old version and replace it with a new one on the server. Then unless it is an emergency fix, you can just let people get the new version when they open the app.

I've never run into that Enable Content message. Is that because you download the FE from the internet? I see that message all the time if I download something from this site and try to open it.
 

isladogs

MVP / VIP
Local time
Today, 19:48
Joined
Jan 14, 2017
Messages
18,186
There are two common methods of distributing the current copy of the FE.
1. Use a custom application that determines if the user's local copy of the FE is the newest version and if not, download a replacement from the server.
2. Always download a new copy of the FE (this is the method I use) by having the shortcut run a .bat file that copies the FE from the master copy on the server to a directory on the local PC and then opens it.

Both of the methods outlined by Pat are easy enough to implement.
Method 2 has the advantage of knowing that all users will always have a fresh copy of the FE.
However, unlike Pat, I always use method 1.

If you use an ACCDE FE so users cannot make design changes, I see no point in users continually downloading fresh copies of the same version from the server. If you have a very large FE (my main schools app has an FE approx. 140 MB) doing so would both add considerable network traffic and detract from user experience due to the download time.

However, I can see the benefits in cases where the FE isn't locked down, gets bloated by repeatedly adding/deleting data in TEMP tables in the FE or for any reason becomes unstable on a regular basis.
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:48
Joined
Sep 21, 2011
Messages
14,046
Colin,
So could you find out the size of the DB from within via VBA and then run the update if it exceeds a certain limit.?

However, I can see the benefits in cases where the FE isn't locked down, gets bloated by repeatedly adding/deleting data in TEMP tables in the FE or for any reason becomes unstable on a regular basis.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 19:48
Joined
Jan 14, 2017
Messages
18,186
Colin,
So could you find out the size of the DB from withing via VBA and then run the update if it exceeds a certain limit.?

Yes you can. If its useful, I can look up the code later today. Let me know if so.
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:48
Joined
Sep 21, 2011
Messages
14,046
No, don't look specially for me please.

I was just wondering if it could be done.

I, like you, was using the version check method. I just thought it was better to only copy when required, rather than every time the app was started.?

Yes you can. If its useful, I can look up the code later today. Let me know if so.
 

isladogs

MVP / VIP
Local time
Today, 19:48
Joined
Jan 14, 2017
Messages
18,186
Yes its very easy to include as part of a shut down procedure:
a) Check the file size
b) If it exceeds a specified value e.g. 50 MB, instruct Access to run a backup & then compact on close. Otherwise, close normally
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:48
Joined
Sep 21, 2011
Messages
14,046
I wasn't even thinking that way. :D

I was thinking, check the size of the FE on startup, and if over an arbitary size (or be able to get the size of the central FE file plus a percentage) then update with a fresh FE, just as you would if the version was out of date.

Yes its very easy to include as part of a shut down procedure:
a) Check the file size
b) If it exceeds a specified value e.g. 50 MB, instruct Access to run a backup & then compact on close. Otherwise, close normally
 

isladogs

MVP / VIP
Local time
Today, 19:48
Joined
Jan 14, 2017
Messages
18,186
There's always at least two ways of doing anything in Access :D
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:48
Joined
Feb 28, 2001
Messages
27,001
@PatHartman:

I've never run into that Enable Content message.

Happened to me at the Navy data center all the time. It had to do with a domain group policy that required an explicit statement of trust once per download of a new file. Trust it once and done. But because I was not allowed to exert control over other users' laptops or desktops, I couldn't force them to use a "pre-trusted" location.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 15:48
Joined
Apr 27, 2015
Messages
6,286
I disagree Colin! There's at least 3!

Sent from my SM-G925F using Tapatalk

Especially if you use one of those fancy Class Modules you have grown so fond of!

And Doc hit the nail on the head: our IT will not allow me to establish a trusted location. Bill Mosca’s FE updater is a bit of a scorched earth paradigm where the directory is deleted outright and then re-established. Writing this down though has given me the idea that maybe if I alter that bit, I may be able to pull this off.

Looks like I have something to look forward to when I get to work tomorrow...
 

Users who are viewing this thread

Top Bottom