Write after write conflicts with unbound forms

[robert d via AccessMonster.com]

I've been developing my application using almost exclusively unbound forms.
I haven't really given much thought to potential write after write conflicts
for a couple of reasons:

1) Most of the users of the app have no permissions to add or update data to
the central database. There is a temporary database that is created for each
user, and all users add and delete data to the tables in the temporary
database. But there wouldn't be write conflicts there because that temp dB
is created for each logged on user.

2) Changes in the data stored in the main tables occur typically once or
twice a month, and the number of people who can make these changes is
relatively small; less than 5.

Still I now suspect that my app could have write after write conflicts
against the main tables on the server because of the almost exclusive use of
unbound forms.

My explanation of write-after-write conflicts is as follows:

1) User 1 and 2 retrieve the same record
2) User 1 makes a change to the data and saves it. User 2's record as
displayed on the form is now out of synch with the data in the table on the
server.
3) User 2 makes a change to his version of the data and attempts to save the
change.

If I understand this scenario correctly, User 2 will overwrite the changes
made by User 1 and will not have known that User 1 made changes.

Finally, this issue only surfaces with editing records and not adding records.
I guess with adding records there is no conflict. If User 2 tries to add a
record, after User 1 has added a record, and the User 2 record just happens
to have the same primary key as User 1's addition, then a primary key error
should appear. Am I correct in this assumption?

Am I correct in my assessment of potential write after write problems with my
unbound application.

 

[Pat Hartman\(MVP\)]

Yes you are correct. There is the potential for write after write
conflicts. There is also the potential for other types of validation
errors. This is always a problem when the editing of a record is separated
in time from the updating of the primary datasource. If something changes
after a record has been validated, when you try to apply the change, it will
fail.

I am curious as to why you are using unbound forms. The biggest strength of
Access is its bound forms and reports. You have created an enormous amount
of work for yourself and there is really no way out. You need to understand
exactly what a bound form does and replicate every aspect of it.

If your table has a datestamp that is maintained by the database engine such
as SQL Server, your update routine can always compare the datestamp for the
record currently in the table with the datestamp of the updated record. If
they are the same, no one has changed the table record and so it is OK to
apply your update but if they are different, that means that some one
retrieved the table record AFTER you did but replaced it BEFORE you did.
Each time a record is updated, the db engine updates the datestamp. So, you
know that the record in the table is somehow different from what it was when
you retrieved it and if you update the table, you could be wiping out
someone else's change.

 

[robert d via AccessMonster.com]

Pat:

Thanks for your response. I don't have a good answer for you on the unbound
form question. I wanted to develop my app in VB6, but was told that a major
potential client no longer allows executables to be installed on user PCs
(after I had completed a substantial amount of work). They do have Access so
I went that route, without understanding the full power of Access.

Anyway, it's way too late to switch.

Was I correct on my assessment of adding records. Namely, that with adding
records, I don't have to worry about the issue mentioned in this post because
a primary key error will take care of any potential conflict.

I posted representative code that I think I need to implement in a post
entitled:

"Transaction processing for unbound forms using DAO"

I'd appreciate it if you could check it out. It's similar to what you
suggest, but I decided to use the method recommended by Mary Chipman in her
Access/SQL Server book (instead of TimeStamp she suggests a ConcurrencyID).
She used ADO and I'm using DAO so I added the BeginTrans, etc.

Thanks for your help.

Yes you are correct. There is the potential for write after write
conflicts. There is also the potential for other types of validation
errors. This is always a problem when the editing of a record is separated
in time from the updating of the primary datasource. If something changes
after a record has been validated, when you try to apply the change, it will
fail.

I am curious as to why you are using unbound forms. The biggest strength of
Access is its bound forms and reports. You have created an enormous amount
of work for yourself and there is really no way out. You need to understand
exactly what a bound form does and replicate every aspect of it.

If your table has a datestamp that is maintained by the database engine such
as SQL Server, your update routine can always compare the datestamp for the
record currently in the table with the datestamp of the updated record. If
they are the same, no one has changed the table record and so it is OK to
apply your update but if they are different, that means that some one
retrieved the table record AFTER you did but replaced it BEFORE you did.
Each time a record is updated, the db engine updates the datestamp. So, you
know that the record in the table is somehow different from what it was when
you retrieved it and if you update the table, you could be wiping out
someone else's change.

I've been developing my application using almost exclusively unbound
forms.

[quoted text clipped - 47 lines]

my
unbound application.

 

[Albert D.Kallal]

Was I correct on my assessment of adding records. Namely, that with
adding
records, I don't have to worry about the issue mentioned in this post
because
a primary key error will take care of any potential conflict.

Sure.....no one else could have retrieved the record already..since you not
yet saved it yet...so, I can't imagine a problem.

Remember, you can turn on record locking in bound forms. Two users can then
load the SAME form, and who ever starts editing gets the lock.

The other user will see a "ghostbuster" slash (circle with a slash in it)
placed on the record selector bar. In other words, the 2nd person will NOT
be able to modify the record. So, both can browse, and look at the record at
the SAME TIME. However, the instant one user starts to type, or modify the
record, it is locked..and then the other user will see a lock symbol on the
record selector. (and, if they try and edit..they get a beep to this
effect).

So, the collision, and locking is built right into ms-access *if* you had
used bound forms....