Sharing violation?
Experts,
An error message appeared today saying that another user is in the same record I am in and Access can not save the record.
I thought that MS Access could deal with 2 users in the same record and both can save?
thank you
An error message appeared today saying that another user is in the same record I am in and Access can not save the record.
I thought that MS Access could deal with 2 users in the same record and both can save?
thank you
ASKER
which do you advise to use: Optimistic or Pessimistic?
I checked the form and the property on Record Locks was "No Locks"
I changed it to "Edited Record"
If I select Edited Record then I woudl not think there would be any changes lost because once 1 person starts to edit then it locks that record. Is that correct?
btw on another issue, this Record Locks issue might be why I get a #error sometimes on a memo field. I think this is because there is >1 user trying to edit the same record as I get that msg box stating "you want to discard changes..." as you mentioned.
The #error I get puts the db in a state that cant be used and have to sometimes revert back to old frontend.
I checked the form and the property on Record Locks was "No Locks"
I changed it to "Edited Record"
If I select Edited Record then I woudl not think there would be any changes lost because once 1 person starts to edit then it locks that record. Is that correct?
btw on another issue, this Record Locks issue might be why I get a #error sometimes on a memo field. I think this is because there is >1 user trying to edit the same record as I get that msg box stating "you want to discard changes..." as you mentioned.
The #error I get puts the db in a state that cant be used and have to sometimes revert back to old frontend.
ASKER
When does Access put a record in an "Edit Mode"? Is it when you enter the record or when a change is actually being made?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jim: very well said. Good to know this about that memo field #error I get sometimes (rarely) .
<Most just use page locking with optimistic locks>
What is "Page Level" Locking? I do not see this option in Record Locks. I only see "No Locks"; "All Records"; "Edited Record". I am using 2k7
I think I will leave change from "No Locks" to "Edited Record" and see how that goes. I think I need some kind of locking to avoid that msg box stating "you want to discard changes..."
<Most just use page locking with optimistic locks>
What is "Page Level" Locking? I do not see this option in Record Locks. I only see "No Locks"; "All Records"; "Edited Record". I am using 2k7
I think I will leave change from "No Locks" to "Edited Record" and see how that goes. I think I need some kind of locking to avoid that msg box stating "you want to discard changes..."
<<What is "Page Level" Locking? I do not see this option in Record Locks. I only see "No Locks"; "All Records"; "Edited Record". I am using 2k7>>
This is a database level setting. Click the Office button, then the Access options button at the bottom. Then look in the advanced section, and scroll down till you see the advanced section.
This controls how the lock is placed. The setting in the form controls when the lock is placed. Note that the setting for all records, no locks, edited record in the above screenshot is only a default for when forms are created. The setting on the form in use is what is actually used.
<<I think I will leave change from "No Locks" to "Edited Record" and see how that goes. I think I need some kind of locking to avoid that msg box stating "you want to discard changes..." >>
That will do it no matter what level the locks are placed at.
JimD.
This is a database level setting. Click the Office button, then the Access options button at the bottom. Then look in the advanced section, and scroll down till you see the advanced section.
This controls how the lock is placed. The setting in the form controls when the lock is placed. Note that the setting for all records, no locks, edited record in the above screenshot is only a default for when forms are created. The setting on the form in use is what is actually used.
<<I think I will leave change from "No Locks" to "Edited Record" and see how that goes. I think I need some kind of locking to avoid that msg box stating "you want to discard changes..." >>
That will do it no matter what level the locks are placed at.
JimD.
ASKER
JimD: I think I will not change the Page Level locking setting as it does not work anyways and instead place the lock on the form as this setting on the form is more applicable. I think I will use "Edited Record" and see how it goes.
it has been an education. Thank you sir...have a good weekend.
it has been an education. Thank you sir...have a good weekend.
ASKER
JimD: I want to relay some feedback after making the change to Record Locks: Edited record.
I encountered an issue with this.
I got an error of the same one from my initial post of : cannot save record, currently locked by another user.
I narrowed this down to that Record Lock feature.
I removed it and placed NO LOCKS and then I could update.
I do have 2 forms with the same records source open on in tabbed mode and I think this might be why I get that error.
I did not change the ppty of the other forms I changed and left as Record Locks: Edited Record as I did not encounter errors on those other forms.
just fyi
I encountered an issue with this.
I got an error of the same one from my initial post of : cannot save record, currently locked by another user.
I narrowed this down to that Record Lock feature.
I removed it and placed NO LOCKS and then I could update.
I do have 2 forms with the same records source open on in tabbed mode and I think this might be why I get that error.
I did not change the ppty of the other forms I changed and left as Record Locks: Edited Record as I did not encounter errors on those other forms.
just fyi
<<I got an error of the same one from my initial post of : cannot save record, currently locked by another user. >>
That is possible because without Record Level Locking, you are locking a page of records at a time, and with the Edited Record, your locking the page at the start of the edit and for the duration of the edit. In terms of users bumping into one another, that's the worse situation you can have.
<<I removed it and placed NO LOCKS and then I could update. >>
That will help as the page won't be locked until the record is saved. But now your back to where you were; One user can loose their changes if two happen to start work on the same record.
If you that is a problem for you, then I would use page level locking for the database, Edit Lock for the form, and pad the record out so that a single record is more then half a page size.
You do that by adding text fields and filling them. JET 4.0 uses 4096 byte pages.
If you want to go this route, let me know and I'll help you figure out the current record size.
Jim.
That is possible because without Record Level Locking, you are locking a page of records at a time, and with the Edited Record, your locking the page at the start of the edit and for the duration of the edit. In terms of users bumping into one another, that's the worse situation you can have.
<<I removed it and placed NO LOCKS and then I could update. >>
That will help as the page won't be locked until the record is saved. But now your back to where you were; One user can loose their changes if two happen to start work on the same record.
If you that is a problem for you, then I would use page level locking for the database, Edit Lock for the form, and pad the record out so that a single record is more then half a page size.
You do that by adding text fields and filling them. JET 4.0 uses 4096 byte pages.
If you want to go this route, let me know and I'll help you figure out the current record size.
Jim.
ASKER
Jim:
thanks for the response.
I am pretty lost on this statement here:
<pad the record out so that a single record is more then half a page size.
I am interested in knowing how I can figure out the current record size.
pdvsa
thanks for the response.
I am pretty lost on this statement here:
<pad the record out so that a single record is more then half a page size.
I am interested in knowing how I can figure out the current record size.
pdvsa
<<I am interested in knowing how I can figure out the current record size. >>
THe JET page structure has never been officaly documented, but you can get a rough idea of the record size by adding up the length for the different data types. Here's how it roughly breaks out:
1. Seven bytes per record for record overhead.
2. One byte variable-length field overhead for each Text, Memo, Hyperlink, and Long Binary (OLE Object) field.
3. One additional byte for every 256 bytes of the total space occupied by all Text, Memo, Hyperlink, and Long Binary (OLE Object) fields
4. One byte fixed-length field overhead for each Yes/No, byte, Integer, Long Integer, AutoNumber, Single, Double, Currency, and Date/Time field
For the fields:
Byte 1 byte
Integer 2 bytes
Long Integer 4 bytes
Single 4 bytes
Double 8 bytes
Currency 8 bytes
AutoNumber with FieldSize property set to Long Integer Long Integer 4 bytes
AutoNumber with FieldSize property set to ReplicationID GUID 16 bytes
Yes/No Boolean 1 bit
Date/Time 8 bytes
Text Variable
Memo Long 16 bytes
OLE Object Long 16 bytes
Hyperlink Long 14 bytes
Now the OLE and memo fields are special in that they are typically stored seperate from the "fixed" portion of the record *unless* they are less than 30 bytes, then they are stored with the rest of the record. I would ignore that and for these fields, just count the overhead and pointers for the fixed record size. That will get you close enough.
Your goal then is to pad the records with text fields so that the size exceeds 2048 bytes (half a page). When you figure out how many text fields you need, modify the table def and make sure you set the default value to all "x"'s. As text fields are stored variable length, you must put something in there or you'll end up with a one byte field.
So if your current record is 224 bytes, then you need padding of 1825 bytes ((4096/2) - 224) to get one record to occupy more then half a page. That would mean adding 8 text fields and filling them all with 255 bytes of data.
Jim.
THe JET page structure has never been officaly documented, but you can get a rough idea of the record size by adding up the length for the different data types. Here's how it roughly breaks out:
1. Seven bytes per record for record overhead.
2. One byte variable-length field overhead for each Text, Memo, Hyperlink, and Long Binary (OLE Object) field.
3. One additional byte for every 256 bytes of the total space occupied by all Text, Memo, Hyperlink, and Long Binary (OLE Object) fields
4. One byte fixed-length field overhead for each Yes/No, byte, Integer, Long Integer, AutoNumber, Single, Double, Currency, and Date/Time field
For the fields:
Byte 1 byte
Integer 2 bytes
Long Integer 4 bytes
Single 4 bytes
Double 8 bytes
Currency 8 bytes
AutoNumber with FieldSize property set to Long Integer Long Integer 4 bytes
AutoNumber with FieldSize property set to ReplicationID GUID 16 bytes
Yes/No Boolean 1 bit
Date/Time 8 bytes
Text Variable
Memo Long 16 bytes
OLE Object Long 16 bytes
Hyperlink Long 14 bytes
Now the OLE and memo fields are special in that they are typically stored seperate from the "fixed" portion of the record *unless* they are less than 30 bytes, then they are stored with the rest of the record. I would ignore that and for these fields, just count the overhead and pointers for the fixed record size. That will get you close enough.
Your goal then is to pad the records with text fields so that the size exceeds 2048 bytes (half a page). When you figure out how many text fields you need, modify the table def and make sure you set the default value to all "x"'s. As text fields are stored variable length, you must put something in there or you'll end up with a one byte field.
So if your current record is 224 bytes, then you need padding of 1825 bytes ((4096/2) - 224) to get one record to occupy more then half a page. That would mean adding 8 text fields and filling them all with 255 bytes of data.
Jim.
ASKER
wow...super techo...I think I will pass on that one. I would grow grey hairs. I think I will be more careful and have a the form that I am having an issue with as MODAL. I think I get an error because I might have clicked to another form and in essence haveing 2 forms open with the same or similar record source.
I know this is not an ideal setup to have but I the form was getting too clulttered and I needed to put other records on teh other form but the issue was when I made a change on the other form that had the same record source as the form I came from and I did not close the form where I made the change (it is popup) and navigated back to the form where I came from...I got the error msg.
If I change the ppty of the form to MODAL then I can not click anywhere else unless closing that form first.
I am rambling... too much coffe this am.
thanks for the advice on that one! You obviously have a lot of experience.
I know this is not an ideal setup to have but I the form was getting too clulttered and I needed to put other records on teh other form but the issue was when I made a change on the other form that had the same record source as the form I came from and I did not close the form where I made the change (it is popup) and navigated back to the form where I came from...I got the error msg.
If I change the ppty of the form to MODAL then I can not click anywhere else unless closing that form first.
I am rambling... too much coffe this am.
thanks for the advice on that one! You obviously have a lot of experience.
Depends on the lock options you have set, and even then, it's not always possible. But even when you can, someone is going to loose their changes. Two users both can't be in the same record at the same time and both have their changes saved. No RDBMS allows that.
The locking is controlled by the form BTW with the Record Locks Property. No Locks means you get optimistic locking, or a lock just while the page in the DB is updated. Edited Record means you get pessimistic locking; the lock is placed on the record as soon as an edit starts.
If you use the first and two users on the same record, the second users that saves will get a dialog asking if they want to discard their changes, overwrite the first users, or cancel the save.
JimD.