Multiple users question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have this database that I'm working on that wasn't designed specifically
as a "multi-user" application. But it's now to be used by various people
across a network, and I'm wondering what, if anything, I need to do to make
this work. I don't really have any security concerns, I just want to end up
with a system where more than one person can create new records at a time,
and browse through existing records, edit them, and print reports off the
record they are viewing.
Access help make it sound rather easy - just set "Open Mode" to "Shared",
and "Default Record Locking" to "Edited Record". "Open Databases Using
Record-Level Locking" is also checked. It sounds like that beyond that, all I
have to do is make the application available on shared network drive, and
that's it.
Could it really be that easy, or what else needs to be done?

Thank you for your advice.
 
I would *strongly* recommend you split the database so that each user has a
separate copy of the front end (the application mdb). More info:
http://allenbrowne.com/ser-01.html

I see no good reason for changing the locking strategy to "Edited Record."
Optimistic locking (shown as "No Locks") is a perfectly good approach if you
just take a moment to explain the conflict dialog to your users.

Record-level locking is okay if you need it. If your tables are reasonably
wide (say the sum of bytes in all fields is > 1000), it will do nothing
helpful. It will slow the database down. And there are some
incompatibilities (e.g. you can run into issues if you attach Access 97
tables.) My approach is to start without it, and enable it only if it proves
necessary.

But in general terms, yes: Access is a multi-user product, and it's that
easy.
 
It is generally preferred to "split" the database in to a FrontEnd (FE) and
a BackEnd (BE). The BE holds all the tables and the FE has all the queries,
forms, reports, etc and the tables are linked from the BE into the FE.

It is also recommended that the FE be placed on each individual workstation
hard-drive. This step will minimize the possibility of corruption.
However, the downside of this is the problem of keeping the FE database in
sync (that is, making sure everybody is using the same version and pushing
out new versions to everybody when you make a change.) Tony Toews has a
solution for that call the Auto FE Updater that you can find here
http://www.granite.ab.ca/access/autofe.htm that will help with this.

