Multi user run of a db application

[JPFred]

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.





P.S. All workstations has an Access license

 

[June7]

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.

 

[JPFred]

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]

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]

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]

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

 

[isladogs]

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]

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.

 

[isladogs]

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]

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]

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]

I wasn't even thinking that way.

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]

There's always at least two ways of doing anything in Access

 

[isladogs]

I disagree Colin! There's at least 3!

Sent from my SM-G925F using Tapatalk

LOL. OK clever clogs, come up with a third method for this scenario!!!!


Sent from my iPhone using Tapatalk

 

[The_Doc_Man]

@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]

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