Could not delete from specified tables. (Error 3086)

[option]

Hey guys,

I'm trying to build a delete query that will remove records in my main table that match any stored in my history table. The problem is that I keep getting the following no matter how I try to build the thing:

Could not delete from specified tables. (Error 3086)
You tried to delete data from one or more tables, but the deletion could not be completed.

Possible causes:

You do not have permission to modify the table. To change your permissions assignments, see your system administrator or the table's creator.
The database was opened for read-only access. The database is read-only for one of these reasons:
You used the OpenDatabase method and opened the database for read-only access.
The database file is defined as read-only in the database server operating system or by your network.
In a network environment, you do not have write privileges for the database file.
In Microsoft® Visual Basic®, you used the Data control and set the ReadOnly property to True.
To delete the data, close the database, resolve the read-only condition, and then reopen the file for read/write access.

My gripe: No one else has this database, it's not open read only, and I have all the permissions I need for this thing since I built it. My delete query joins tblHistoric to tblWorkNow by account number, and should clear out everything from tblWorkNow that matches tblHistoric, but I get the above error.......any thoughts?

 

[boblarson]

(this is just a stab in the dark) - Did you perhaps link your archive with your current data in the relationships window and set Referential Integrity?

 

[DreamGenius]

I was searching for the solution to this same problem and, though Bob Larson's suggestion was not true in my case, I found that changing my query from two joined tables to embedded select queries, I got around the problem. It is however slower.

My queries changed from:

DELETE tblLeavers.*, tblCurrent.[Associate number], tblPrevious.[Associate number]
FROM (tblLeavers LEFT JOIN tblCurrent ON tblLeavers.[Associate number] = tblCurrent.[Associate number]) LEFT JOIN tblPrevious ON tblLeavers.[Associate number] = tblPrevious.[Associate number]
WHERE (((tblCurrent.[Associate number]) Is Not Null) AND ((tblPrevious.[Associate number]) Is Null));

which didn't work, to

DELETE tblLeavers.*, tblLeavers.[Associate number]
FROM tblLeavers
WHERE (((tblLeavers.[Associate number]) IN (SELECT tblCurrent.[Associate Number]
FROM tblCurrent) AND (tblLeavers.[Associate number]) NOT IN (SELECT tblPrevious.[Associate Number]
FROM tblPrevious)));

which does.

If anyone's able to correct my original query, I'd be grateful but I have a working solution for the time being.