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?
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 ;