Updating Field Size through SQL

[VANMAN]

I have an Access DB that I am updating using VB and I want to increase the field Size of a TEXT field from 50 to 200. Can anyone tell me how this is done
I was trying to use EXECUTE to do this but I can't find the correct command.

Thanks

 

[smedvid]

Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb&quot

' Add the Salary field to the Employees table
' and make it a Currency data type.
dbs.Execute "ALTER TABLE Employees " _
& "ADD COLUMN Salary CURRENCY;"

dbs.Close

Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[VANMAN]

Sorry this will only create a new field not modify the size of an existing one.

 

[smedvid]

What I've done in the past is add a new column, via ALTER, then UPDATE to populate the New column, then DROP the old Column... Essentially, 3 statements. I really wanted to insure that I did not lose any data, etc...

Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[VANMAN]

I am lazy and I was hoping I could do this with one command not 3, but I guess I spent more time trying to avoid the work than just getting it done. Thanks for your time I will do it that way.

 

[tlbroadbent]

For future reference, you can use the ALTER TABLE command in JET SQL to change a column size.

DoCmd.RunSQL ("ALTER TABLE tbl ALTER COLUMN Test text(12)&quot

I recommend the Jet SQL Reference in Access Help. It contains the syntax for SQL DDL and DML statements. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[VANMAN]

Well BOB'S YOUR UNCLE that worked like a hot dam, I used db.execute to do run it. I missed the ALTER COLUMN in my original execute. Thanks a bunch I will remember that.