#Deleted

[Stapes]

Hi

My client has a field on his database Data Type Memo. On one record
this field is displaying the text #Deleted. We have tried deleting
this text - it just comes back.
I have tried going in to the table directly and deleting the contents
of the field. Then when I go to close the table I get a message
"Record is deleted", which is odd, as I have not deleted a record.
Then when I go OK, it says "You can't save this record at this time"
etc. On reopening the table, the bloomin' message #Deleted is back
again.

Any ideas?

Stapes

 

['69 Camaro]

Hi, Stapes.

On one record
this field is displaying the text #Deleted. We have tried deleting
this text - it just comes back.

Your client has a corrupted record, possibly more than one. First, make a
backup of the database. Ensure the Track Name AutoCorrect option is turned
off. Create a new table with the same structure and relationships as the
table with the bad record. Create two queries, one to import the records
preceding the bad record into the new table, and one to import the records
following the bad records. Delete the table with the bad record and rename
the new table with the same name as the table you just deleted.

If you need help with the queries, please let me know by posting the table
name, the primary key name, and the primary key value of the record that
precedes the bad record (when the records are sorted), and the primary key
value of the record that follows the bad record.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Jerry Whittle]

The database is corrupt. Although you can't see it, memo fields are kept in a
seperate object in the database and not really in that table. Often the link
between the record and the memo field gets broken and you get the things that
you are seeing. That's why I avoid memo fields when possible.

Here's how to fix it. First make a complete copy of the database file(s)
just in case the cure is worse than the diesese. Next do a compact and
repair. If you are lucky, that fixes the problem. If not follow the following
steps.

Write down the primary key information for the bad record(s). Yes records.
It's very possible that more than one has the #Deleted problem and you need
to find them all.

Make a create table query based on the problem table and in the criteria
exclude the bad record(s). That gets all the good records out.

Next create an append query based on the bad table to the new table. Select
only the bad record(s) but don't include the memo field. That saves most of
the data.

Delete the bad table and rename the new table to the bad table's name. You
may need to set up relationships and indexes.

You aren't done yet. The database still has a corruption problem. Create a
new database file and import import all the objects from the bad database to
the new one. If something won't import, it's probably corrupt. You'll need to
set up things like security, relationships, and maybe a few other things.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

['69 Camaro]

Hi, Jerry.

just in case the cure is worse than the diesese.

I hope it's not cuntayjuss.

<g,d&r>

Gunny

 

[Jerry Whittle]

Looks like great minds think alike!

 

[Dirk Goldgar]

'69 Camaro wrote:
[snipped]

Gunny, please contact me when you get a chance. I sent you a message
the other day, but I don't know if you got it.

 

['69 Camaro]

Hi, Dirk.

I just checked that mailbox shortly before you wrote this message and found
your message from Monday. My apologies for not checking that mailbox
sooner. I just sent my reply a moment ago, so you should see it in your
inbox momentarily.

Gunny

 

[Stapes]

Hi

This is not as easy as it sounds.

I made a query to copy all the uncorrupted records. That worked.
Then one to copy the corrupt records - but without the corrupt field.
Trouble is, the autonumber key field at the beginning had been
renumbered?

How do I get the append query to keep the values in that key field?

Stapes