Runtime error 3129 invalid operation on delete query 1

[vba317]

Hello,
I am trying to delete specific records with no luck. When I run a ?strSQL here are the results:

DELETE * FROM RPT_Data rd WHERE rd.uci ='BIP' AND rd.rptpd = 375;

There are currently 12 records that fit the criteria.

If I run a regular delete query such as
DELETE RPT_Data.uci, RPT_Data.rptpd
FROM RPT_Data
WHERE (((RPT_Data.uci)="BIP") AND ((RPT_Data.rptpd)=375));
It works with no problem.

Any help is appreciated.

Tom

 strSQL = "DELETE * " & _
                 "FROM RPT_Data rd " & _
                 "WHERE rd.uci ='" & strUCI & "' AND rd.rptpd = " & intPd & ";"
         Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            With rst
                .MoveLast
                .MoveFirst
            End With

 

[KornGeek]

The only thing I see is that in your WHERE clause, you are putting single quotes around the strUCI value rather than double quotes. I suspect this may be your issue.

 

[Andrzejek]

Try:

strSQL = "DELETE * FROM RPT_Data " & _
 "WHERE uci ='" & strUCI & "' AND rptpd = " & intPd

No alias for the table name.

Have fun.

---- Andy

 

[vbajock]

Assigning a Delete query to a recordset? Ah, that just don't work. Try this:

strSQL = "DELETE * " & _
"FROM RPT_Data rd " & _
"WHERE rd.uci ='" & strUCI & "' AND rd.rptpd = " & intPd & ";"

currentproject.connection.execute strSQL

 

[vba317]

Thank you vbajock your solution worked great!

 

[KornGeek]

OK, after playing around with this a bit, it looks like your original strSQL will work, but rather than opening a recordset, use CurrentDB.Execute instead.

strSQL = "DELETE * " & _
         "FROM RPT_Data rd " & _
         "WHERE rd.uci ='" & strUCI & "' AND rd.rptpd = " & intPd & ";"
CurrentDb.Execute strSQL

 

[KornGeek]

Oops, looks like vbajock solved it while I was still playing around.

 

[vbajock]

Use either technique (CurrentDb.Execute strSQL executes the query via DAO, currentproject.connection.execute strSQL executes via ADO) to execute 'action' queries, recordsets are only for SELECTS. I usually don't do INSERT queries using these methods, tho. Do INSERT by calling a saved query via docmd.openquery "someInsertQuery" because the .execute method will error out if there are any duplicates and the INSERT will fail, by using a saved query instead you can use setwarnings to either ignore or notify the user of key viols by setting it to true or false.

 

[PHV]

Another simple way:
DoCmd.RunSQL strSQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886