How to terminate SQL statements when using DEFAULT value and OleDB?

Using C# and the JET OleDB driver (Microsoft.Jet.OLEDB.4.0) to connect to an old Access database (think Access 97). The following SQL statements work fine:

ALTER TABLE [MyTable] ADD [FunkyInt] Integer DEFAULT 3
ALTER TABLE [MyTable] ADD [FunkyString] Text(30) DEFAULT hello

But terminating the statement with a semi-colon (;) causes problems:

ALTER TABLE [MyTable] ADD [FunkyInt] Integer DEFAULT 3;  -- syntax error
ALTER TABLE [MyTable] ADD [FunkyString] Text(30) DEFAULT hello; -- default is now "hello;"

Can SQL statements with DEFAULT be terminated?

Answer

Default values can (and probably should) be quoted using double quotes to avoid these errors, among others:

ALTER TABLE [MyTable] ADD [FunkyInt] Integer DEFAULT "3";  
ALTER TABLE [MyTable] ADD [FunkyString] Text(30) DEFAULT "hello"; 

Note that Access doesn’t support executing multiple statements at once, so there’s really no reason to terminate statements. They terminate at the end automatically.

Alternativly, add a trailing space after the literal. The DEFAULT clause is followed by a literal, which is either enclosed in quotes and ends on the closing quotes, or ends on the following whitespace.

ALTER TABLE [MyTable] ADD [FunkyInt] Integer DEFAULT 3 ;