Change field size via code

[GuildBoss]

Hiyas all -

Can't seem to locate any information on modifying a field's Size property (Text type) in code. I'm using DAO.

Any tips are greatly appreciated.

TIA

[cyoconnor]

You can't modify a database field length in code, as it throws the data out of sync.

Most dbs don't store all the trailing spaces, so if your worry is file size, rest easy.

CYOCONNOR

[Clunietp]

Don't be so negative CYOCONNOR!

You cannot do this directly thru the FIELDS collection of your tabledef object once the column has already been created. You can, however, use a little SQL magic to make it happen

Like my mama always said, there's nothing a little DDL can't fix
(no, my mother was not Dr. E F Codd)

    'uses dao 3.5/6
    Dim db As Database
    
    'open database
    Set db = DBEngine.OpenDatabase("Nwind2k.mdb")
    
    'execute DDL SQL statement
    db.Execute "Alter Table Customers ALTER COLUMN ContactName char(30)"
    
    'cleanup
    db.Close
    Set db = Nothing

You don't lose any data (unless you make the field too small, of course).



Tom

[GuildBoss]

Wait one second, there, Mr. Tom. That's much too easy a solution. Are you sure that works?

How the devil did I miss that... sigh.

Thanks for the info--you're a life saver! :-)

Paul

[GuildBoss]

I knew it was too good to be true.... :-(

Tom, there is no "Alter Column" directive. There is an ADD and a DROP but no ALTER...

Am i missing something? TIA

Paul

[Clunietp]

I would not have posted it if it did not work! I wrote that and tested it special for you....I was using an Access 2000 database though...

OK, I just tested it. It only works for an Access 2000 database, sorry!

[GuildBoss]

Ah, I see. Well, at least I have something to look forward too when I upgrade. :-)

Thanks for the help anyway.

Paul