Request for help - Trying to use default value in CREATE TABLE statement

[gellerche]

I've been given DDL scripts so I can create a copy of a database in Access. I would like to simultaneously create the tables and assign their default values, but I can't. If I do this:

SQL = "CREATE TABLE tablename("
SQL = SQL + "ID CHAR(11) NOT NULL, "
SQL = SQL + "CD SMALLINT NOT NULL, "
SQL = SQL + "MOD_ID CHAR(8) NOT NULL, "
SQL = SQL + "TSTMP TIMESTAMP NOT NULL, "
SQL = SQL + " CONSTRAINT tablename_pk Primary Key(ID))"

DoCmd.RunSQL (SQL)

it works. But if I do this (change is on the 5th line)

SQL = "CREATE TABLE tablename("
SQL = SQL + "ID CHAR(11) NOT NULL, "
SQL = SQL + "CD SMALLINT NOT NULL, "
SQL = SQL + "MOD_ID CHAR(8) NOT NULL, "
SQL = SQL + "TSTMP TIMESTAMP DEFAULT NOT NULL, "
SQL = SQL + " CONSTRAINT tablename_pk Primary Key(ID))"

DoCmd.RunSQL (SQL)

I get a syntax error. Can anyone tell me how to assign default values when creating tables?

Thank you,


Steve Geller

 

[WayneRyan]

Steve,

What I've read says that it can't be done in SQL, but you
can do it with ADO, here's a link:

http://dbforums.com/arch/210/2002/9/499823

Wayne

 

[Jon K]

You can set the default value after the table is created.

For example, to set the default value of the field TSTMP to the Date() function, you can use:

CurrentDb.TableDefs("tablename").Fields("TSTMP").DefaultValue = "Date()"

 

[gellerche]

Wayne and Jon:

Thank you both for your help (especially Wayne, I know you've helped me out in the past). I've used Jon's solution, and it works perfectly.

Steve Geller