Problem when using same record

[Guest]

Hi. I've a a mini-program. it's dived in front and Back-end. For the same
table I have two diferent Form. Those forms access to the same table using a
diferent query. In both form I insert data into the table, but I never delete
a record.

My problem is when teo diferent users, are accesing the same record from
diferent forms, it returns an error. What should I do?

Marco

 

[Arvin Meyer [MVP]]

Two people cannot edit the same record, or the same page of records at the
same time. Go to Tools ... Options ... Advanced tab and make sure the edited
record radio button is chosen. That will send a warning back to the second
user.

 

[Jamie Collins]

Two people cannot edit the same record, or the same page of records at the
same time.

Really? Even though Jet 4.0 has row-level locking? What about using an
ADO recordset with optimistic locking?

Jamie.

--

 

[Guest]

Hi. I did what you told me, but it's not working. What it does is if two
people are using the same record, but not the same field it lets save the
first person changes but when the second is changing it only retunrs a
message with 3 options. Save this record and delete the preciuos changes.
Cancel and drop changes.

My default file format is Acess 2000 but my office is 2003. I even made the
change you told me in bak-end and all fronts-ends.

Any idea?

Marco Silva.

 

[Guest]

Hi. could you send me a little examplke of ADO connection?

Marco

 

[Jamie Collins]

Hi. could you send me a little examplke of ADO connection?

Well, that might not be a good idea. Better wait for Arvin Meyer to
reply (note I'm asking questions myself <g>).

Jamie.

--

 

[Arvin Meyer [MVP]]

Really? Even though Jet 4.0 has row-level locking? What about using an
ADO recordset with optimistic locking?

No matter which locking scheme is used, 2 people cannot edit the same record
at the same time. To use row-level locking with DAO, you have to jump
through a few hoops and use ADO first:

http://support.microsoft.com/kb/306435/en-us

I prefer to use DAO with Jet, and unless I'm working with an ADP, or a VB
app against SQL-Server, I rarely use ADO. I haven't had any locking problems
yet. Because of the way I design my front-ends, it is highly unlikely that 2
people will ever edit the same page of records anyway.

 

[Arvin Meyer [MVP]]

You can use row-level locking, but you'll need to use ADO first:

http://support.microsoft.com/kb/306435/en-us

You can also pad out your records so that they are slightly over 1K each.
That way each record will use 1 page and you won't have a problem with
anyone trying to edit a different record on the same page. Lastly, you can
lock the table (that's usually only done for large appends).

Again, 2 people should never be allowed to concurrently edit the same record
anyway. What good is data that does not have integrity?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Jamie Collins]

pad out your records so that they are slightly over 1K each.
That way each record will use 1 page and you won't have a problem with
anyone trying to edit a different record on the same page.

What about using a natural key (or perhaps even a random - not
incrementing - autonumber) PRIMARY KEY to improve concurrency because
the likelihood of two rows being on the same page decreases when the
file is compacted (contrarily without a compact the rows are in
date/time inserted order and there are more likely to be clashes)?

Thanks again,
Jamie.

--

 

[Arvin Meyer [MVP]]

Most of the better candidates for natural keys are smaller dataset which
generally are static and don't get edited, like state abbreviations and
country codes.

I do use random autonumbers in web apps where the client creates the number,
or replication where the client also creates the number. The one thing that
I dislike about random autonumbers is that the data typically requires
adding a timestamp to be able to find the last 20 records added, etc. This
is extremely important when debugging and when correcting errors.

 

[Jamie Collins]

Most of the better candidates for natural keys are smaller dataset which
generally are static and don't get edited, like state abbreviations and
country codes.

Most of the worst natural keys change frequently. The majority of
natural keys change only rarely. It's called the Normal distribution
;-) It doesn't change the fact that the natural key (where one exists)
needs to be enforced in a table to ensure data integrity. Whether a
natural key should be used for PRIMARY KEY designation on a Access/Jet
table is a whole other (OT) matter.

Jamie.

--