Write Date/Time value to a Ms Access table

[ben64]

This is causing me a real headache. Here is the statement I'm sending and the error I receive:

INSERT INTO Measurement (TestID,DateTime) VALUES (8732,#'2015-01-28 16:17:58'#);

NI_Database_API.lvlib:Conn Execute.vi->Untitled 2.vi->Untitled 1.vi<ERR>ADO Error: 0x80040E14
Exception occured in Microsoft JET Database Engine: Syntax error in INSERT INTO statement. in NI_Database_API.lvlib:Conn Execute.vi->Untitled 2.vi->Untitled 1.vi

I tried most of the possible combinations of #,'," around the datetime string and I also tried different datetime string formats YYYY/MM/DD HH:MM:SS AM/PM, DD/MM/YYYY...

Writing to other data fields do not cause any error, I only get the syntax error when I try to write to the DateTime field (data type Date/Type)

I'm using LV2012 with the database connectivity toolkit and MS Access 2010 (stuck with it, cannot change). Same issue using User DSN or UDL connection (jet engine).

It works with the DB Tools Insert Data.vi but we want to use a direct INSERT statement using DB Tools Execute Query.vi because we experienced some error 0x80004005 locks that may be caused by the DB Tools Insert Data.vi using ADODB.Recordset.

Attached is the vi I use for debug.

Ben64

[JimAllen]

My Access is all screwed up so I cant test it with your code but I have been writing Timestamp strings to an SQL database using this format "%Y-%m-%d %I:%M:%S" and surrounding it with '.

[ben64 · Accepted solution]

Finally found the problem!

Even if the data type is Date/Time in Access and it is possible to set the field name to DateTime it seems to be a restricted word when using an SQL statement. The solution is to add brackets around DateTime like this:

INSERT INTO Measurement (TestID,[DateTime]) VALUES (1,'2015-01-28 06:52:16');

Thanks for your help Cosworth.

Ben64