Change data type size

[albertkhor]

i'm using microsoft access and oledb.

i create a table which name "Product". Inside the "Product" table have 3 field: id, name, price. The name field i set as varchar and size is 50.

But now i want to change the name field size to 100 is that possible? How to do that? If cannot change the size then i think drop table is posible right? but how to copy all the name field data to another field?

 

[cjard]

youwant to do this programmatically? (why?)

field size is a design time issue - something you gte right from the start. in this case solving it at design time is as simple as opening access, opening the table designer and changing the field size

 

[albertkhor]

yes, i know how to change the field size using access but my customer don't know . that is why i want to do this programmatically.

 

[cjard]

ahhh, then you are issuing a fix? I see

I dont know exactly how it is for other DB but I would issue a statement like this for Oracle:

ALTER TABLE product MODIFY name VARCHAR2(100);


Downsizing conversions require the column to be empty in some cases. If this happens, you can:

1) CREATE TABLE tmpProduct as SELECT * FROM product;
2) DELETE FROM product;
3) ALTER TABLE product MODIFY name VARCHAR2(25);
4) INSERT INTO product(name, size, weight, etc)
SELECT(SUBSTR(name, 1, 25), size, weight, etc) FROM tmpProduct


Im sure youll be able to google for the corollarary in whatever DB tech youre using

 

[albertkhor]

Dear cjard,

Thanks for your reply but "ALTER TABLE product MODIFY name VARCHAR2(25);" cannot use in microsoft access~

 

[cjard]

I did note that I didnt know what the syntax was for the DB engine youre using (Access), and i noted:

Im sure youll be able to google for the corollarary in whatever DB tech youre using

So, i recommend you look up how to ALTER TABLE in access.. Much as I could do it for you, I think you';ll gain more from the experience if you have a bash at using google yourself