Distributing Access Forms to global users for validations or amendments.

Question

I would like to distribute forms to User X, User Y and User Z. These forms would be pre-populated with existing data on their ongoing projects. Meaning for User X, he will only be able to see his own details etc. They are to validate the information, and make changes to the data if necessary.

I tried finding the best way to go about executing this and landed on MS Access (if there's something else, please do share).

So I have a database, created my forms but how do I go about sharing only the forms to my users and updating my database. Resources I have include:

1. SharePoint Online (may or may not have access to it...)

2. Outlook

3. Desktop Access

I am open to various scenarios involving direct updating through SharePoint, or even manually updating the forms received through them via email if it is possible.

My most important consideration is data security. User X should not be able to see the details of other users. User-level security from older versions of Access could probably do that but its no longer in the newer version and a check online suggests it isn't the most secure option.

Any help would be much appreciated.

Answer 1

Hello,

You can secure your data on latest desktop version MS Access as well. In order to do that, you need to save the database in executable form (.accde extension). Earlier version had inbuilt security which was easy to setting things up. In latest version, you can limit your user what to see and what to access everything by using code and a few setups.

Please let us know if you have any questions.

Answer 2

Hi,

Using an online system like SharePoint would probably best, but if not everyone would have access to it, then perhaps the easiest way is to distribute Excel spreadsheets of their own projects to each group/owner and then consolidate the information they send back to you for your own use.

Just my 2 cents...

Answer 3

If each of the users does not currently have Access installed you can distribute the free runtime version, which can be downloaded from Microsoft.  Assuming that each user is connected to a wired local network you can install the back end in a shared location on the system, to which each user has full permissions.  Each user can then be provided with a front end which contains only the forms they need, plus links to the relevant back end tables.

Each form should have as its RecordSource property an SQL statement which restricts the rows returned to those to which the current user has authorised access.  The user will be able to update the relevant back end data in these forms.

You do need to be aware that Access is not a secure environment.  Its previous implementation of user and group security was dropped because it was not secure.  While the above will limit each user's access to the data in the day-to-day operation of the database via the forms, and you can take other measures to restrict their access to the linked tables, if true Security is wanted then you need to think about using other products, such as an SQL Server back end.

For means of locking down a database in Access per se take a look at:

https://www.devhut.net/2016/09/01/securing-your-ms-access-database-front-end/

where Daniel Pineault provides an excellent overview of what can be usefully done.

For security by means of VBA or Active Directory see the blog by Tom van Stiphout et al at:

http://www.accesssecurityblog.com

For examples of various approaches to 'row level security' see SecDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

---

Ken Sheridan, Stafford, England