Change Field Size through VB

Can I increase the size of a Text data type Field in table (MS Access Database)?

e.g. Table Name is EMPLOYEE
    Field Name is Fname Text(20)

Through VB Code I want to make it 25 or greater.

how it is possible? pl. let me know.

hnd_raAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
RichWConnect With a Mentor Commented:
bruintje,

Sorry.  I started writing the comment, then walked away and came back.  Didn't know you already gave the same thing I entered.

Anyway, hnd_ra the line should read:

cnn.Execute "ALTER TABLE EMPLOYEE ALTER [FName] VarChar(25)"

Cheers,
RichW
0
 
bruintjeCommented:
Hi hnd_ra,

don't think it's possible. see what MSDN help says

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

Using the ALTER TABLE statement, you can alter an existing table in several ways. You can:

Use ADD COLUMN to add a new field to the table.

Use ADD CONSTRAINT to add a multiple-field index.

Use DROP COLUMN to delete a field.

Use DROP CONSTRAINT to delete a multiple-field index.

if you still want to do it there's a small workaround through DAO

-Assume a field trans_type TEXT (20) whose size needs to be 30.
-Add a field trans_type_temp TEXT(30)
-UPDATE <Table> SET trans_type_temp = trans_type
-Delete the trans_type field.
-Add a field trans_type TEXT(30)
-UPDATE <Table> SET trans_type = trans_type_temp
-Delete the trans_type_temp field.

lot of work better make a new table and move the data

HAGD:O)Bruintje
0
 
RichWCommented:
Dim cnn as ADODB.Connection
Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0;"
      .Open "Data Source=C:\Path\YourDB.mdb;"
   End With

cnn.Execute "ALTER TABLE YOURTABLE ALTER [FName] VarChar(25)"

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
bruintjeCommented:
no problem, just wondering why i didn't do it with ado, guess because i got no setup to test now ;)
0
 
hnd_raAuthor Commented:
Thanks  RichW ,
   This is working fine.
I wnted to know ne more thing, if I have to chnage DatType e.g. from Text to Long, then what will be the syntex.

Pl. let me know.

Again a lot of thanks

0
 
RichWCommented:
Thanks hnd_ra.

I believe the following will change it to Number (Long Integer) for Access:

cnn.Execute "ALTER TABLE EMPLOYEE ALTER [FName] Long "

Thanks again!
RichW
0
 
hnd_raAuthor Commented:
Thanks RichW ,
     Yeahn its working fine. Actually earlier I also did like this one but by mistake there was a parenthesis ")" in my syntex.

Really u r great.........

Thanks a lot

0
All Courses

From novice to tech pro — start learning today.