I posted about this some time back.
MS have published an article on their website
http://support.microsoft.com/support/kb/articles/Q128/8/09.asp
reading
ACC: "#Deleted" Errors with Linked ODBC Tables
----------------------------------------------------------------------------
----
The information in this article applies to:
Microsoft Access 2.0
Microsoft Access for Windows 95, version 7.0
Microsoft Access 97
----------------------------------------------------------------------------
----
SYMPTOMS
When you retrieve, insert, or update records in a linked ODBC table, each
field in a record contains the "#Deleted" error message. When you retrieve,
insert, or update records using code, you receive the error message "Record
is deleted."
CAUSE
The Microsoft Jet database engine is designed around a keyset-driven model.
This means that data is retrieved, inserted, and updated based on key values
(in the case of a linked ODBC table, the unique index of a table).
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.
RESOLUTION
The following are some strategies that you can use to avoid this behavior:
Avoid entering records that are exactly the same except for the unique
index.
Avoid an update that triggers updates of both the unique index and another
field.
Do not use a Float field as a unique index or as part of a unique index
because of the inherent rounding problems of this data type.
Do all the updates and inserts by using SQL pass-through queries so that you
know exactly what is sent to the ODBC data source.
Retrieve records with an SQL pass-through query. An SQL pass-through query
is not updateable, and therefore does not cause "#Delete" errors.
Avoid storing Null values within any field making up the unique index of
your linked ODBC table.
MORE INFORMATION
Note: In Microsoft Access 2.0, linked tables were called attached tables.
Steps to Reproduce Behavior
Open the sample database Northwind.mdb (or NWIND.MDB. in Microsoft Access
2.0)
Use the Upsizing Tools to upsize the Shippers table.
NOTE: This table contains an AutoNumber field (or Counter field in Microsoft
Access 2.0) that is translated on SQL Server by the Upsizing Tools into a
trigger that emulates a counter.
Open the linked Shippers table and enter a new record. Make sure that the
record you enter has the same data in the Company Name field as the previous
record.
Press TAB to move to a new record. Note that the "#Deleted" error fills the
record you entered.
Close and re-open the table. Note that the record is correct.
=======================================================================
My database consists of two linked tables. One table has a primary key. The
second table has a different primary key, and a field that is a foreign key
which comes from the primary key of the first table.
The problem I have experienced was thought to be due to the use of serials
(similar to Access's Autonumber) to generate unique primary key values,
particularly when adding data to the second table which is in a 1 to many
relationship with the first table. I tried generating my own unique PK
values and alternately posting the new record before editing it (forces the
serial to be generated and stored in the record so that it is available to
the secondary table). The second approach has not been totally successful,
not sure about the first but will experiment with that. Does anyone have
opinions on ways of resolving these issues?
=======================================================================
Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/
Peter replied, Repent and be baptized, every one of you, in the
name of Jesus Christ for the forgiveness of your sins. And you will
receive the gift of the Holy Spirit. The promise is for you and
your children and for all who are far offfor all whom the Lord our
God will call. -- Acts 2:38
http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010304
=======================================================================
Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/
