Syntax error in Alter Table statement - Run-time error '3293': (1 Viewer)

JGravesNBS · Oct 23, 2015

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 · Oct 23, 2015

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 · Oct 24, 2015

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

Code:

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

Then

Code:

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

JGravesNBS · Oct 24, 2015

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 · Oct 24, 2015

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

Syntax error in Alter Table statement - Run-time error '3293': (1 Viewer)

JGravesNBS

Registered User.

pbaldy

Wino Moderator

JHB

Have been here a while

JGravesNBS

Registered User.

JHB

Have been here a while

Similar threads

Users who are viewing this thread