Multi User Lockout

[MartinMCU]

Hello all,

I am designing an inventory database, using a FE/BE model in Access 2000,
that will be used by clients on Windows 2000 and Windows XP. Currently, all
the data entry forms are implemented and working correctly. The more advanced
features like searching for parts, etc. will be added as I get them written,
which is a nice feature of the FE/BE model. Currently, I have the FE
distributed as a .mde to one coworker who is populating the database.

Oddly, if I am editing the FE (as a .mdb on my computer) and he tries to
open his FE, it seems to hang. Upon closing my .mdb, his FE can open
normally. I can then reopen my FE and it seems to be working correctly. So, I
am not sure why it doesn't seem to be letting multiple users in. Is this a
"feature" of a .mde and .mdb trying to access the same backend?

There is a significant amount of startup code in my database. I have
implemented automatic client updating using version numbers, as discussed at:

http://www.databasejournal.com/features/msaccess/article.php/3286111

I also have automatic verification of the linked tables, and use the Common
Dialog control to locate the backend, if/when it moves. I have implemented
this, as discussed in the Solutions9.mdb file available on MSDN at

http://support.microsoft.com/kb/248674/EN-US/

At least, I think this uses the Common Dialog control. It brings up a dialog
that certainly appears to be the Common Dialog, though the code used does
differ from that in the MS Knowledgebase article

http://support.microsoft.com/default.aspx?scid=kb;en-us;209862

Regardless, the implemented code works, and seems to be playing nicely with
my automatic update code, since my automatic update code relies on paths in a
linked table, as opposed to being hardcoded as in the example. Finally, as a
performance booster, I am maintaining a persistent connection to the Linked
"tblPaths" Table via a hidden form. Could this be causing a locking issue?
Any thoughts are greatly appreciated. Thanks,

Matt Martin
(e-mail address removed)

 

[Klatuu]

I believe your problem has to do with record locking. Check your settings in
Tools->Options->Advanced
Default Open Mode should be Shared
Default Record Locking should be No Locks

 

[Albert D.Kallal]

Upon closing my .mdb, his FE can open
normally. I can then reopen my FE and it seems to be working correctly. So,
I
am not sure why it doesn't seem to be letting multiple users in.

You need to check and ensure that your users to the shared folder have FULL
rights. When I say full rights, we are talking about file create rights,
file delete rights etc. When ms-access starts, it needs to create some
locking files to allow it so run in multi user mode. What happens when some
users don't have full rights to the folder, then ms-access can't create
these temp files to manage collisions, and there for opens the file in
SINGLE USER mode.

In addition to the above, it is possible that the settings (in your mde, or
mdb) file were set to open the file exclusive, and again this means that the
users settings will be for single user mode. Check out the settings in

tools->options->advanced tab.

Take a look at the above settings, and make sure the default open is set to
shared.

And, also check out the permissions you gave to users to the shared folder
where the mdb is, since temp files need to be created, and deleted in this
folder. (in fact, the persistent connection trick fixes performance problems
due to those temp files not having to be created, and deleted).

 

[Klatuu]

Albert,
Enlighten me if I am confused. His user is using a different FE, hopefully

 

[MartinMCU]

Are these settings for the FE or the BE? If they differ between the two,
which gets priority? Also, if I want to lock a record so that only a single
user may edit it at once, how do I go about doing that? Thanks,

Matt Martin
(e-mail address removed)

 

[Rick Brandt]

Albert,
Enlighten me if I am confused. His user is using a different FE,
hopefully on his own computer. Since he is opening the FE, wouldn't
his .ldb files and others be there?

In a split app two LDB files are created. One for the front end and one for
the back end. Each is created in the same folder as the corresponding app
file.

 

[MartinMCU]

The FE is installed on each user's computer. Does the BE generate a separate
lockfile for each user, or does it keep track of multiple users within a
single file?

Matt Martin
(e-mail address removed)

 

[MartinMCU]

Alright, I just did a little poking around, and discovered that my machine
has Access set with the Default Open Mode as Shared, and the Default Record
Locking as Edited Record Only. My coworker has Shared and No Locks. This
would most likely explain why I can open it after he does, but not the other
way around. There is also a checkbox just below those two options labeled as
Open Databases using Record Level Locking, which is checked on both machines.
How does that checkbox interact with the other options?

Anyways, after setting both machines to Shared and No Locks, opening seems
to be working just fine. I'm still trying to figure out exactly how to set up
a multiuser environment so that only one user may edit any given record at a
time. Thanks for the help though!

Matt Martin
(e-mail address removed)

 

[Albert D.Kallal]

Albert,
Enlighten me if I am confused. His user is using a different FE, hopefully
on his own computer. Since he is opening the FE, wouldn't his .ldb files
and
others be there?

