Multiuser locking problem...again.

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

Guest

I have the now almost ‘standard’ problem with multiuser access and have I
scoured the internet for a solution, but with no luck to date. Please help!

Problem: Once one user has access to the tables, another user is prevented
from accessing those tables, until the first user leaves.

I have the following setup:

PC1: Front end (client) database with forms, reports, queries and a couple
of local ‘temporary’ tables.
PC2: Front end – same as PC1 (client) database with forms, reports, queries
and a couple of local ‘temporary’ tables.

The back end database of just tables resides on PC1 in a shared folder,
fully accessible on the network to PC2. Both front end databases are linked
to the back end database.

If I run PC1 Front end it creates a record locking ‘database’ in the same
folder as the Back End. I can use the PC1 front end to view all forms bound
to the back end. I have full functionality.
When I run PC2 front end the application hangs (not responding) and remains
so until I close PC1 front end. Immediately on doing so PC2 front end runs
successfully and I can access all back end tables.
Further from this, if I run the PC2 front end first I have full access….then
concurrently starting PC1 this also runs fully, accessing the same back end.
It will perform record locks and record changes perfectly!!

Sounds familiar?

In summary, if PC1 runs first, no other front end on other workstations can
access it. It seems to me that PC1 has full ownership of the back end and
prevents further users, but doesn’t mind ‘coming in after’ another user has
the back end locked.

(Naturally, when PC1 is run first, it creates the back end record locking
file with the extension (.lbd for Access 203 or .laccdb for Access 2007).
Closing PC1 front end deletes the back end record locking file. Running PC2
front end has the same effect).

Now for what I have checked:
The back end is on PC1 shared. This is FULLY shared, with full permissions.
From PC2 I can create a test text file on the shared folder, I can edit the
test text file, I can delete the test text file. Full rights.
The folder and back end database are not Read only.
The back end database and front end databases are all opening in Shared
mode, and do not have record locking invoke by default.

I’ve eliminated the front end databases by simply opening the back end
database on PC1 and holding a table open. I can then open the same back end
database from PC2 but am unable to open a table, even a different table
opened by PC1. Closing that table held by PC1 allows PC2 to open other tables.

I’m running Vista/Office 20007/Access 2007 on both PC’s, now with all front
end and back end as Access 2007 format (.accdb)
I’ve had the same problem when PC1 is XP with the front end and the back end
uses .mdb and PC2 is Vista with the front end using .accdb format. Therefore
it can’t be any ‘special’ administrative right that Vista demands since it
also occurs with the XP machine.
I’ve moved the back end to PC2 and the reverse happens…PC1 cannot get access
if PC2 is run first.
I’ve moved the back end to a third computer, call it PC3 which PC1 and PC2
have full administrative rights to. Whichever PC1 or PC2 opens the backend
first, prevents the other opening it too.
I’ve switched off all the firewalls to be sure.

I’ve followed every tip I’ve found on the internet on fixing the problem…but
no success.

My thoughts are that perhaps there’s an ownership problem with the record
locking file (.lbd) preventing non-local access??

(note that I prevent PC1 from accessing PC2, even Public folders, but I
don’t think this is an issue since there is nothing PC1 needs to ‘relate’
back to PC2 with in respect of the database since all transactions are made
where the back end is…on PC1)

This looks to be a rather common problem raised, but with no real solution.
Most explanations always talk about database splitting and permissions, but
as you can see I’ve tried these. What more can I do for permissions other
than create/edit/save/delete!?!

I’d really appreciate some help with this please.

Cheers,
 
Hum, I would double check the settings for PC one (front end).

It's possible that it set up to open the database exclusive, but when they
can't achieve that goal perhaps it opens in shared mode?

You can find this setting under:

office button-> access options->advanced

scroll down to the advanced group. Make sure the default open mode is
shared.

(The above is a bit of a long shot but your might as well check this
setting).

I would also ensure that the share folder you're using for the back end is
not in c:\program files, as
there is some file virtualization issues that now come into play when you
use vista.

Another thing I would attempt to do is to perhaps run access with elevated
privileges.
(You seem to hit that you had exactly the same problem with xp, so, again
think this is a bit of a long shot).

But, try running ms-access (the shortcut) by right clicking on it..and
choosing "run as administrator ". AFTER ms-access loads, you THEN open your
front end via the file-Open dialog in ms-access.

Try the above couple of things, it seems you pretty well tried most other
things.

keep in mind that individual files can have particular settings and
permissions set on them, so while you might set the folder to all users, the
individual file still can have some permissions assigned to it. so, I would
also check the individual permissions you've decided to that one file.

I remember taking a floppy disk and pulling the filed from a floppy disk to
a server desktop. I then copy that file to a shared directory, and LO and
behold no one could use it. The reason for this is the file inherited the
security settings for the machine that I was working on, in this case the
server. it did not matter, or change anything of the fact that the folder I
copied the file into had full permissions for everybody, The navage a file
still retained its security permissions so, all I am saying is that an
individual file can have individual permissions assigned to it in Windows
XP, or vista (these permissions I'm talking about are not related to MS
access any way shape or form, but could affect this issue). Once again this
is a bit of a long shot, but I think at this point we're willing to try just
about anything that might be a possible solution .
 
