DDL to create TimeStamp field

[winshent]

Hi Guys

I'm trying to use DDL to add a DATE field as above with NOW() as the default value..

I am using the same syntax pretty much (Altering instead of Creating a table), but its not working..

The error I get is:

err.number = 3293
err.description = Syntax error in ALTER TABLE statement.

Here is the code.. Any ideas ?

CurrentDb.Execute "ALTER TABLE MyTable ADD COLUMN RecordCreatedDate DATE DEFAULT NOW()"

 

[BlueIshDan]

CurrentDb.Execute "ALTER TABLE MyTable ADD RecordCreatedDate DATE DEFAULT NOW()"

http://www.w3schools.com/sql/sql_alter.asp

 

[BlueIshDan]

If not that, add the column without default then run this type of query:
http://www.w3schools.com/sql/sql_default.asp

 

[winshent]

All of the following failed:

CurrentDb.Execute "ALTER TABLE Audit ADD RecordCreatedDate DATE NOW() ;"

CurrentDb.Execute "ALTER TABLE Audit ADD RecordCreatedDate SET DEFAULT NOW() ;"

CurrentDb.Execute "ALTER TABLE Audit ADD RecordCreatedDate SET DEFAULT 'NOW()' ;"

CurrentDb.Execute "ALTER TABLE Audit ADD COLUMN RecordCreatedDate SET DEFAULT NOW() ;"

CurrentDb.Execute "ALTER TABLE Audit ADD COLUMN RecordCreatedDate SET DEFAULT 'NOW()' ;"

 

[BlueIshDan]

CurrentDb.Execute "ALTER TABLE Audit ADD RecordCreatedDate DATE DEFAULT GETDATE();"

 

[winshent]

CurrentDb.Execute "ALTER TABLE Audit ADD RecordCreatedDate DATE DEFAULT GETDATE();"

Still doesn't work.

GETDATE is a SQL Server function.

You also need to specify what you are adding, in this case COLUMN, as opposed to CONSTRAINT etc..

 

[BlueIshDan]

http://www.w3schools.com/sql/sql_default.asp

 

[winshent]

http://www.w3schools.com/sql/sql_default.asp

Hi BlueIshDan

Thanks for helping out so far..

I tried the following two examples from this link..

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

and

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

Both failed. Wondering if its a problem with my install. Does the CREATE TABLE statement work on your machine ?

 

[BlueIshDan]

are you getting an error?

 

[winshent]

are you getting an error?

Same error as in my first post

err.number = 3293
err.description = Syntax error in ALTER TABLE statement.

 

[winshent]

Ok.. looks like the following code works..

Frustrating that I cannot set the default in DDL though.

CurrentDb.Execute "ALTER TABLE Audit ADD COLUMN RecordCreatedDate DATE ;"

CurrentDb.TableDefs("Audit").Fields("RecordCreatedDate").DefaultValue = "now()"

 

[BlueIshDan]

Hahaha the issue is still bothering me!
congrats though.