Delete Query failure

[Guest]

I have two tables containing identically structured records. I wish to
delete the records in one table which have the same invoice numbers as the
records in the other. I thought that this could be simply done with a delete
query, joining the invoice fields between the two tables but the delete query
will have none of it, returning an Error 3086 ("Could not delete from
specified tables.") The Help file suggests that the problem lies with
inappropriate permissions or that one or both of the tables is set read only.
This is not however the case. If one of the tables is removed from the
delete query, the other can be deleted quite normally. It is only when both
tables are displayed simultaneously that the problem occurs, whether or not
the connection is show by a join or as a criterion. The records for deletion
are clearly identified in the query properties as those from the one table
from which the deletion is to take place.

 

[Guest]

Try and change the UniqueRecords Property of the query to yes

This usually happen when you join the tables not by the key

 

[John Spencer]

Make sure you are not referring to fields in both tables in the delete clause.

DELETE TableA.FieldA, TableB.FieldC
FROM TableA INNER JOIN TableB
ON TableA.InvoiceNumber = TableB.InvoiceNumber

The above will fail and will give you the error message you specified.

DELETE DistinctRow TableA.FieldA
FROM TableA INNER JOIN TableB
ON TableA.InvoiceNumber = TableB.InvoiceNumber

The above should work.

If you are using the query grid, UNCHECK Show for any fields that are in the
table you do not want to delete from.

 

[Guest]

Thanks very much for that. Your suggested syntax did not work as stated but
a modification of it did. The required form turned out to be :-
DELETE DISTINCTROW TableA.Invoice_No,TableA.*
FROM TableA INNERJOIN TableB
ON TableA.Invoice_No = TableB.Invoice_No

I can't claim to have thought that up myself. When your version was run, an
error was generated indicating that the table from which the deletion was to
be made should be specified. Once that was done in the delete query design
window, the necessary clause was automatically added to the SQL and all was
well.

I still find SQL statements arcane and I cannot see how the required form of
statement can be generated automatically in the query design window. Had you
not pointed out that DISTINCTROW needed to be added, I very much doubt that I
would ever have stumbled on the required query form. Still, thanks to your
input I can now proceed.

 

[Guest]

Hi Peter

As I mentioned in my post, you need to change the UniqueRecords Property of
the query to yes, that would add the DISTINCTROW to the query, so you wont
have to type it yourself

To get to the query properties, open the query in design view, press
Alt+Enter, or select from the menu bar (view > properies)

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

Thanks very much for that. Your suggested syntax did not work as stated but
a modification of it did. The required form turned out to be :-
DELETE DISTINCTROW TableA.Invoice_No,TableA.*
FROM TableA INNERJOIN TableB
ON TableA.Invoice_No = TableB.Invoice_No

I can't claim to have thought that up myself. When your version was run, an
error was generated indicating that the table from which the deletion was to
be made should be specified. Once that was done in the delete query design
window, the necessary clause was automatically added to the SQL and all was
well.

I still find SQL statements arcane and I cannot see how the required form of
statement can be generated automatically in the query design window. Had you
not pointed out that DISTINCTROW needed to be added, I very much doubt that I
would ever have stumbled on the required query form. Still, thanks to your
input I can now proceed.

 

[Guest]

Many thanks for that. It was not immediately apparent where to set the
UniqueRecords property nor that it would add the DistinctRow clause, but your
clarification has very effectively killed two birds with one stone. I can
now delete the common records in the two tables and do it through the query
design window. I am not too keen at the prospect of handling raw SQL and I
view Access as an excellent high level programming tool to avoid doing that.
Your suggestion will now enable me to achieve that end. I had a look at the
work-around that would be needed if I could not get the query to work and it
was not an encouraging prospect. Thankfully it will not now be necessary.
John Spencer's contribution means that, if I do get further problems, I can
always check the SQL statement and know what to look for. Between the two of
you I think you have resolved that problem very nicely. I am particularly
grateful.