By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Multi user locking after first update

P: n/a
Experts and books all say that you can share an Access back end on a
shared
drive with the front end running on each host computer. I have a
simple
database that tracks student data and it is shared between 4 staff
memebers.
Each staff computer has a copy of the front-end (linked tables, forms,
and
queries). They basically only use one form. The form works like this

Form -Query -Linked Tables -Main DB Tables

When the user opens the form initially, it works just fine. In fact,
two or
three users can open it up and edit fields simultaneously, but after
an
update has been made, only those who have that form open can continue
editing
(and it works fine). BUT, when the next user tries to open the form
a
message in the lower left corner says "this recordset is not
updatable". At
that point, the data is locked to any new users. Anyone who was
already in
can access and update the data, but any new instances of the form are
not
updatable.

I have double checked that the form and underlying query have "No
Locks." I
have noticed, however, that for some arbitrary period of time the
"record-locking" file is created for the back-end databse, even when
it's not
open. I do have a couple calculated fields (string manipulation
mostly)

Any help would be greatly appreciated. This has been the bain of my
existance for weeks.

Thanks,

Zmickle
Nov 12 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Would you mind telling us what backend you are using (Jet, MS-Acccess
mdb, MS-SQL, MySQL), version of backend, locking strategies...

rob*

zm*****@gmail.com wrote:
Experts and books all say that you can share an Access back end on a
shared
drive with the front end running on each host computer. I have a
simple
database that tracks student data and it is shared between 4 staff
memebers.
Each staff computer has a copy of the front-end (linked tables, forms,
and
queries). They basically only use one form. The form works like this

Form -Query -Linked Tables -Main DB Tables

When the user opens the form initially, it works just fine. In fact,
two or
three users can open it up and edit fields simultaneously, but after
an
update has been made, only those who have that form open can continue
editing
(and it works fine). BUT, when the next user tries to open the form
a
message in the lower left corner says "this recordset is not
updatable". At
that point, the data is locked to any new users. Anyone who was
already in
can access and update the data, but any new instances of the form are
not
updatable.

I have double checked that the form and underlying query have "No
Locks." I
have noticed, however, that for some arbitrary period of time the
"record-locking" file is created for the back-end databse, even when
it's not
open. I do have a couple calculated fields (string manipulation
mostly)

Any help would be greatly appreciated. This has been the bain of my
existance for weeks.

Thanks,

Zmickle
Nov 12 '08 #2

P: n/a
On Nov 12, 12:47*am, "Robert S." <robsmi...@googlemail.comwrote:
Would you mind telling us what backend you are using (Jet, MS-Acccess
mdb, MS-SQL, MySQL), version of backend, locking strategies...

rob*

zmic...@gmail.com wrote:
Experts and books all say that you can share an Access back end on a
shared
drive with the front end running on each host computer. *I have a
simple
database that tracks student data and it is shared between 4 staff
memebers.
Each staff computer has a copy of the front-end (linked tables, forms,
and
queries). *They basically only use one form. *The form works like this
Form -Query -Linked Tables -Main DB Tables
When the user opens the form initially, it works just fine. *In fact,
two or
three users can open it up and edit fields simultaneously, but after
an
update has been made, only those who have that form open can continue
editing
(and it works fine). *BUT, when the next user tries to open the form
a
message in the lower left corner says "this recordset is not
updatable". *At
that point, the data is locked to any new users. *Anyone who was
already in
can access and update the data, but any new instances of the form are
not
updatable.
I have double checked that the form and underlying query have "No
Locks." *I
have noticed, however, that for some arbitrary period of time the
"record-locking" file is created for the back-end databse, even when
it's not
open. *I do have a couple calculated fields (string manipulation
mostly)
Any help would be greatly appreciated. *This has been the bain of my
existance for weeks.
Thanks,
Zmickle- Hide quoted text -

- Show quoted text -
The backend is an Access DB, therefore Jet (no?). It is the result of
splitting a database that used to be consolidated.

More facts:
- I tried to take out all calculated fields in the query behind the
form, but it did not stop the locking problem.
- The locking file for teh BACK END is created when the form is
updated and does not go away until the fomr on the FRONT END is
closes.

Thanks for the help.
Nov 12 '08 #3

P: n/a
Ok reread your previous post.

What seems strange is the locking file even nobody is on the DB. You are
absolutely sure for this?

