Chaging Fields Properties of existing tables

[GigglyGirl]

Hi,

I am trying to figure out how to change properties of fields of existing tables. Is it possible to change properties through Access VBA?

Thank you

 

[Terpsfan]

First you want to look at the sql syntax for altering tables/columns

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
ALTER COLUMN field type[(size)] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }

Sub ChangeFieldProperty()


Dim dbs As Database
Set dbs = CurrentDb()

dbs.Execute "ALTER TABLE tblAccounts ALTER COLUMN AcctNum Text(20);"


dbs.Close


End Sub

Sub AddColumn()


Dim dbs As Database
Set dbs = CurrentDb()



dbs.Execute "ALTER TABLE tblAccount ADD COLUMN AcctNum Long;"


dbs.Close


End Sub

 

[GigglyGirl]

yeah but here you are adding column..
The thing is I am trying to just change fields' properties of an existing table because it contains recods already.

Thanks for you fast feedback Omega

 

[pweegar]

The problem you may encounter (And YES you CAN alter the column types.. esp. if you have access to the table design) is that changing field properties may cause some undesired side affects. Make a column width to short and you'll truncate data. Change the type from say text to integer and yoou may lose data or it will convert tosomething you don'twant/recognize. Date fields can be an especailly big pain. Just be careful and give lots of thought to what you really want to do first.