Access数据库不接受使用VBA的具有特定值的插入

Question

我在Access 2010中使用一个后端JET数据库，将值插入该数据库。

这不会插入到数据库中（未返回错误消息）

insert into [PROJECT RESOURCE UPDATE HISTORY] ([Current Date], [Project Auto No Ref], 
    [Resource ID Ref], [Allocation Notes], [Allocation Role],[Usage Percentage - Oct], 
    [Usage Percentage - Nov], [Usage Percentage - Dec], [Usage Percentage - Jan],
    [Usage Percentage - Feb], [Usage Percentage - Mar], [Usage Percentage - Apr], 
    [Usage Percentage - May],[Usage Percentage - Jun], [Usage Percentage - Jul], 
    [Usage Percentage - Aug], [Usage Percentage - Sep] ) values (#2/13/2017 11:01:41 AM#,
    540,11,'','Project Manager',0,1,1,1,1,1,1,0,0,0,0,0)

但这确实

insert into [PROJECT RESOURCE UPDATE HISTORY] ([Current Date], [Project Auto No Ref], 
    [Resource ID Ref], [Allocation Notes], [Allocation Role],[Usage Percentage - Oct], 
    [Usage Percentage - Nov], [Usage Percentage - Dec], [Usage Percentage - Jan],
    [Usage Percentage - Feb], [Usage Percentage - Mar], [Usage Percentage - Apr], 
    [Usage Percentage - May],[Usage Percentage - Jun], [Usage Percentage - Jul],
    [Usage Percentage - Aug], [Usage Percentage - Sep] ) values (#2/13/2017 11:04:38 AM#,
    540,821,'','DevOps',0,0.13,0.13,0.13,0.13,0.13,0.13,0,0,0,0,0)

下面是一段代码，用于实际插入

sqlStatement2 = "insert into [PROJECT RESOURCE UPDATE HISTORY] (" & _
    "[Current Date], [Project Auto No Ref], [Resource ID Ref], [Allocation Notes], [Allocation Role]," & _
    "[Usage Percentage - Oct], [Usage Percentage - Nov], [Usage Percentage - Dec], [Usage Percentage - Jan]," & _
    "[Usage Percentage - Feb], [Usage Percentage - Mar], [Usage Percentage - Apr], [Usage Percentage - May]," & _
    "[Usage Percentage - Jun], [Usage Percentage - Jul], [Usage Percentage - Aug], [Usage Percentage - Sep] " & _
    ") values (" & _
    "#" & Now() & "#," & _
    rst![Current Project Ref No] & "," & rst![Current Resource Person ID] & ",'" & rst![Current Allocation Notes] & _
    "','" & rst![Current Allocation Role] & "'," & CDbl(oct) & "," & CDbl(nov) & "," & CDbl(dec) & _
    "," & CDbl(jan) & "," & CDbl(feb) & "," & CDbl(mar) & "," & CDbl(apr) & _
    "," & CDbl(may) & "," & CDbl(jun) & "," & CDbl(jul) & "," & CDbl(aug) & _
    "," & CDbl(sep) & ");"
    MsgBox (sqlStatement2)
    CurrentDb.Execute (sqlStatement2)
    rst.MoveNext

我相信问题围绕着在第一个示例中将1插入数据库。数据库没有报告错误，也没有出现该行。如果将值从1更改为0.99，它会很好地插入并显示在数据库中。我尝试和观察的事情：

>

在vba代码中执行插入语句之前，我还尝试使用CDbl（）函数手动将1转换为双精度。

如果在第一次插入值为1后中断代码，则行id会跳到下一个值，就像插入了行一样

我还能去哪里找？

Answer 1

数据库不会报告错误，也不会出现该行。

这是因为<代码>的默认行为。执行方法如下

CurrentDb.Execute (sqlStatement2)  ' with no second argument

将避免在查询遇到困难时引发错误。您应该使用

CurrentDb.Execute sqlStatement2, dbFailOnError

所以。Execute可以告诉您INSERT不成功的原因。