Need query to add New Column for existing table with default value

[mailtosrajesh]

Hello friends,

I want to add new column to an existing table in Microsoft Access database with default value to be set.

I tried the following query,

ALTER TABLE Customers ADD COLUMN runno NUMBER DEFAULT 0;

This is not executing.

In MSDN its mentioned:

"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"

Is there any way to achieve this?

Thanks,
Rajesh.S

[NassauBob]

Hello. Now, is there a reason you are doing this in SQL, and not from the table design view? Because you should be able to simply open the table in design view, add the column wherever you want it, and set the default value in that attribute under Field Properties.

Using Microsoft Help, I find the syntax for the ALTER TABLE statement as follows:

ALTER TABLE Employees ADD COLUMN Notes TEXT(25)

Perhaps it is the default value portion of your statement that causes the failure. Try running the statement as specified above, then run a second query to update all of the new values to the desired default value.

Shane

EDIT: I just tried this in my database over here, and yes, the default value portion of the statement causes an error. First add the column, then do a separate update statement to add the default value, if you still want to do it in SQL.

[mailtosrajesh]

Hello Shane,

Thank you for spending time for my problem,

I am doing this query from Code to add new columns to be added into existing table, at the same time i want to set default value to that new column,
so that later if i not provide value for those colums while inserting records it should take default value. (Here I specified 1 column but in real time there are many columns like this , so i won't provide values in some circumstations in that time it should take default value.)

Regards,
Rajesh.S

[NassauBob]

I understand your intentions, and I think that once the table column(s) have been added, and you add the appropriate default values in table design view, that any new records without explicit values in those columns will have the desired default inserted.

Have you tried it yet with a small sample table? If no, I recommend it.

[mailtosrajesh]

I tried creating sample small table with 2 columns initially, and I need to add 1 or more columns through query that should have default values.not only for previous existing records, this default values should be applicable for newly added rows also.

For example assume my table customer is having id, name,address,

I want to add new column through query (not in design view, because i am going to use this query in my programming code) for this newly added column, default value propery should be set,

For example assume I am adding new column runno ,(assume default is 0)

While inserting records, Some times i may not provide value for runno, so it should take default value 0 (not null value) If we not specify default value its currently taking null value.

For example ,

while inserting

insert into customer values(1,'rajesh','address');

I didn't provide value for runno,this case it should take 0 as runno,this is my requirement.

Thanks,
Rajesh.S

[NassauBob]

Looking at the ALTER TABLE man pages, I think that you are going to have to look at doing this another way. All over the web there is the same question: can I set a default value on the addED column on the fly? And over and over, the answer is No.

Sorry dude, but at least it appears that you are not alone in your endeavor.

[mailtosrajesh]

Hi Shane,

Thank you very much for looking my queries.

Thanks
Rajesh.S

[ronymaxwell]

I found this useful. I have 26 tables of word lists each starting with a different letter of the alphabet. I'd just listed the words, then realised I needed a numerical field to find them with DLookUp, etc. I used 'ALTER TABLE B ADD COLUMN ID AUTOINCREMENT;' running in SQL view, just altering the table name as I went which saved me working on each table individually.