Haunted by Appen/Update query bug

  • Thread starter Thread starter Pavils Jurjans
  • Start date Start date
P

Pavils Jurjans

Hallo,

I am haunted by the sql append/update query bug (on Access 2000):

If I have a simple table with one id field, and one memo field, then trivial
append/update queries that attempt to add data to the memo field, may fail
with ADO error "The changes you requested to the table were not successful
because they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain duplicate
data, remove the index, or redefine the index to permit duplicate entries
and try again." In the Access environment I am informed that adding certain
entry would result in key violation.

Somewhat cloe to my problem is this kb article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;296389

First, I used only append queries, and they failed time to time. I
discovered that it happens if the text being added is very similar to some
existing record in the table. Then I tried to use append/update combination,
but also that fails.

So, I desperately hope there is some workaround for this problem...

Thank you for any hints,

-- Pavils
 
Hey, I actually accidently found a fix!

Some time ago that memo was a text field. In those days, it has an index
attached that didn't allow duplicate values. When specs were changing, I
changed the field type from text to memo, and assumed that Access would
delete the index. Especially because memo fields are not supposed to have
any indexes. But, in fact, Access somehow kept an eye on incoming records
and still didn't allow "almost" duplicate values. I haven'd done too much
testing what means "almost", but I assume that first 255 characters, case
insensitive, are indexed and thet serves to reject some records that differ
in case or differences are above 255 characters.

But, how I fixed it:

I changed the field type to OLE object (I was so desparate that I was ready
to switch to holding binary data instead of text), and Access warned me that
this will result in removing some indexes! When I clicked Ok, and then
switched back to Memo type, the problem was gone!

This is obviously a bug, and I don't complain, since I use Access 2000
(:-/), but seems that some aspects of it live also in XP version of Access.

-- Pavils
 
Back
Top