Delete query refuses to run (looks good in select mode)

  • Thread starter Thread starter Jan
  • Start date Start date
J

Jan

I have a simple query where I want to delete records existing in
another table. Preview works excellent, but it refuses to delete the
records and the "help" function of Access suggests that perhaps the
file is write protected. This is incorrect, I can perfectly well delete
records manually in this stand-alone MDB in Access2003/XP.

There's no primary key (unique), is this needed to delete records? I
don't need it to be unique as I simply want any records with a match on
a field to be deleted.

If Access is impossible to use for deletion, is there a work-around
like find all records with the "key" not matching? Then I can rename
the make-table result and delete the original table to achieve the same
as deletion.

What's the trick to making a delete query to perform a deletion?

DELETE [082712pg1].*
FROM 082712pg1 INNER JOIN 0825B2exist ON [082712pg1].KeyValue =
[0825B2exist].KeyValue;
 
I think your table name starts with a digit (not letter Oh), so the name
must be enclosed in square brackets:
DELETE FROM [082712pg1] INNER JOIN [0825B2exist]
ON [082712pg1].KeyValue = [0825B2exist].KeyValue;

If that still doesn't work, try a subquery instead of the join:
DELETE FROM [082712pg1]
WHERE EXISTS
(SELECT KeyValue FROM [0825B2exist]
WHERE [0825B2exist].KeyValue = [082712pg1].KeyValue);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Back
Top