How to allow a Query to Delete Records

[doyle60]

I have a query that looks like this:

SELECT ShipmentHeadertbl.*, ShipmentDetailtbl.ShipmentID
FROM ShipmentHeadertbl LEFT JOIN ShipmentDetailtbl ON
ShipmentHeadertbl.ShipmentID = ShipmentDetailtbl.ShipmentID
WHERE (((ShipmentDetailtbl.ShipmentID) Is Null));

I run in and get about 4000 records. I than delete those records. I
do this by selecting all the records and right clicking and deleting
with the mouse. It deletes them just fine. Than I close down the
query. But when I go back into the query the records are still there.
They were not deleted and Access didn't tell me so.

So than I change it to an actual Delete Query:

DELETE ShipmentHeadertbl.*, ShipmentDetailtbl.ShipmentID
FROM ShipmentHeadertbl LEFT JOIN ShipmentDetailtbl ON
ShipmentHeadertbl.ShipmentID = ShipmentDetailtbl.ShipmentID
WHERE (((ShipmentDetailtbl.ShipmentID) Is Null));

When I double click the query, I get this message:

"Couldn't delete from specified tables."

The "Help" button says this:
_____________________________________________

Couldn't delete from specified tables. (Error 3086)

You tried to delete data from one or more tables, but the deletion
couldn't be completed.
Possible causes:

· You don't 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 don't have write privileges for the
database file.
· In 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.
_____________________________________________

But I do have permissions and I set nothing to Read-only. The records
can be deleted by going directly to the table. I am the owner of the
database.

So what is up? What do I need to do to delete these records?
Suggestions?

Thanks,

Ma

 

[Barry-Jon]

Try this:

DELETE ShipmentHeadertbl.*, ShipmentHeadertbl.ShipmentID
FROM ShipmentHeadertbl
WHERE (((ShipmentHeadertbl.ShipmentID) In (SELECT
ShipmentHeadertbl.ShipmentID
FROM ShipmentHeadertbl LEFT JOIN ShipmentDetailtbl ON
ShipmentHeadertbl.ShipmentID = ShipmentDetailtbl.ShipmentID
WHERE (((ShipmentDetailtbl.ShipmentID) Is Null))));

Can't remember the full explanation but I believe the query you are
using is not an updateable query. Strange that the delete appears to
work in the first place. Hoping one of our learned MVP can shed some
light on it...

 

[John Vinson]

I have a query that looks like this:

SELECT ShipmentHeadertbl.*, ShipmentDetailtbl.ShipmentID
FROM ShipmentHeadertbl LEFT JOIN ShipmentDetailtbl ON
ShipmentHeadertbl.ShipmentID = ShipmentDetailtbl.ShipmentID
WHERE (((ShipmentDetailtbl.ShipmentID) Is Null));

I run in and get about 4000 records. I than delete those records. I
do this by selecting all the records and right clicking and deleting
with the mouse. It deletes them just fine. Than I close down the
query. But when I go back into the query the records are still there.
They were not deleted and Access didn't tell me so.

You're deleting records from the "many" side table - ShipmentDetailtbl
- in each grouped record; and because of the outer join, there's
actually nothing there to delete. The records from the "one" side
table don't get deleted.

So than I change it to an actual Delete Query:

DELETE ShipmentHeadertbl.*, ShipmentDetailtbl.ShipmentID
FROM ShipmentHeadertbl LEFT JOIN ShipmentDetailtbl ON
ShipmentHeadertbl.ShipmentID = ShipmentDetailtbl.ShipmentID
WHERE (((ShipmentDetailtbl.ShipmentID) Is Null));

When I double click the query, I get this message:

"Couldn't delete from specified tables."

Remove the reference to ShipmentDetailtbl in the DELETE clause:

DELETE ShipmentHeadertbl.*
FROM ShipmentHeadertbl LEFT JOIN ShipmentDetailtbl ON
ShipmentHeadertbl.ShipmentID = ShipmentDetailtbl.ShipmentID
WHERE (((ShipmentDetailtbl.ShipmentID) Is Null));

To do this in the grid, uncheck the Show checkbox under the field.

John W. Vinson[MVP]

 

[doyle60]

Actually the above didn't work. I solved the issue however. In the
queries that allowed me to delete records (I built 13 such queries),
the link was always an arrow pointing to a key field. In the ones that
didn't allow deletions, the arrow pointed to a non-key field.

I wondered what the unmatched query wizard would do. After building
it, I saw it allowed deletions. The difference in my handwritten ones
and the wizard built ones was that the wizard used SELECT DISTINCTROW
and I used just SELECT.

So I just had to put in the word DISTINCTROW in some of my queries,
like this:

SELECT DISTINCTROW Categorytbl.*
FROM Categorytbl LEFT JOIN OrderHeader ON Categorytbl.CategoryID =
OrderHeader.CategoryID
WHERE (((OrderHeader.CategoryID) Is Null));

And they all worked. Thanks,

Matt