Setting up Access 2007 with multi-users that can either read or write on Sharepoint

Question

I've been handed a problematic Access 2007 data base solution. The problem to start with is in avoiding record locks the original designer decided to split the data into three separate data bases (based on product line) which would avoid one program manager locking out another. The second problem now is that if anyone opens any one of the three data bases, they lock out the writers who can only open in read access.

There are approximately 100 users globally with three or four writers, the rest being readers. The data bases are on a SharePoint site.

To solve the issues, I'm proposing to do the following based on research to date.

1) Create a split data base thereby isolating the front end user interfaces from the data repository.

2) Create two front end interphase databases connecting to the backend. One is for readers, the other for writers.

3) For the readers, I'll be adding the following code to each form's Form_Load event:

   Me.AllowEdits = False
   Me.DataEntry = False
   Me.RecordLocks = 0  ' No Locks

This seems to work (in the limited testing that I can perform) in that it allows the readers to change column widths etc., copy from and paste elsewhere data but prevents them from locking the records for the writers and/or making any changes to the data.

4) For the writers the following to the same On-Load event:

   Me.AllowEdits = False
   Me.DataEntry = True
   Me.RecordLocks = 2  ' Edited Record

This works in preventing writers from saving changes to the form but allows editing of disparate records (although Access help says RecordLocks applies to the Page of records which sounds like they are talking about the table, not individual records??? My tests seems to show exclusivity to the record a writer is editing, not the entire table.).

5) I'm going to move all the records from the three data bases into one as the original work-around to avoiding record locks seems to have been resolved through the above steps(??). I should point out that the likelihood of one writer editing a record that is owned by another writer is very low. However, a writer editing a record cannot lock out another from editing a different record in the same table.

So far so good I'm thinking.

The next plan is to place the data base (Front and Back) on SharePoint. However, all the research I've seen indicates that each user should get their own front end locally. I would like to avoid that for obvious reasons (updating multiple Front Ends established on local machines). So my questions here are as follows:

A) Will SharePoint's own permission levels interfere with those set internally in the Access data bases? Right now, anyone (readers and writers) can edit a record.

B) Why can't I place the two instances of the Front Ends that multiple users can share? In other words, if the users are only reading or writing, can they not use/share the single instance of the Access Front End without locking if from use by other users? Right now, anyone can open the data base from the SharePoint site. It's just that the first one to open it gets write access. Everyone else who try to use the same data base are set to read only including the writers (but this is resolved first of all through splitting the data base, no?).

Lastly, if the SharePoint thing is a non-starter for any reason, I can use a shared network folder.

Any thoughts would be appreciated. Thanks

Answer 1

Seeing as there is no answer forthcoming to my query here, I'll answer myself with research and tests I've conducted.

First of all, using VBA to manage form properties and controlling the Writer's front end from being accessed by a non-authorized reader is a fail. When Office is loaded onto a user's workstation, the macro setting is defaulted to "High", or "Do not run Content...". So when a Reader inadvertently opens a Writer front end, the Form_Load event is blocked from running and the user has full edit permissions. If they happen to allow the macro to run, then the proper settings are established but it is assumed that most will not click on the "Allow Content to run" button. The solution then is to set the permissions in the form's property sheet. Yes a determined user can hack through if they want but who wants to do someone else's job in editing records.

The settings for the reader are as follows: Data Entry: No Allow Additions: No Allow Deletions: No Allow Edits: No Allow Filters: Yes Record Locks: No Locks

For the Writer: Data Entry: No Allow Additions: Yes Allow Deletions: Yes Allow Edits: Yes Allow Filters: Yes Record Locks: Edited record

I noted that when the Data Entry property was set to Yes, then the MS Find became unavailable. It's used to get to a particular record. Don't know why it becomes unavailable but there you have it.

The same applies to controlling access through a IsValidUser() function. The solution for the Writer front end is to password protect it. Again, not 100% fool proof but enough to dissuade an overworked engineer from going any further.

Putting the back end data tables onto SharePoint is not an option. You can't link any of the front end objects (Forms, queries etc.) to the underlying tables if they are on SharePoint. You can send the tables over as a list and then link to that but I was getting all manner of "Invalid path..." errors when I tried to update the links. So the back end resides in a standard Windows network folder.

There is no reason why you can't have a single Access front end residing on SharePoint that can be accessed concurrently by multiple users to view or edit records. Setting the Writer form's Record Lock property to Edited Record (aka Pessimistic) locks out a writer from editing a record already being edited by someone else. However, they can edit another record. On the Reader side, it is set to No Locks. This all works.