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?
 
RichWCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.