MS 访问运行 SQL 不插入数据，没有错误 [英] MS access running SQL doesn't insert data, no error

问题描述

我想从表单向该表添加数据.

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

我有工作 ID 和团队 ID 的组合框，用于从各自的表中查找数据.

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

这是生成的 SQL 字符串:

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

Debug.Print .RecordsAffected

打印 0

推荐答案

As @AlexK.解释，您没有看到错误的原因是因为 On Error Resume Next 隐藏了错误.当您使用它时，您是在告诉 Access 忽略任何错误 --- 甚至不要提及它 --- 并在下一行继续."

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."

但是代码构建的INSERT语句肯定会触发错误.如果您从立即窗口复制 Debug.Print strSQL 的输出，在查询设计器中创建一个新查询，将查询切换到 SQL 视图，粘贴语句文本并尝试，您可以确认这一事实运行它.

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.

当您有一个包含空格的字段名称时，您必须将其括在方括号中，以便数据库引擎将其识别为一个标识符而不是两个标识符.我也会把 Name 括起来，因为它是一个保留字，但我怀疑它是否真的会导致这里的问题:

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)"

除此之外，我建议您使用基于参数查询的临时 QueryDef，提供参数值，然后 Execute 它.

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

这篇关于MS 访问运行 SQL 不插入数据，没有错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！