Albert D. Kallal said:
I would also ensure that the share folder you're using for the back end is
not in c:\program files, as
there is some file virtualization issues that now come into play when you
use vista.
Click to expand...

Same for c:\Documents and Settings although that's called something
different in Vista. I'd be real tempted to setup a whole now folder
at the root of the hard drive and share just that.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Gentlemen...thank you for your responses.

Still no joy though. Following your advice I've moved the back end to a new
folder at the root of the hard drive of PC1. Full permissions given. The
folder and back end database are neither read only and I can create, edit and
delete from PC2. No joy - PC2 still can not open the database with PC1
holding a table open.

All three databases, 2 x Front end and 1 x back end are set to open in
shared mode, No locks on record locking and will open by using record level
locking. No joy.

I've run the PC2 front end as Administrator. No joy.

I've eliminated the front ends by opening the back end from first PC2 then
PC1 - as before, if PC2 opens the database first, the front end of PC1 (same
machine as the back end) will also access the database. The other way round;
PC1 first then PC2 can not open the same back end. This also eliminates any
linking issues as I'm directly using the back end.

I've tested this on two separate XP (with Office 2007) machines. No joy.

I've created a new test database (front end/back end) just in case there was
an inherited problem after upgrading to accdb from mdb. No joy.

I've decrypted the back end (removed password). No joy.

I'm seriously thinking it's a problem with the laadb created for the back
end. So, what permission 'contol' is excerted on the laccdb when it's
created? It's very strange that PC1 where the back end resides can gain
concurrent access with PC2, but PC2 is unable to gain concurrent access once
PC1 has the back end open.

Further, if I move the back end to a third PC3 then only one out of PC1 and
PC2 can open the database (and PC3 does NOT hold the database open). So, from
this it seems neither PC1 or PC2 has the permissions to concurrently join the
..lccdb already opened by the other (or wrest 'contol' from the other).

As you can tell, I'm at a complete loss! All comments welcome, even ones
suggesting me to chuck the kit out of the window...as I'm getting close to it!

I'd really appreciate some more thoughts, even if it's just to hear from
people how do have concurrent usage of Vista/Access 2007 so I can continue
with some sort of hope.

Many thaks for reading thus far!!!

John. Cape Town.
 
One point I've noticed concerns ownership of the .laccdb is that when PC1
opens the back end (PC1 is where the back end resides), it creates the
..laccdb with ownership as PC1/Admin.

If PC2 opens the back end first it creates .laccdb with ownship of PC1/Guest.

Perhaps a 'Guest' is UNABLE to update the locking table (.laccdb) when owned
by Admin, and Admin IS able to update when owned by Guest....

I'm out of my depth on this now...

John.
 
John Diamond said:
One point I've noticed concerns ownership of the .laccdb is that when PC1
opens the back end (PC1 is where the back end resides), it creates the
.laccdb with ownership as PC1/Admin.

If PC2 opens the back end first it creates .laccdb with ownship of PC1/Guest.

Perhaps a 'Guest' is UNABLE to update the locking table (.laccdb) when owned
by Admin, and Admin IS able to update when owned by Guest....
Click to expand...

Ah, yes, ok, now that makes sense.

Create an account on the PC1 machine with a password. Ensure it has
full privileges to that folder. Create an account with the same
name and password on the PC2 machine. Log onto PC2 using that
account and password and run your app.

At least that's how I do things on my Windows XP network at home to
give myself privileges on the other system.

There may be better ways of doing this in a work group environment but
I'm not aware of them.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Hi Tom,

Thank you for your posting. Good one. This does cover some of what I'm
experiencing.

The 'serious error' message will appear if I choose to end the none
responding program.

Tom, and for those who may experience this same issue, it's not just when
the back end is on a Vista machine. It's happened when the back end is on an
XP and is .mdb and the client (front end) is a Vista machine.

Finally, I don't believe it's (just) Vista. From my experiences, I think
it's Office 2007. The problem occurs with two XP machines, but with Office
2007 installed on the client (Office 2003 or Office 2007 on the back end
machine makes no difference).

Having tried all of the suggestions from Albert and Tony (thank you too) I
had come to the conclusion that there was a more serious problem and that I
wasn't doing something 'stupid'. At least this confirms it's not me and I
don't have to waste time trying!

I'll get the hot fix from MS and give an update here asap.

Thanks to all.
John.
Cape Town.
 
Hi Tom,

The hotfix works (with no 'side affects' yet either). Thank you!

For the record, the hotfix is suggested to be used on Vista machines. Since
I've experienced this problem also on XP running Office 2007 machines (none
with Vista) my suspicions are that it's more of an Office/Access 2007
problem. However, I don't know if the same hotfix can be applied to XP with
Office 2007...and am not too keen on trying to apply the patch...

Thanks to all who contributed.
John.
Cape Town.
 
Back
Top