Microsoft Access run-time error '3075' Syntax Error

Question

I want to delete something in my database using a button with the following VBA behind it.

Unfortunately it gives me a run-time error and I don't know why. I've looked how Access give the SQL statement back and in SQL Server it gives me no errors.

VBA:

Private Sub btnDeleteTaak_Click()
      If Not IsNull(Me.lstTaakMonteur) Then
        If Not IsNull(Me.comboMonteur.Value) Then
            If Not IsNull(Me.comboOpdracht.Value) Then
                Dim DeleteSQL As String
                DeleteSQL = "DELETE OpdrachtTaak WHERE opdrachtnr = " & Me.comboOpdracht.Value & " AND taaknr = " & Me.lstTaakMonteur.Value & " AND monteurcode = '" & Me.comboMonteur.Value & "'"
                DoCmd.RunSQL (DeleteSQL)
                Me.lstTaakMonteur.Requery
            Else
                MsgBox ("Er is geen opdracht geselecteerd")
            End If
        Else
             MsgBox ("Er is geen monteur geselecteerd")
        End If
    Else
        MsgBox ("Er is geen taak geselecteerd")
    End If
End Sub

The error:

Syntax error (missing operator) in query expression 'OpdrachtTaak WHERE opdrachtnr = 1 AND taaknr = 6 AND monteurcode = 'LM1"

Table structure OpdrachtTaak:

opdrachtnr NUMERIC(5) <pk,fk2> NOT NULL
taaknr NUMERIC(3) <pk, fk3> NOT NULL
tijdsduur NUMERIC(4,1) NULL
Monteurcode VARCHAR(3) <fk1> NULL

Would be great if you can help me out.

Maybe it needs a FROM clause? Change the SQL to DELETE * FROM OpdrachtTaak WHERE opdrachtnr... ? — Acantud
– Acantud, Commented Jun 7, 2014 at 15:03
@Acantud Wow that did the job sir. Don't believe I missed that. Thanks for the answer. — JoeriSmits
– JoeriSmits, Commented Jun 7, 2014 at 15:09

Hamid Reza · Accepted Answer · 2014-06-08 08:25:12Z

1

Delete statements in MS Access databases is fairly different from those in SQL Server. In MS Access you must include From statement in your delete query. So your code will be:

DeleteSQL = "DELETE * FROM OpdrachtTaak WHERE opdrachtnr = " & Me.comboOpdracht.Value & " AND taaknr = " & Me.lstTaakMonteur.Value & " AND monteurcode = '" & Me.comboMonteur.Value & "'"

answered Jun 8, 2014 at 8:25

Hamid Reza

4851 gold badge5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Microsoft Access run-time error '3075' Syntax Error

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related