"Deleted" Records Still Appear in Tables

[BlaineR]

Hi...I hope you can help!

I created an Access database that has a form with a subform. Everything has seemed to be fine until now, when a problem was detected with the reporting functionality. After much (!!!) investigation, I realized that there seems to be a problem with deletions. I think - though I'm not sure - that the problem arises when the user deletes the first record in the subform of a given form. I have also noticed that although I set the key field for the subform entries to be an autonumber, there are actually repeated autonumbers in my subform table.

I tried to delete the rows with the repeated (key field) autonumbers, but Access doesn't let me. It appears to, but they magically return.

I looked in the "subform table", and even though records do not appear in the subform after being deleted, they occasionally (not always) appear in the subform table.

However, there, all the fields contain "#Deleted" rather than a value. And, when I attempted to delete these rows from the table itself, it allowed me to do so, gave me the confirmation notice, and seemed to delete the "ghost" record. Yet, when I closed the table and then opened it again, the record appeared there once again with the "#Deleted" value in all the fields.

I think my queries are choking on this bad data when I try to run my reports, but I can seem to get rid of the bad data, no matter whether I delete the form or the subform entries.

I confirmed that the relationships were set up properly, so that you'd expect the proper cascading deletions of the subform. But that's not happening.

Please help!

Thanks very much,
Blaine

 

[cmmrfrds]

Did you do a Compact and Repair after deleting the records? If not, try it and see if that fixes the problem.

 

[BlaineR]

Hi cmmrfrds,

Thanks very much for the suggestion. I will try the compact and repair, and let you know what happens.

Just wondering, though...to have to do so after every deletion seems like a lot of "overhead" maintenance. Is that normal to expect with Access?

Thanks again!

Blaine

 

[cmmrfrds]

No, it is not normal to need to compact after deletion but if you do a lot of deletes it helps to keep the database tuned. I would very carefully check the logic flow of how the deletions are done and look for something that could cause the problem.

I am not sure how cascade delete works in Access, I never use cascade on delete since I like to control that part myself. Maybe experiment with the cascading part.

 

[WildHare]

I confirmed that the relationships were set up properly, so that you'd expect the proper cascading deletions of the subform. But that's not happening.

Cascading deletes work by removing the MANY-side records that are related to a ONE-side record that is deleted. If you're deleting off a subform, I'm assuming that the subform is showing the MANY side guys. So cascading deletes are not an issue. Cascade goes DOWN, not UP..

It sounds very much like you have an integrity problem with your database structure - I would tread carefully until you have solved this problem.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[BlaineR]

Hi,

Well, I used the compact and repair function today and guess what! Problem solved. Thanks VERY much for the tip, cmmrfrds!!

WildHare, thanks, too for the advice. I will have another look at the integrity issue, although the table structure and relations is a pretty simple one. I agree with your definition of "cascade"...I probably wasn't clear. I meant that I could clear up my "ghost" records in the subform (the MANY) even by deleting the entire form (the ONE). In any case, your comments are right on and I will re-check things. Thanks.

I appreciate all the help very much. Thanks to you both.

Blaine