Delete Query with more than one table

[cwolgamott]

Hello. I have been looking through this forum to find a possible solution to a problem I have been having with a delete query. I would like to be able to delete from two tables joined together by a field in one of the tables. I have tried several statements but none of them seem to work. Here is the latest statement that I tried:

DELETE Meter.*, Reading.*
FROM Meter LEFT JOIN Reading ON Meter.MeterNum = Reading.MeterNum WHERE Reading.ProcessedReadFlag='Y';

This brings back the records that I want to delete, but when I try to delete them, I get the following message:

Could not delete from specified tables.

I would greatly appreciate any suggestions or comments on how I might be able to solve my problem. Thank you.

 

[tman138]

Can't do it in a single query. You can do it in 2 (or more)

One thing you might try is BeginTrans / CommitTrans /Rollback running both delete queries before committing the transaction. Something like:
Public Function fnMyFunction()
Dim IntCount As Integer

Dim db As Database
Dim ws As Workspace

On Error GoTo ERROR_fnMyFunction

Dim varQ
Dim strQ As String
varQ = Array("dlryFirstDeleteQuery", "dlrySecondDeleteQuery&quot 'Use your own delete query names here

Set ws = DBEngine(0)
Set db = ws(0)
DoCmd.SetWarnings False
ws.BeginTrans

IntCount = 1
For IntCount = 1 To 2
strQ = varQ(IntCount - 1)
DoCmd.OpenQuery strQ

Next

ws.CommitTrans


EXIT_fnMyFunction:
Exit Function

ERROR_fnMyFunction:
ws.Rollback
Resume EXIT_fnMyFunction

DoCmd.SetWarnings True
End Function

In this way if one of the queries fails, neither will run.

 

[danvlas]

Another option would be to set referential integrity between the two tables, check 'Cascade delete related records' and make a delete query on the first table only. This will delete the related records automatically.

Just an opinion...

Dan