473,327 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

record lock = db lock?

My users are running Access '03. They have a database with a form
that allows them to enter new records. Frequently they will move to a
new record and not enter any information. This prevents the record
from writing to the table, and worse, it prevents anyone else from
opening the database (db has been placed in a state by user....etc.)

I haven't been able to figure a way to force the autonumber to
generate with an event on the new record. I suppose if I could, then
I could force it to write to the table.

Is there something that can be done programatically to prevent these
lockouts from occuring?

Apr 26 '07 #1
5 8798
This lock should not occur if:

a) Everyone opens the database in Shared mode (not exclusive):
Tools | Options | Advanced | Default Open Mode = Shared

b) You use optimistic locking (which is the default):
Tools | Options | Advanced | Default Record locking = No Locks.

c) The form does not dirty the record immediately you visit it.
This problem usually occurs if you have code in the Current event of the
form that assigns something to a bound control. Show the Record Selector
(Left edge of the form), so you see the icon change from a triangle to a
pencil if editing has begun. Alter the code of the form so that it does not
dirty the new record merely by visiting it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa****@yahoo.comwrote in message
news:11**********************@s33g2000prh.googlegr oups.com...
My users are running Access '03. They have a database with a form
that allows them to enter new records. Frequently they will move to a
new record and not enter any information. This prevents the record
from writing to the table, and worse, it prevents anyone else from
opening the database (db has been placed in a state by user....etc.)

I haven't been able to figure a way to force the autonumber to
generate with an event on the new record. I suppose if I could, then
I could force it to write to the table.

Is there something that can be done programatically to prevent these
lockouts from occuring?
Apr 27 '07 #2
Allen,

That 3rd answer is solving our problem. I moved the onCurrent events
to onDirty events. This will save a lot of headaches. Thank you!

Maybe you have an answer for a similar issue. Users add a new record
and do not enter anything to trigger the autonumber. Another user
will then be unable to commit their new record to the table due to
some conflict. I have it set to locking the edited record, but I
think this might not include a new non-committed record as something
being "edited".


Apr 27 '07 #3
If these are Access tables, you should see the AutoNumber assigned at the
instant you start the new entry (i.e. when Form_BeforeInsert fires.) The
unique number is assigned to this user, and will never be assigned again.
Even if you abort the new record, the autonumber is not reused. Therefore
another user starts a new record before you save yours, they are not given
the same AutoNumber.

If your database is not behaving like that, then something is different.
Perhaps they are not JET tables, but attached tables from another database.
Or perhaps they are not AutoNumber fields, but numbers assigned by code. Or
perhaps something is messing it up (such as a main form and subform, with
the same autonumber field in LinkMasterFields and LinkChildFields.)

In general, "No Locks" (also known as optimistic locking) gives the best
results.

And back to the previous topic, if you only want to assign those values at
the new record, it might be better to use the form's BeforeInsert event
rather than OnDirty. BeforeInsert is more reliable, and only fires for new
records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa****@yahoo.comwrote in message
news:11*********************@s33g2000prh.googlegro ups.com...
Allen,

That 3rd answer is solving our problem. I moved the onCurrent events
to onDirty events. This will save a lot of headaches. Thank you!

Maybe you have an answer for a similar issue. Users add a new record
and do not enter anything to trigger the autonumber. Another user
will then be unable to commit their new record to the table due to
some conflict. I have it set to locking the edited record, but I
think this might not include a new non-committed record as something
being "edited".
Apr 27 '07 #4
Good tip on the BeforeInsert.

--

If you were to create a form with an autonumber field, and click on
the arrow with the >* to add a new record, the autonumber does not
activate until you type something into the record. I can see the
value as [autonumber] in the field, waiting to be triggered.

Testing this situation, if you are to leave the record like this,
other users will have problems adding a record of their own.

On another note, a problem we encounter when setting the record
locking to no locks is users frequently getting the message about
"another user making changes...drop changes or overwrite?"

So mainly I'm aiming to solve this whole issue of users sitting idle
in a record before the autonumber assigns.
Apr 27 '07 #5
Okay, perhaps someone else who uses pessimistic locking can comment on this.

I generally use optimistic, and always train the users in how to handle that
conflict dialog, but most of them tell me they never see it. (And of course,
the conflict doesn't arise with optimistic locking.)

Perhaps another option might be to use the form's Timer event to close the
form if the user is idling at the new record:
http://support.microsoft.com/?id=128814

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa****@yahoo.comwrote in message
news:11**********************@t39g2000prd.googlegr oups.com...
Good tip on the BeforeInsert.

--

If you were to create a form with an autonumber field, and click on
the arrow with the >* to add a new record, the autonumber does not
activate until you type something into the record. I can see the
value as [autonumber] in the field, waiting to be triggered.

Testing this situation, if you are to leave the record like this,
other users will have problems adding a record of their own.

On another note, a problem we encounter when setting the record
locking to no locks is users frequently getting the message about
"another user making changes...drop changes or overwrite?"

So mainly I'm aiming to solve this whole issue of users sitting idle
in a record before the autonumber assigns.
Apr 28 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Anita | last post by:
Hi All, I have a question about lock hint for you : If the first user currently run a select command with share lock and hold it. What kind of lock (lock hint) should be used by the second...
7
by: Sunny | last post by:
Hi, I can not understend completely the lock statement. Actally what is locked: 1. the part of the code between {...} or 2. the object in lock() In the docs is written: for 1: The lock...
1
by: Ryan Moore | last post by:
is there any way to "lock" all controls? something like : myControl.LockOthers(); myControl.ReleaseOthers(); thnx
14
by: Sharon | last post by:
Hi all. I have an ArrayList and sometimes while enumerating through, i get an exception because another thread has added to the ArrayList. To solve this problem, i lock the enumeration, passing...
0
by: Alvin A. Delagon | last post by:
I have a multithreaded application that spawns threads which query a database server. During stress test I encountered some threads failing due "lost connection errors" and sometimes the actual...
22
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4....
2
by: shenanwei | last post by:
DB2 V8.2 on AIX, type II index is created. I see this from deadlock event monitor. 5) Deadlocked Connection ... Participant no.: 2 Lock wait start time: 09/18/2006 23:04:09.911774 .........
1
by: Ian | last post by:
I've just discovered the msclr::lock class in the C++ Support Library online documentation. This seems like a much cleaner way to implement thread protection than using...
1
by: chintuna4it | last post by:
How to implement a vb.net windows based Application lock like windows lock Lock means suppose we hv a option like lock in our form and press lock so our application is lock and then press enter then...
1
Dököll
by: Dököll | last post by:
Hey Ladies and Gents! I would like to permit only one user edit to a row in a database. I am a firm believer at having the db do most of the work but the app I am maintaining could use some...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.