SQL Set Default Not Working in Ms Access

SQL SET DEFAULT not working in MS Access

Support for DEFAULT was included in Access DDL with Jet 4 (Access 2000). However it can only be used in DDL executed from an ADO connection.

This worked with Access 2007.

CurrentProject.Connection.Execute "ALTER TABLE MyTable " & _
    "ALTER COLUMN field2 SET DEFAULT ""foo"";"

Note if your db file is Access 97 or earlier, you won't be able to set a field DEFAULT value from DDL.

Database Access Mdb how to set a default value to existing column via SQL

I solved using AxBase from SourceForge.net.

I could properly open the *.mdb database and run the above SQL commands and they worked perfectly:

 ALTER TABLE Members ALTER COLUMN Privacy SET DEFAULT 1

SQL to add column with default value - Access 2003

Tools -> Options -> Tables/Queries -> (At the bottom right:) Sql Server Compatible Syntax - turn option on for this database.

then you can execute your query:

ALTER TABLE documents ADD COLUMN membersOnly NUMBER DEFAULT 0

---

Related Topics