zm*****@gmail.com wrote:
On Nov 12, 12:47 am, "Robert S." <robsmi...@googlemail.comwrote:
>Would you mind telling us what backend you are using (Jet, MS-Acccess
mdb, MS-SQL, MySQL), version of backend, locking strategies...
>>I have double checked that the form and underlying query have "No
Locks." I
have noticed, however, that for some arbitrary period of time the
"record-locking" file is created for the back-end databse, even when
it's not
open. I do have a couple calculated fields (string manipulation
mostly)
Any help would be greatly appreciated. This has been the bain of my
existance for weeks.
Thanks,
Zmickle- Hide quoted text -
- Show quoted text -
The backend is an Access DB, therefore Jet (no?).
Fine. So it is no server issue... ahm STOP it could be a server issue.
If you having SAMBA running as a fileserver (DB is stored there) and
create rights are set wrong, the lock database file ( assuming ldb )
will be owned by the person who logged into mdb, first. The other ones
having no chance to accessing it because they cannot modify ldb. Default
rights are set to reading, so DB is accessible but not modifyable for
others. You have to set create mask attributes to rw for all.

More facts:
- I tried to take out all calculated fields in the query behind the
form, but it did not stop the locking problem.
- The locking file for teh BACK END is created when the form is
updated and does not go away until the fomr on the FRONT END is
closes.
Another thing that could be. Some VBA behind the form may cause the
lock. Did you check this?

rob*
Nov 13 '08 #4

P: n/a
<zm*****@gmail.comwrote in message
news:95**********************************@i18g2000 prf.googlegroups.com...
>
Any help would be greatly appreciated. This has been the bain of my
existance for weeks.
Do all of the users have full change permissions to the BE host folder?

Keith.
www.keithwilby.co.uk

Nov 13 '08 #5

P: n/a
On Nov 13, 3:18 am, "Keith Wilby" <h...@there.comwrote:
<zmic...@gmail.comwrote in message

news:95**********************************@i18g2000 prf.googlegroups.com...
Any help would be greatly appreciated. This has been the bain of my
existance for weeks.

Do all of the users have full change permissions to the BE host folder?

Keith.www.keithwilby.co.uk
@Rob - Upon further inspection, the locking file goes away after the
form is closed on the computer that made the update.

@Kieth - All users have read/write permission. Do they need more?
Nov 13 '08 #6

P: n/a
"zm*****@gmail.com" <sa********@gmail.comwrote in message
news:de**********************************@q26g2000 prq.googlegroups.com...
>
@Kieth - All users have read/write permission. Do they need more?
They need Delete permissions so that the last user out can destroy the
record locking file (ldb).

Nov 13 '08 #7

P: n/a
On Nov 13, 9:06 am, "Keith Wilby" <h...@there.comwrote:
"zmic...@gmail.com" <samlamb...@gmail.comwrote in message

news:de**********************************@q26g2000 prq.googlegroups.com...
@Kieth - All users have read/write permission. Do they need more?

They need Delete permissions so that the lastuserout can destroy the
record locking file (ldb).
Kieth, that is priceless information. Since I don't have control of
the shared drive, I can't change the permissions, so I will request it
tomorrow. Cross your fingers.

Thanks,

Sam
Nov 14 '08 #8

P: n/a
On Nov 13, 8:38*pm, "zmic...@gmail.com" <samlamb...@gmail.comwrote:
On Nov 13, 9:06 am, "Keith Wilby" <h...@there.comwrote:
"zmic...@gmail.com" <samlamb...@gmail.comwrote in message
news:de**********************************@q26g2000 prq.googlegroups.com....
@Kieth - All users have read/write permission. *Do they need more?
They need Delete permissions so that the lastuserout can destroy the
record locking file (ldb).

Kieth, that is priceless information. *Since I don't have control of
the shared drive, I can't change the permissions, so I will request it
tomorrow. *Cross your fingers.

Thanks,

Sam
Turns out all users have "Full Control" of the folder.

I have also tested the query itself without the form. When I update
the query, it locks the data until the datasheet is closed.
Nov 14 '08 #9

P: n/a
Hi Sam,

sorry for delay. Was off some time...

Sam Lambson wrote:
On Nov 13, 8:38 pm, "zmic...@gmail.com" <samlamb...@gmail.comwrote:
Turns out all users have "Full Control" of the folder.

I have also tested the query itself without the form. When I update
the query, it locks the data until the datasheet is closed.
If you having a query that cause problems, the only ways to stop it is,
to segmentize it to its bones. A boring job if it is a huge one.

Maybe it is a join or a reference between tables - you must see what it
is...

rob*
Nov 18 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.