FYI: Avoiding write conflict errors in MS Access clients

[Andrew L. Gould]

This email is semi-off topic; but documents a problem and the server-side
solution when using MS Access as a client to many (all?) database servers. I
decided to post the problem/solution because the MS Access error message is
very misleading.

For those of you creating applications using PostgreSQL with MS Access
clients:

PostgreSQL will allow bit fields to hold null values. I consider this to be
valuable because a null boolean field tells me the information is incomplete
(a question hasn't been answered, for example); which is, itself,
information.

MS Access will allow users to insert records into linked tables with null
values in bit fields. Unfortunately, when the user tries to update the
record later, Access will interpret the null value as a data type violation
and will produce a "Write conflict" error message telling the user that
someone else has edited the record since the user started editing the record.
(I was the only user.) The user can drop the changes or copy them to the
clipboard; but no changes will be allowed to the data table.

This problem is prevented by using "not null" and assigning default values
when the table is created in PostgreSQL. If you are dealing with the problem
after the fact, use psql to change all of the null values in bit fields to 0
(or false, for boolean fields). Then use the "alter table" sql command to
add default values and the "not null" attribute to the table structure.

Best of luck,

Andrew Gould

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster