Access 2003: #Deleted when adding a record

[newToAccessDev]

Hello all,

I've converted an application from Access97 to 2003. My problem is that in Access 2003, when adding a new record to a particular table by entering the required data on a form then clicking the "Save" button, "#Deleted" shows up in all the fields on the form. However, when I go under the Access "Tables" tab and open the corresponding table, the record I tried adding shows up just fine. Also, back on the form, if I click the "Find" button and enter the info for the new record on the search form that comes up, the record data comes up fine on the form where the "#Deleted" text had appeared.

The table I'm trying to add to is a linked Oracle table. I've done some research and found an article explaining a similar situation with linked SQL Server tables, and which says that you can resolve the problem by installing the latest Microsoft Jet 4.0 service pack that contains an updated version of the Microsoft Jet 4.0 database engine.

Can anyone tell me what the latest service pack is (is it service pack 8)? Also, how can I tell whether I already have this on my system (I think I already have the SP8, but am not sure)? Finally, any alternative ideas of how this problem can be solved?

I'm very new to Access development, so any help will be greatly appreciated!

Thanks!

 

[mikebaldam]

Hi,

I've had similar problems working with onlein linked MySQL DBs.

Normally its due to the data being changed by another user/DB and can be helped with setting some Database options...

i.e. Tools - Options - Tables/Queries and check SQL SERVER COMPATIBLE and test again
also Tools - Options - Advanced then check your DDE Requests and Refresh Intervals

It might be worth checking for any timestamp fields being used by linked tables too, these often cause problems (well the MySQL ones and some SQL ones do) and swap them to being just string format to test.

Hope one of those help

 

[Banana]

I strongly recommend that you read the whitepaper about Jet and ODBC connectivity, which goes into details about how #deleted can show up and what can be done plus a whole lot other information that will be helpful.

You can get the whitepaper there. It was written for 3.0, but should still be relevant for 4.0

 

[newToAccessDev]

Hi mike,

Your first suggestion (setting SQL SERVER COMPATIBLE option) unfortunately didn't work -- after making this change, the search functionality on that form no longer worked, and the "#Deleted" message still appeared in the form's fields when adding a new record. Perhaps the problem here is that my tables are Oracle ones, and not MySQL??

Under Advanced tab: DDE refresh is enabled, DDE requests are ignored, and the Refresh interval is 60 seconds. What changes do you think might help here? Also, what's DDE??

Anyone know anything about the MS Jet 4.0 service pack option (i.e. whether installing the latest one might solve the problem)?

Thanks for the help!

 

[Banana]

My best guess for DDE.

IINM, those settings has nothing to do with the #deleted symptoms; the issue is that Access is confused because it thinks the key has apparently changed.

If you read the whitepaper, you will see how Access will check for whether a key has changed by requerying for a exact match but if the query doesn't have all columns or somehow alter the data, it looks as if it was deleted and it cannot distinguish from a genuine deletion.