Another query will not work

[lawdy]

This is almost the same as my previous problem. But this is a delete query. On the line Set rs = db.OpenRecordset(sqlQuery), I get error Run-Time error '3219' Invalid Operation. Follow is the entire function

Public Function modCal_removePrebilledJob() ' remove job from tblCalendar
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlQuery As String

Set db = CurrentDb
sqlQuery = "DELETE * FROM tblCalendar WHERE fLngAProposalNo = 20250762;"
Set rs = db.OpenRecordset(sqlQuery)

Do While Not rs.EOF
Debug.Print rs!fLngProposalNo
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing


End Function

Please help, thank you.

[June7]

Can't use a DELETE SQL to set a recordset.

[Welshgasman]

Seriously? How are you meant to walk a deleted recordset? rofl.

[lawdy]

Thanks June7, but what do I do?

[mike60smart]

All you need for a Delete Query is something on these lines:-

DELETE tblMachine.MachineID,
tblMachine.MachineName
FROM
    tblMachine
WHERE
    (((tblMachine.MachineName) = "ab111"));

[orange]

If you are going to use vba, then this example will show the common use of db.execute for action queries.

Sub DeleteRecords()
    Dim db As DAO.Database
    Dim sql As String

    ' Define the SQL DELETE query
    sql = "DELETE FROM Employees WHERE Department = 'Sales';"

    ' Set the current database
    Set db = CurrentDb

    ' Execute the DELETE query
    db.Execute sql, dbFailOnError

    ' Confirm deletion
    MsgBox "Records deleted successfully!", vbInformation
End Sub

[Welshgasman]

If you want to see what you are about to delete, you can do it this way.

Sub DeleteTemp()
Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from tbltemp")
Do While Not rs.EOF
    rs.MoveFirst
    Debug.Print rs!TempID
    rs.Delete
Loop
Set rs = Nothing
Set db = Nothing
End Sub

However why are you debug.printing a field that was the criteria for the sql?

[June7]

All you need for a Delete Query is something on these lines:-

DELETE tblMachine.MachineID,
tblMachine.MachineName
FROM
    tblMachine
WHERE
    (((tblMachine.MachineName) = "ab111"));

Serves no purpose to list fields with DELETE as entire record is deleted. The * wildcard is not even needed.
DELETE FROM tblMachine WHERE MachineName = "ab111"

[lawdy]

I want to thank everyone that replied to my post. I have a lot of help and I needed it. I am having trouble with queries, as you could see. You did solve my problem.