Syntax error in Alter Table statement - Run-time error '3293':

[JGravesNBS]

I'm getting following error message on following Alter command:

Run-time error '3293':
Syntax error in Alter Table statement

dbs.Execute "ALTER TABLE Customers ADD COLUMN dtmDateCreate DATE DEFAULT Date();"

Also, what is the syntax to add the following to the Alter statement?

Format mm/dd/yyyy
Input Mask 90/90/99;0;_
Caption Create

 

[pbaldy]

I wonder if it's this from MSDN:

The DEFAULT statement can be executed only through the Access OLE DB provider and ADO. It will return an error message if used through the Access SQL View user interface.

 

[JHB]

You can change the field property after you've created the field.
So first:

dbs.Execute "ALTER TABLE Customers ADD COLUMN dtmDateCreate DATE;

Then

dbs.TableDefs("Customers").Fields("dtmDateCreate").DefaultValue = "Date()"

 

[JGravesNBS]

Thanks for the solution that worked

How about the syntax to add the following to dtmDateCreate

Format mm/dd/yyyy
Input Mask 90/90/99;0;_
Caption Create
Required Yes
Indexed Yes

dbs.TableDefs("Customers").Fields("dtmDateCreate").Format = "mm/dd/yyyy)"

Returns Compile error: Method or data member not found

 

[JHB]

The "Format" is under the fields properties, and if it not exist you've to create it!
Here is a link to another thread with some code how to create a property, look at post #12.
http://www.access-programmers.co.uk/forums/showthread.php?t=281943