Re: Record is deleted error
Gary Walter
"mattieflo" wrote:
>
> I have a table called Orders that keeps track of a list of Orders for a
> week. I'm trying to append to a table called Orders Archive which keeps
> track
> for all orders ever received. For some reason, when I try to execute the
> append query, I get the "record is deleted" message. I'm not sure why its
> happening, because the query used to work up until a few weeks ago. The
> two
> tables are actually SQL Server tables that I'm using with an access front
> end. Does anyone know what the culprit is behind this message?
>
Hi Mattie,
It was working, now it doesn't...what changed?
--Did you "just refresh" links which can lose unique
index(es) that you set when you first linked to table(s)?
Easiest test, delete links and relink (not "refresh link").
--Did you recently decide to use a Float field
as a/or part of a unique index?
--Do your SQL tables each have a datatype Timestamp field?
just some ideas to check....
good luck,
gary
Gary Walter
http://support.microsoft.com/default.aspx?scid=kb;en-us;128809
***quote***
After Microsoft Access performs an insert or an update of a linked ODBC
table, it uses a Where criteria to select the record again to verify the
insert or update. The Where criteria is based on the unique index. Although
numerous factors can cause the select not to return any records, most often
the cause is that the key value Microsoft Access has cached is not the same
as the actual key value on the ODBC table. Other possible causes are as
follows:
. Having an update or insert trigger on the table, modifying the key value.
. Basing the unique index on a float value.
. Using a fixed-length text field that may be padded on the server with the
correct amount of spaces.
. Having a linked ODBC table containing Null values in any of the fields
making up the unique index.
These factors do not directly cause the "#Deleted" error message. Instead,
they cause Microsoft Access to go to the next step in maintaining the key
values, which is to select the record again, this time with the criteria
based on all the other fields in the record. If this step returns more than
one record, Microsoft Access returns the "#Deleted" message because it does
not have a reliable key value to work with. If you close and re-open the
table or choose Show All Records from the Records menu, the "#Deleted"
errors are removed.
Microsoft Access uses a similar process to retrieve records from an linked
ODBC table. First, it retrieves the key values and then the rest of the
fields that match the key values. If Microsoft Access is not able to find
that value again when it tries to find the rest of the record, it assumes
that the record is deleted.
***unquote***
Gary Walter
have a field in SQL Server that has
a default value...
what are you talking about Gary?!
Say in table OrdersArchive is a field
called BadIdea with default 0.
And you tell Access this field should
be part of unique index when you link
to OrdersArchive.
Everything goes swimmingly (I almost
drowned once so don't fully understand
how anything "swimmingly" is good, but...)
until you try to append a record where
BadIdea is Null.
Does OrdersArchive.BadIdea then get changed to 0?
I don't know for sure, but speculate what
would happen if it did...
When Access tries to "reuse cached unique index"
to verify insert, it cannot find null BadIdea
(its been changed to 0), and then assumes
*record was deleted*.
"Gary Walter" <ga...@wrotein.msg>
Gary Walter
default values for I don't believe
it is probably appropriate in your case
(it does though maybe give you
an insight into the "mechanism"
that produces the deleted record msg).
The realms of Null vs Empty and
Access vs SQL Server can be blurry
(especially if one doesn't have the time
to get it right like this morning), but...
I think the "default value" came into
play where you might have a form bound to
OrdersArchive and attempted to save a new
record w/o giving BadIdea a value (i.e., empty).
good luck,
gary
"Gary Walter" <ga...@wrotein.msg>
mattieflo
updated. Thanks for your time!
Matt