Another alternative is to create separate directories on the network for
each user and put a copy of the FE in each one. However, if neither of
those alternatives is possible (I can't do either of these where I work) you
CAN have everyone access the same network copy of the FE. Just be sure to
keep a development copy elsewhere. I do that as a matter of course and give
the users an MDE to use instead of the MDB.

That's generally all you need to do.

One caveat: If everyone shared access to the same network FE, you can't
have any temp tables or programmatically modify queries and such. In this
case, your best best is to have seperate FEs for each user.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thanks a lot, Allen.
I see no good reason for changing the locking strategy to "Edited Record."
Optimistic locking (shown as "No Locks") is a perfectly good approach if you
just take a moment to explain the conflict dialog to your users.
Click to expand...

The reason why I thought to use "Edited Record" locking is that I'd rather
avoid that error message because it looks a bit scary to the uninitiated.
More importantly, however, I had some data corruption problems at one point
when, while trying out something, I clicked on "Save" [simply to see what
would happen] - and Access replaced a couple of primary keys [ 1 or 2 digit
numbers] with weird 6 or more digit numbers that couldn't even be changed
back.
So why then do you think "Edited Record" locking is not necessary or good?
What are the disadvantages?

As for record-level locking, you advised against it until/unless necessary.
My question would then be - when will it become necessary? It seems to have
been on by default.
 
Thank you, Roger, for explaining the options I have. Looks like splitting is
the way to go.
I shall take a look at that auto-updater, thanks for posting the link.
 
The optimistic locking strategy is considerably more flexible, since the
lock exists during the save operation only (milliseconds.) I have dozens
(possibly hundreds) of multi-user databases in the field using optimistic
locking, some going back well over 10 years. With a well-designed database
on a stable network with reliable power and users who don't just switch off
in the middle of a write operation, corruptions are very rare - perhaps
averaging once in 3 - 5 years - and then usually fixable with a simple
compact/repair. OTOH, if any of those reliability factors are not present,
mean time between corruptions is measured in days rather than years.

Older versions of Access (like most databases) used page-level locking.
Access would lock a 2KB memory page of the data. If the width of the record
(the sum of possible bytes in all fields, plus the field and record
overhead) was less than 1KB, the locked page would hold more than one
record. Therefore it might be possible for one user to be editing a record,
and another user to find they were unable to edit the next/previous record
since the locked page contained more than one record.

Starting with Access 2000, Microsoft offered the option of record-level
locking. It is less tested (lots of the new stuff in A2000 was buggy), less
efficient (any operations with records take longer), and less reliable. I
personally ran into issues where transactions failed in Access 2000, 2002,
and 2003 if record-level locking was turned on, and the tables were attached
from Access 97 back end. Hence the suggestion to use record-level locking
only in cases where you need it. If users are regularly getting record
locking issues on narrow tables, then I suggest you trial turning on
record-level locking to solve that particular issue.

HTH.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Niniel said:
Thanks a lot, Allen.
I see no good reason for changing the locking strategy to "Edited
Record."
Optimistic locking (shown as "No Locks") is a perfectly good approach if
you
just take a moment to explain the conflict dialog to your users.
Click to expand...

The reason why I thought to use "Edited Record" locking is that I'd rather
avoid that error message because it looks a bit scary to the uninitiated.
More importantly, however, I had some data corruption problems at one
point
when, while trying out something, I clicked on "Save" [simply to see what
would happen] - and Access replaced a couple of primary keys [ 1 or 2
digit
numbers] with weird 6 or more digit numbers that couldn't even be changed
back.
So why then do you think "Edited Record" locking is not necessary or good?
What are the disadvantages?

As for record-level locking, you advised against it until/unless
necessary.
My question would then be - when will it become necessary? It seems to
have
been on by default.
Click to expand...
 
Hm, I guess your experience speaks for itself, and that the normal locking
should be ok.
But if I understand you correctly, problems with record-level locking are
more likely when you combine newer and older versions of access, right? So if
everybody uses at least Access 2002, it won't be issue, or will it?

You can probably tell that I'm really trying to avoid the situation where
people edit a record and then at the end are presented with the dialog that
tells them that somebody else already edited the same record, and do they
want to save anyway or discard their changes.

On the other hand, I've just been asked to restrict record editing so that
only the creator of a record can make changes to it afterwards.
Seems like that would render this issue moot.
 
I don't have any experience of problems with record-level locking in JET 4
tables, but I can't really comment as I generally turn this option off so my
experience is very limited. Others may be able to contribute here.

Access security does not stretch to the record-level, so you will need to
manually track who created the record if you wish to restrict who can edit
it.

If it's any use, there is a sample database illustrating some of these
techniques here:
Field-level permissions - How to lock controls on a per-field basis
at:
http://allenbrowne.com/ser-55.html
 
I already have a field where the creator of a record is supposed to put in
his/her name, so I was planning on using that.
And thank you for the link, I'll take a look and see how I can use it.
 
Niniel said:
I already have a field where the creator of a record is supposed to put in
his/her name, so I was planning on using that.
Click to expand...

Instead of asking the user to put in their name why not use their
network login name.

API: Get Login name
http://www.mvps.org/access/api/api0008.htm

What happens when they leave the employer?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
True, but I already have to collect that information for other reasons, so I
thought I could just re-use it. But you are right; maybe I can automate the
process and they won't have to actually type their own name.
 
if you using Access Data Projects; it would be as simple as
SUSER_SNAME()

Microsoft Access MDB isn't as mature as ADP is; and MDB hasn't been
fixed in a decade; it's time to move into a more reliable database
engine-- like SQL Server for example

MDB is obsolete; it's going away in the next version of Office

-Aaron
 
Niniel said:
True, but I already have to collect that information for other reasons, so I
thought I could just re-use it. But you are right; maybe I can automate the
process and they won't have to actually type their own name.
Click to expand...

One less thing for them to enter. And to potentially falsify.
Although why I do not know.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
"Allen Browne" wrote:
After looking at your sample db, it occurred to me that maybe it might be
better a/o easier to filter out records at the query level, ie. to let people
only see the records they've created.
Would that be possible?
 
If you have a way of identifying what the user's name is, and you don't want
them to be able to unfilter and see the other records, you could use the
Open event of the form to assign a query statement to its RecordSource.

This kind of thing:
Private Sub Form_Open(Cancel As Integer)
Dim strSql As String
strSql = "SELECT * FROM Table1 WHERE MyUser = ""Jack"" ORDER BY
Table1.ID;"
Me.RecordSource = strSql
End Sub
 
Cool, thank you, that may just be what I need.

Is there a way, using your method, for a "super user"/administrator type to
be able to see all records?
 
Ok, thanks.
I was told that they need 3 people to see everything, so I'm not sure that
that won't make things overly cumbersome, but I'll give it a try.
 
I should mention that this sample (like most of my samples) is intended to
suggest an approach rather than provide a robust solution. There are many
ways it could be improved. In this case, I'd suggest adding another field
the the users table called Group and put all those people into the admin
group. In that way your Or condition would be OR [Group] = 'Admin'

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2003 application slow when multiple users are on. 10
Record-Locking remains open after closing 0
Record Locking 2
Multi-user access 2
Muilty-user locks Records 5
Record lock settings 2
Multiple Users 4
Multiple Users- auto update? 1

Back
Top