MS Access INSERT INTO statement

Question

I need to insert form data from my VB.NET application to a Microsoft Access database.

This is the syntax I'm using atm:

INSERT INTO bs1 (teacher, subject, date, period) VALUES ('test', 'test', 'test', 'test')

I'll admit I'm used to the MySQL type syntax, any help on this matter would be greatly appreciated thanks.

Answer 1

I believe date is a reserved word. You need to encapsulate the reserved field names in square brackets:

INSERT INTO bs1 (teacher, subject, [date], period) VALUES ('test', 'test', 'test', 'test')

EDIT: See the following article for a complete list of reserved words in Access 2002 and greater: http://support.microsoft.com/kb/286335

~md5sum~

Answer 2

In Access the delimter for literal values inserted into date fields is #, for text fields is ' or " and numeric field values do not have a delimiter, which suggests:

INSERT INTO bs1 (teacher, subject, [date], period) VALUES ('test', 'test', #2009-12-31#, 0)

Answer 3

In Access Database Engine SQL code, when you need to specify that a literal value is of type DATETIME, you can either explicitly cast the value to DATETIME or use # characters to delimit the value.

Using an explicit cast using the CDATE() function:

INSERT INTO bs1 (teacher, subject, [date], period) 
   VALUES ('test', 'test', CDATE('2009-12-31 00:00:00'), 0);

Using a DATETIME literal value:

INSERT INTO bs1 (teacher, subject, [date], period) 
   VALUES ('test', 'test', #2009-12-31 00:00:00#), 0);

When INSERTing a value into a column of type DATETIME, if you do not specify an explicit DATETIME value, the engine will implicitly attempt to coerce a value to DATETIME. The literal value 'test' cannot be coerced to type DATETIME and this would appear to be the source of your syntax error.

Note: none of the above applies to the NULL value. In Access Database Engine SQL there is no way to cast the NULL value to an explicit type e.g.

SELECT CDATE(NULL)

generates an error, "Invalid use of NULL". Therefore, to specify a NULL DATETIME literal, simply use the NULL keyword.

It pays to remember that the Access Database Engine has but one temporal data type, being DATETIME (its synonyms are DATE, TIME, DATETIME, and TIMESTAMP). Even if you don't explicitly specify a time element, the resulting value will still have a time element, albeit an implicit one. Therefore, it is best to always be explicit and always include the time element when using DATETIME literal values.