How can I remove a record with (#deleted) in all fields 2

[SuePee]

I have a database with a table for drawing information. This table gets joined to the Change Report so that when the change report is printed, the drawing information is pulled from the table and added to the report.

There is a record in the drawing table that has "#Deleted" in all the fields. There are 4 more drawings also associated with this change record. But when the Change report is printed out, NO drawing information shows up.

I can delete this record at the table, but when I reopen the table it is there again.

I have 2 questions.
1. How in the heck do I delete this #Deleted record?
2. Where did it come from and why?

Thanks for any help you can give.

SuePee [Hairpull2]

 

[dhookom]

The #Deleted record came from some form of corruption. It could be a bad NIC or connection. The only method that I know of to resolve this (other than restoring from backup) is to create a new table with the same structure. Append records to the new table from the corrupt table where the primary key values are less than the corrupt record. Then do the same for primary key values that are greater than the corrupt record.

When this is finished, you need to rename the tables so the new table is used rather than the corrupt table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SuePee]

Thanks,

This has happened before, and from the same user. I am in MI and he is in FL. but it is only one record and only from that user. Could it be from a virus on his computer?

 

[dhookom]

I doubt this is caused by a virus. I haven't had this happen to me but know it has happened at our other mfg plants in the USA and they all run fairly strict AV software.

Tony Toews has a wealth of information on corruption and other Access stuff at

http://www.granite.ab.ca/access/tipsindex.htm.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SuePee]

dhookom,

How's this for thinking outside the box? I exported the whole table to Excel. Then I deleted the offensive record and imported the table back into Access and Voila! No corrupt table!

Thanks for the help

SuePee

 

[dhookom]

Good job. I hope Excel didn't mess with any values. It is generally quite reliable in maintaining the data in the same format as Access.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jsteph]

Just another thing to look at...this type of single-record corruption often comes from Memo fields--the memo field is a pointer to a separate disk section in the .mdb file, so they are easily corrupted--as dhookom said, something like a bad nic can cause that to happen.

Memo fields also degrade peformance. It looks like you're in Michigan and connect to Florida, so even over hi-speed, Access is slow and even slower when Memo's are involved, so with these two negative's in mind, I generally stay away from these unless there is no easy way around it. I know 255 can be short, but it is an elementary excercise to split/concat 2 or 3 these fields for data entry/display, respectively, when a bit more than 255 chars are needed.
--Jim

 

[SuePee]

Hi guys,

I am afraid to ask lest I look reallly stupid, but here goes anyway. What is "NIC"?

Thanks for all your help.

SuePee

 

[jsteph]

nic is the network card. If they're flaky data can get corrupted. Access just happens to be very unstable when it comes to communication over the network, it gets corrupted easier from things such as an unplugged network cable or flaky nic--most apps would obviously show some error, but then they'd be ok once the connection is restored. With Access, such interruptions can cause corruption.
--Jim