Add column with default value 1

[Niebotel]

I try to add new colums to a table.
First line works fine
Second line works fine also
Third line gives a syntaxis error; I want to set also the default value in the new field (What is wrong)

1. db.Execute "ALTER TABLE Facturen ADD COLUMN Credittekst Text 25"

2. db.Execute "ALTER TABLE Facturen ADD COLUMN CreditBedrag Currency"

3. db.Execute "ALTER TABLE Facturen ADD COLUMN DebitBedrag Currency DEFAULT 0"

Thanks for your help on forehand

Willem

 

[RoyVidar]

While the Jet engine, starting with the 4.0 version, allows such DDL, it has to be performed through the Jet OLE DB provider and ADO. Say

[tt]currentproject.connection.execute _
"ALTER TABLE Facturen ADD COLUMN DebitBedrag Currency DEFAULT 0",, _
adcmdtext + adexecutenorecords[/tt]

For more info, check out for instance

http://msdn.microsoft.com/en-us/library/aa140015(office.10).aspx

and the articles linked from there.

Roy-Vidar

 

[randy700]

How about...

db.Execute "ALTER TABLE Facturen ADD COLUMN DebitBedrag Currency"
db.TableDefs("Facturen").Fields("DebitBedrag").Properties("DefaultValue") = 0

Randy

 

[Niebotel]

Great!! many thanks

Willem