Set a default value for an integer using SQL

[KevinTekTip]

I have a Microsoft Access DB that I use Visual Basic to write various SQL statements. I have a command button that successfully executes the following SQL statement:

DoCmd.RunSQL "CREATE TABLE Table1(Root VARCHAR(30), ShotLocation VARCHAR(30) PRIMARY KEY, Done INTEGER)
'This successfully creates a tabled named "Table1" that contains the column "Root", "ShotLocation" as the primary key, and the column "Done"

Now my only problem is setting the default value of the number "0" to the integer column

I have tried ...Done INTEGER DEFAULT 0, ...Done INTEGER DEFAULT '0', ...Done NUMBER 0, ...Done NUMBER '0'

I have even also tried to keep the code above that I know works and then run a 2nd SQL statement to APPEND TABLE

DoCmd.RunSQL "ALTER TABLE Table1, ALTER COLUMN Done INTEGER DEFAULT 0"

and that does not work either. My error is always Syntax error in ALTER TABLE statement

Does anybody know if Access has different methods of assigning a default value with SQL? Or is it that I am just missing some simple step? Thanks in advance for the help!

PS. I think I saw a similar question in thread701-552484 but that answer does not work with my problem.

 

[PHV]

I'm afraid you have to play with the Properties collection of the DAO.Field object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886