Temp locking files are created on the shared folder. Ask your self how does
ms-access handle collisions between multiple users when no software runs on
the server side? (answer: Each front end creates entries in a locking file
that is in the shared folder, and thus each front end can look at this
shared file and manage the collisions).

Sure, a locking file is also created in the front end folder, but that would
be used only for local tables in the FE.

So, if locking files can't be deleted, and created in the back end folder,
then ms-access can't run in multi-user mode. It needs these files so each
front end can figure out who what got what record locked.

Ms-access has to manage and figure out how to deal with multi-users, and
that information can't possibly be in the front end folder...so, naturally,
that locking information is SHARED amount all of the front ends...just like
the data file is......

 

[Albert D.Kallal]

The FE is installed on each user's computer. Does the BE generate a
separate
lockfile for each user, or does it keep track of multiple users within a
single file?

There is ONE lock file created on the server that ALL of the fronts use. So,
only ONE lock file is made, and the first user in creates this lock file.

The above explains often why people ask how come when certain users go in
first, that NO other users can work. Where as if other users go in first,
then all others can work. (answer: that first user going in has
create/delete rights to the folder...where the other user did not. As
mentioned, if ms-access can't create the locking file in the BE to be shared
by ALL users, then multi-user operation cannot happen).

This shared folder is where information about collisions and record locking
occurs. Very simply if ms-access can't make that file, then it can't manage
multiple users.

 

[aaron.kempf]

do real-world people really use MDB still?

you should use ADP; you centralize all your queries on the db server
and it's a LOT more fun to work with.

 

[Albert D.Kallal]

do real-world people really use MDB still?

Of course people still use mdb files. Why would they not? You are aware that
JET is roughly 50 to 200% faster then sql server..right?

you should use ADP; you centralize all your queries on the db server
and it's a LOT more fun to work with.

In what context? In the post you are responding about, at this point we are
only aware of TWO users in the system.

It would be incredibility stupid, ignorant, and arrogant on your part to
suggest
the cost of installing, setting up, and maintaining a server based system
for two
users. While in the future, the original poster may want more users, but
there is no indication, nor no way of discerning this. Since we don't know
the
details of the original posters intentions, I am at a complete and utter
loss
as why anyone with a real proper function brain would suggest using a adp
project in this case?

Further, many developers right now prefer using a mdb file linked to sql
server. This preference is ESPECIALLY so when you have an existing
application
with a good deal of dao code. There is little, if any performance difference
between a linked table via odbc/mdb, and a ADP table that uses a ole/db
connection.

You should only use a server based system when you need to. It is ridiculous
to suggest or hint that no one should use a mdb anymore. Or, suggest that
people who use mdb files are dinosaurs. I cannot imagine why you are
asking as to why people are still using a mdb file.

Further, what stops you from using a mdb with linked tables to sql serer
anyway?

I am not looking to pick on you, nor start a fight, but your comments seem
WAY out of line here, and further these comments are obviously not based
on any intelligence gathering on your part.

If you stopped talking your medication..then get back on it...or if you been
taking some drugs you are not supposed to..then it is time to stop.

Quit this nonsense while you are ahead.......

 

[Jeff K]

MartinMCU:

I was looking through this discussion, and I am pretty much in the exact
same situation. We are utilizing a FE/BE Access DB in a shared environment
(2-6 users) and having issues with users being locked out.

Looking through the discussion, it looked like there were 2 main issues:
- Record Locking strategy
- Access privileges for the users

Did their suggestions help? Is your db working as intended? Are you able
to lock a particular record that one usier is working on?

If everything is working well, please share:
- What are the FE settings for record locks?
- What are the BE settings for record locks?

Thanks,

 

[Apmarg]

I'm a self proclaimed newby at access programming, but one thing I've
discovered that will appears to lock other users out of a database is
changing properties of controls on a form without the "Me." prefix. Without
it it acts like the user has changed the design of the form and nobody else
can get it.

Adding the Me. prefix apparently only changes properties of the local copy
or something to that effect. I still find the Me. bit hard to grasp. I'm
sure most of the seasoned pros here are saying this is obvious, but not all
of us are pros.

Hope this helps, if not - it shouldn't hurt.

 

[Terry]

A disappointing reply especially from an MVP. Please! Let's be civil

 

[Larry Daugherty]

If you think Albert is being a little harsh it could be because you
came late to the party. Aaron Kempf is a troll. Google his posts to
get the flavor of his contributions, then put him in your Bozo file
for peace of mind.

Albert's speculations viz too little of a necessary drug or too much
of a banned substance seem bang on to me. I don't know the basis of
Aaron's issue but he desperately needs help.

HTH