Record Locking

[Guest]

I have an Access back end database on a server and a front end in the same
server directory. Multiple users open instances of the front end for
simultaneous data access. One user needs to run action queries that require
all other users to first close their instance of the database.
Is there a combination of options in record locking and query properties
that would permit the action queries to run without requiring other users to
first close out?
Does "No Record Locks" acheive this? If so, what are other consequences of
choosing that option?
Thank you for your consideration.

 

[[MVP] S.Clark]

Allowing multiple users into the same front end is known to cause many
problems. Locking is one, corruption is another.

Total Access Startup is an application that assists with delivering the
latest version of the front end to each user's machine. By doing this, you
will avoid all of these types of issues.

http://fmsinc.com/products/startup/

 

[Brian Wilson]

Allowing multiple users into the same front end is known to cause many
problems. Locking is one, corruption is another.

Total Access Startup is an application that assists with delivering the
latest version of the front end to each user's machine. By doing this,
you will avoid all of these types of issues.

Could you have been in such a rush to advertise your company's wares that
you forgot to read the question properly? Perhaps, as a knowledgeable MVP,
you would like to re-read and re-post?

 

[Guest]

Thank you for the information.
Instead of using Total Access Startup, could I elect to create a separate
front end DB for the one PC that needs to do the updates? If so, what lock
settings would I use on that copy and on the community copy?

 

[Brian Wilson]

Thank you for the information.
Instead of using Total Access Startup, could I elect to create a separate
front end DB for the one PC that needs to do the updates? If so, what lock
settings would I use on that copy and on the community copy?

"[MVP] S.Clark" wrote:

Hi George
Just to make sure I understand correctly - when you write:
"Multiple users open instances of the front end for simultaneous data
access"
I assume you mean that each user has his own copy of the front end (mdb or
mde file) on the local hard disk of his workstation, while the single shared
datasource is on a separate server. If so, this is good - the proper way to
do it.

Firstly we should establish whether all users really need to be out or not,
afterall, it is possible to batch update a table while others are using the
database without any conflict at all. For example, if you had a database
with a table of products which people were not constantly adding or
deleting, but had a field called Price which needed updating by increasing
all prices by 5%.
Set dbs=Currentdb
strSQL="UPDATE tblProduct SET Price = [Price]*1.05"
dbs.Execute strSQL,dbFailOnError
Msgbox CStr(dbs.RecordsAffected) & " record(s) updated"
Set dbs=Nothing
However, you might decide that you want to make sure that it is impossible
for anyone to view some cached version of the old price (say on some
combobox on a form). So on balance you might decide that you really do need
everyone to be out.

In general, though, you have no way of ensuring you have exclusive access to
the back end. But you can do some tricks - provided that everybody is
connecting to the back end via the front end which you designed. You can
have each front end asking every minute "does someone want to lock the
database?" This you would do by having a form which is constantly open (and
possibly hidden) grab a value from a field in a single-row table, eg.
tblDbInfo.ShutDown which is a yes/no field. You can then set this to 'yes'
and then wait while the front-ends start to realise that someone is
requesting a shut down. How the front-end applications react to this is
down to you, but they could flash or beep until the either the user shuts
down or the code does it for them. Then, once everyone has been kicked out,
you can open the database exclusively yourself with code like:
Set wks = DBEngine.CreateWorkspace("Jet", "Admin", "", dbUseJet)
Set dbs = wks.OpenDatabase("C:\Data\BackEnd.mdb", True)
If this works with no error, you can relax in the knowledge that everyone
else really is out and you can now make whatever changes you like.

 

[[MVP] S.Clark]

George,

Best practice dictates that you give ALL users their own front end mdb. You
can opt to give it to only one, but the settings should stay the same for
each user. Maintaining different version of the same app is a pain.

Your delima is probably the fact that a user has opened a form, edited a
record, and gone to lunch without saving or canceling the record. Another
user tries to access the record, or run the action query, and it fails. As
a business rule, you may want to better define WHEN a process should be
executed, such that other users are not affected or do not prevent it from
occurring. For example, have a nightly process that performs all of the
updates, when the other users have it closed anyway.

Deploying latest versions, tweaking record locking schemes, creating a
schedule route, and/or determining who is in the database when you need to
run a process are Access problems that I choose not to deal with.

Despite Mr. Wilson's comments, these are the reasons that products like
Total Access Startup, Admin, Agent, and the others were created.

 

[Guest]

I very much appreciate both of the replies.
Since I have not been following the good practice you describe, I will first
switch from multiple users having multiple instances of a single front-end
on the server, to each user having her own front-end on her own hard drive.
As you recommended, I will keep the design of those front-ends identical.
Then, since most of the users normally use the DB only to view records
without making changes, I will see if batch updates can be made without
further changes. I have Default Record Locking set to "Edited Record." If
that does not work, I'll try "No Locks."
Thanks again.

Thank you for the information.
Instead of using Total Access Startup, could I elect to create a separate
front end DB for the one PC that needs to do the updates? If so, what lock
settings would I use on that copy and on the community copy?

"[MVP] S.Clark" wrote:

Hi George
Just to make sure I understand correctly - when you write:
"Multiple users open instances of the front end for simultaneous data
access"
I assume you mean that each user has his own copy of the front end (mdb or
mde file) on the local hard disk of his workstation, while the single shared
datasource is on a separate server. If so, this is good - the proper way to
do it.

Firstly we should establish whether all users really need to be out or not,
afterall, it is possible to batch update a table while others are using the
database without any conflict at all. For example, if you had a database
with a table of products which people were not constantly adding or
deleting, but had a field called Price which needed updating by increasing
all prices by 5%.
Set dbs=Currentdb
strSQL="UPDATE tblProduct SET Price = [Price]*1.05"
dbs.Execute strSQL,dbFailOnError
Msgbox CStr(dbs.RecordsAffected) & " record(s) updated"
Set dbs=Nothing
However, you might decide that you want to make sure that it is impossible
for anyone to view some cached version of the old price (say on some
combobox on a form). So on balance you might decide that you really do need
everyone to be out.

In general, though, you have no way of ensuring you have exclusive access to
the back end. But you can do some tricks - provided that everybody is
connecting to the back end via the front end which you designed. You can
have each front end asking every minute "does someone want to lock the
database?" This you would do by having a form which is constantly open (and
possibly hidden) grab a value from a field in a single-row table, eg.
tblDbInfo.ShutDown which is a yes/no field. You can then set this to 'yes'
and then wait while the front-ends start to realise that someone is
requesting a shut down. How the front-end applications react to this is
down to you, but they could flash or beep until the either the user shuts
down or the code does it for them. Then, once everyone has been kicked out,
you can open the database exclusively yourself with code like:
Set wks = DBEngine.CreateWorkspace("Jet", "Admin", "", dbUseJet)
Set dbs = wks.OpenDatabase("C:\Data\BackEnd.mdb", True)
If this works with no error, you can relax in the knowledge that everyone
else really is out and you can now make whatever changes you like.


.