Delete Query error after corrupt record (1 Viewer)

Zigzag · Oct 14, 2008

Hi,

I have been running a multi user application (30MB FE with 100mb BE) for the past 2.5years now without any problems until this morning. The application fell over due to a corrupt record in one of the tables (ARF table). I am 99% confident that this was due to a network card problem as it completely failed about an hour after the record was corrupted.

The DB was then compacted and repairing and the corrupt record was deleted.

The db appeared to run OK until a Delete Query was run that displayed the run time error 3086 Could not delete from the specified tables.

To get the system up and running and give myself a bit of thinking time I resorted to a backup of the backend data which worked fine.

After playing around and doing a bit of surfing I tried changing the Unique Records from No to Yes in the properties of the delete query and hey presto all was OK. I have never really paid much attention to this property before.

This now leaves me with a couple of question that I am hoping someone can help with.

In no particular order.

1, If this is being caused by corruption within one of my tables why cant I see it?

2, OK so I cant see it… how can I repair it?

3, Should I change the Unique Records property to yes in all of the Delete Queries or just the ones with more than one table?

4, Is setting the Unique Records to Yes masking a problem else where?

I have posted a stripped version to demonstrate.
( Open form1 and click the command button)

Garry

jal · Oct 15, 2008

I don't know enough to shed much light here - I just wanted to comment that I am surprised you got your DELETE Query working at all. I gave up on trying to do a Delete using a JOIN - because I was getting precisely the same error msg that you started getting. Here is what I recorded in my notes, a while back:

Someone gave me an example of how to do a DELETE using an INNER JOIN - but at RUN TIME IT THROWS THE ERROR "Could not delete from specified tables". Well, later he said that the table in the FROM clause must be joined on the FROM-table's primary key. But that route wasn't feasible for my particular situation.

jal · Oct 15, 2008

So, anyway, the althernative method is to use a subquery, maybe something like this:

DELETE FROM Orders
WHERE Orders.CustomerID IN
(Select C.CustomerID FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE Country = 'USA')

Zigzag · Oct 15, 2008

Hi Jal,

Thanks for the comments however I already have a workaround for the issue so I don’t really want to start changing the actual query structure.

What I would really like to know is why after 2.5 years should I have to change the properties of the query and will it have any future consequences?

Garry

ajetrumpet · Oct 15, 2008

Jal,

I was going to say that you might be "orphaning" a record. That is, deleting a record on the one side of a relationship, which I "think", results in that error! But, maybe that was not the case. I would probably say the same thing to you ZigZag, but it probably doesn't seem like that's the case here...

Zigzag · Oct 15, 2008

I had a quick scan through the records thinking along the same line but could not see anything. I deleted all the records out of both tables to prove that orphaned records could exist and the same error message appeared.

Strange!!!!!!

ajetrumpet · Oct 15, 2008

WOW. Unbelievable. But...I wasn't referring to the already orphaned records. I was referring to the error message appearing BECAUSE OF THE ATTEMPT to orphan the records. Do you know what I mean by that?

Zigzag · Oct 15, 2008

Adam,

Yep, 'if it would leave an orphan record it refuses to do it'

But if there are no records there it should not be a problem. Dont figure (but then again Access never did)

Do you have any views on changing the property Unique Records to Yes in the Query?

Garry

ajetrumpet · Oct 16, 2008

i'M CONfuSed ZiGzAg.

I'd have to see it to give you an idea. I'm not sure. Are you saying that if you delete a parent record without child records attached to it, you still get the message? Because something like that should not happen!

Delete Query error after corrupt record (1 Viewer)

Zigzag

Registered User.

Attachments

jal

Registered User.

jal

Registered User.

Zigzag

Registered User.

ajetrumpet

Banned

Zigzag

Registered User.

ajetrumpet

Banned

Zigzag

Registered User.

ajetrumpet

Banned

Similar threads

Users who are viewing this thread