Access Database not accepting inserts with certain value using VBA

Question

Im working in Access 2010 with a backend JET Database, inserting values into that database.

This doesn't insert into the database (no error msg returned)

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)

But this does

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)

and here is a snippet of code that does the actual inserting

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

I believe that the issue surrounds inserting 1 into the database in the first example. The database doesn't report back an error and the row doesn't appear. If change the value from 1 to 0.99, it insert fine and shows up in the database. Things that I have tried and observations:

I have also tried to manually converting 1 into a double using the CDbl() function before I do the insert statement in my vba code.
If I break the code after the first insert with value 1, the row id skips to the next value like the row was inserted

Where else can I look?

I bet if you hard coded 1.0 as a value it would work. What are your regional settings, I wonder if it interpreting the 1,1,1 as a number ? — Minty
"The database doesn't report back an error" - Try using CurrentDb.Execute sqlStatement2, dbFailOnError and see if it behaves differently. — Gord Thompson
@gordThompson With the dbFailOnError, I found that it was a validation error in the table itself. Thanks! — Tino

Gord Thompson Gord Thompson · Accepted Answer · 2017-02-14T14:53:49

The database doesn't report back an error and the row doesn't appear.

That's because the default behaviour of the .Execute method is such that

CurrentDb.Execute (sqlStatement2)  ' with no second argument

will avoid throwing an error if the query encounters difficulties. You should be using

CurrentDb.Execute sqlStatement2, dbFailOnError

so .Execute can tell you why the INSERT was unsuccessful.

Access Database not accepting inserts with certain value using VBA

1 Answers