How to use a UNION subquery to delete unmatch records?

[jbt007]

Hi all - I am trying to create a single sql statement that will delete unmatch recoreds. It is a fairly simple action on the surface, however it involves a subquery that is a UNION. I keep getting errors. Can someone point me in the right direction?. I can create the UNION query, and then create another query that uses the Union query. However, I would really like to combine the two into one SQL statement so it can be placed in VBA without any queries saved in the db. Here's the "delete" query:

DELETE tblActMap.Job, tblActMap.Phase, tblActMap.Act, tblActMap.S_Key, qryAllActivities.S_Key 
FROM tblActMap LEFT JOIN qryAllActivities ON tblActMap.S_Key = qryAllActivities.S_Key
WHERE (((qryAllActivities.S_Key) Is Null));

Here's the UNION query (qryAllActiviti es in the above):

SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key
UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key
UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key;

Again, I would like a single SQL that combines the two. My thought is that this would be a Subquery, but I can't get it to work...

Thanks in advance.

[Rabbit]

Does this work?

LEFT JOIN (SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key 
UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key 
UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key) AS qryAllActivities

[jbt007]

Rabbit - Yes and no. It's much closer than I got. Funny how when you see the answer it seems obvious. I am still getting the following error:

Runtime Error '3128'
Specify the table contining the records you want to delete.

Here's the code I used:

    strSQL = "DELETE tblActMap.Job, tblActMap.Phase, tblActMap.Act, ActList.S_Key " & _
             "FROM tblActMap LEFT JOIN " & _
                "(SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key " & _
                "UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key " & _
                "UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key) " & _
            "AS ActList " & _
            "ON tblActMap.S_Key = ActList.S_Key WHERE (((ActList.S_Key) Is Null));"
    Debug.Print strSQL
    db.Execute strSQL, dbFailOnError

(Note: I changed "qryAllActiviti es" to "ActList" while testing...)

The debug.print statment gave me this:

DELETE tblActMap.Job, tblActMap.Phase, tblActMap.Act, ActList.S_Key FROM tblActMap LEFT JOIN (SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key) AS ActList ON tblActMap.S_Key = ActList.S_Key WHERE (((ActList.S_Key) Is Null));

and if I copy/paste it into a blank sql edit screen and run it, it works fine. Is it something to do with the db.Execute?

[Stewart Ross]

You'll be able to tell if it is a db.execute problem by substituting the following for the db.execute statement:

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

The RunSQL method runs an action query in a very similar way to running it in the query editor (which is why you need to set warnings off, as otherwise the DB would ask for confirmation of the delete).

On occasion I've found that an apparently valid update or other action query fails to run from the Execute method of the application object, yet runs fine from the RunSQL method of the DoCmd object. If that's the case here it's another quirk of the database engine I guess.

-Stewart

[jbt007]

A Solution

Stewart & Rabbit -

Thanks for the input, both of you were very helpful. Using the "DELETE ..." sql code and DoCmd.RunSQL command (Stewart) gave me the error:

Runtime Error: '3027':
Database or Object is Read-Only

I did some help-file reading and from what I can tell the recordset generated from the SQL code creates a read-only recordset (rs.Updatable = False) because of the joined subquery. To solve the problem, I used the following:

    strSQL = "SELECT tblActMap.Job, tblActMap.Phase, tblActMap.Act, tblActMap.ActTitle, tblActMap.S_Key " & _
             "FROM tblActMap LEFT JOIN " & _
               "(SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key " & _
               "UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key " & _
               "UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key) AS ActList " & _
             "ON tblActMap.S_Key = ActList.S_Key WHERE (((ActList.S_Key) Is Null));"

    'Debug.Print strSQL
    Set rs = db.OpenRecordset(strSQL)
    If rs.EOF And rs.BOF Then
        'No records to delete
    Else
        rs.MoveLast
        rs.MoveFirst
        Do While Not rs.EOF
            strSQL = "DELETE tblActMap.* FROM tblActMap WHERE (((tblActMap.S_Key) = '" & rs!S_Key & "'));"
            'Debug.Print strSQL
            db.Execute strSQL, dbFailOnError
            rs.MoveNext
        Loop
    End If

This code uses the SQL Rabbit help with as a SELECT rather than a DELETE, and then deletes the records from the tblActMap one at a time using the rs to identify records to delete. We will never have very many records to delete (larges I have seen is around 25 records), so this process will runn fast enough. I would prefer a "one sql statement" solution, but it doesn't look like it's going to happen.

Thanks for all your help.

[jbt007]

For whoever wants to know... I found the solution to the original question. The answer was to put "DISTINCTRO W" in the SQL Statement so Access would know to ignor the non-primary key joined table. Here's the solution:

    strSQL = "DELETE DISTINCTROW tblActMap.* " & _
             "FROM tblActMap LEFT JOIN " & _
               "(SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key " & _
               "UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key " & _
               "UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key) AS ActList " & _
             "ON tblActMap.S_Key = ActList.S_Key WHERE (((ActList.S_Key) Is Null));"
    db.Execute strSQL, dbFailOnError

Thanks again to Rabbit and Stewart for their help.