I'm trying to update an old Access database using SQL scripts, but I can't find the syntax for removing a default value from a column.
Is this possible in Access?
+1
A:
I don't think the Access SQL is much different from the regular SQL syntax:
ALTER TABLE TableName
ALTER COLUMN ColumnName DROP DEFAULT
Matthew Jones
2009-06-23 13:50:09
Tried that Matthew and it give a syntax error
LordSauce
2009-06-23 13:53:32
I forgot the ALTER COLUMN statement. Updated my answer.
Matthew Jones
2009-06-23 13:54:16
Just read some of the answers to the question Brettski mentions and discovered the SQL syntax option - your statement works if you switch the database to SQL Server syntax mode, but not in Access syntax mode.
LordSauce
2009-06-23 13:58:36
Yes, this syntax is only supported in ANSI-92 Query Mode (http://office.microsoft.com/en-gb/access/HP030704831033.aspx).
onedaywhen
2009-06-23 14:22:45
+2
A:
Alter statements are not fully supported in Access and cause many headaches. You would be much better off if you open the database in access and edit the schema that way.
This question is a possible dupe SQL to add column with default value - Access 2003
Brettski
2009-06-23 13:50:29