MS access running SQL doesn't insert data, no error

Question

I want to add data to this table from a form.

enter image description here

I have Combo box for the Job and Team ID's that look up data from their respective tables.

Private Sub save_new_Click()
On Error GoTo save_new_Click_Err

    On Error Resume Next
    Dim strSQL As String

    strSQL = "INSERT INTO Employee (Name,ATUUID,Job ID,Team ID,Start Date, Comments) " & _
     " VALUES(" & Me.Employye_Name & ", " & Me.ATTUID & ", " & Me.cboFunc & ", " & _
     Me.cboTeam & ", " & Me.Start_Date & ", " & Me.Comments & ")"
     Debug.Print strSQL
    With CurrentDb
        .Execute (strSQL), dbFailOnError
        Debug.Print .RecordsAffected
    End With

Here is the resulting SQL string:

 INSERT INTO Employee (Name,ATUUID,Job ID,Team ID,Start Date, Comments)  VALUES(asd, asd, 1, 2, 7/10/2015, asdasd)

Debug.Print .RecordsAffected

Prints 0

You need to delimit your strings with " (or maybe it is ' --- can't remember for access.) — Hogan
– Hogan, Commented Jul 16, 2015 at 15:05
You are vulnerable to sql injection attacks, which is why your query is failing. it's totally invalid. 7/10/2015 is mathematical DIVISION, not a date. asdasd is a field name, not a string, blah blah blah — Marc B
– Marc B, Commented Jul 16, 2015 at 15:08
@AlexK.: gotta love languages that give you the power to say "I've just destroyed the entire universe, oh well, let's just pretend that didn't happen" — Marc B
– Marc B, Commented Jul 16, 2015 at 15:09
The first step in debugging it for me would be to run the query on the SQL Server using Management Studio, and see if it actually works; as others have said I suspect it won't. If it does... then run activity monitor on the server while running your code, and see what, if anything, happens. — JeffUK
– JeffUK, Commented Jul 16, 2015 at 15:24

Community · Accepted Answer · 2017-05-23 11:51:12Z

As @AlexK. explained, the reason you're not seeing errors is because On Error Resume Next hides errors. When you use that, you're telling Access "ignore any error --- don't even mention it --- and continue at the next line."

But the INSERT statement that code builds will definitely trigger an error. You can confirm that fact if you copy the output of Debug.Print strSQL from the Immediate window, create a new query in the query designer, switch the query to SQL View, paste in the statement text and try to run it.

When you have a field name which includes a space, you must enclose it in square brackets so the db engine recognizes it as one identifier instead of two. I would also bracket Name because it's a reserved word, but I doubt it actually contributes to the problem here:

"INSERT INTO Employee ([Name], ATUUID, [Job ID], [Team ID], [Start Date], Comments)"

Beyond that, I suggest you use a temporary QueryDef based on a parameter query, supply the parameter values, and Execute it.

'On Error Resume Next '<-- leave this disabled, AT LEAST while debugging!
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "INSERT INTO Employee ([Name], ATUUID, [Job ID], [Team ID], [Start Date], Comments)" & vbCrLf & _
    "VALUES (pName, pATUUID, pJobID, pTeamID, pStartDate, pComments);"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSQL)
With qdf
    .Parameters("pName").Value = Me.Employye_Name.Value
    .Parameters("pATUUID").Value = Me.ATTUID.Value
    .Parameters("pJobID").Value = Me.cboFunc.Value
    .Parameters("pTeamID").Value = Me.cboTeam.Value
    .Parameters("pStartDate").Value = Me.Start_Date.Value
    .Parameters("pComments").Value = Me.Comments.Value
    .Execute dbFailOnError
End With
Debug.Print db.RecordsAffected

thanks this solved the issue, I also messed up on ATUUID which is ATTUID :D

Collectives™ on Stack Overflow

MS access running SQL doesn't insert data, no error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related