Why do I get #Deleted after inserting a row in Oracle using Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access 97 database that links to an Oracle database. I insert
records into an Oracle table using a Form/SubForm via a Datasheet. I have a
problem when running this application in Access XP. Whenever a record is
inserted via the Datasheet, Access returns #Deleted in all of the row cells.
If I close and reopen the form, the inserted data is displayed properly. Why
is Access displaying #Deleted and how do I fix it?
 
One solution is to expose a timestamp field in the sub-form. In fact, for
all tables, and queries you SHOULD include, and expose the timestamp field
from the oracle table. (this allows ms-access, and in fact a lot of other
software to figure out when, how, who, and the last time the data was
changed. Without this information, ms-access can get confused.

So, give the above a try.

The other tip of course is to always have a primary key field exposed. This
again applies to all tables, as ms-access works a lot better when you have a
unite (primary key) id for each table.
 
Hi.
I have an Access 97 database that links to an Oracle database. I insert
records into an Oracle table using a Form/SubForm via a Datasheet. I have a
problem when running this application in Access XP.
Click to expand...

Why
is Access displaying #Deleted and how do I fix it?
Click to expand...

Are you using a composite primary key in the Oracle table where at least one
of the records has a null value in at least one of these composite key
columns? Jet will choke on this. The solution to this problem is to either
eliminate the null values or create a surrogate key with a sequence and a
trigger to update the sequence, then drop the link to the Oracle table and
recreate the link using this surrogate key as the primary key.

Some older Oracle drivers aren't completely compatible with Jet 4.0. First,
make sure that the computer has the latest service packs for Jet 4.0 (SP-8)
and MDAC (v2.8, SP-1). You may need to experiment with other drivers to see
if another one works better than the one you are using.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Back
Top