Changing Field Size in Code

[Rohdem]

I have a table that already exists and I want to change the size of one of the fields in code from 7 to 50. I have tried using

Dim tdf As TableDef, fld1 As Field
Dim dbs As Database

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("tblsernums&quot
Set fld1 = tdf.Fields("datesold&quot
fld1.Size = 50

Apparently the 'size' property is read only if it already exists in the collection.

I've also tried fld1.fieldsize = 50, but had the same read-only problem.

There must be a way to change the field size after it has been created using VB!!!!

Thanks for any help you can give me.
Mike Rohde
rohdem@marshallengines.com

 

[Rohdem]

Just so you know, "datesold" is a text field, not a date field. The table is imported from a text file. The procedure I am trying to write imports the text file to a table and cleans up the data.
Mike Rohde
rohdem@marshallengines.com

 

[MichaelRed]

At least according to the Ms. Docs., you cannot change the fieldsize of an existing table/field via code. There is a rather awkward work around. You can create a new field and assign the properties to it and then append it to your original table. Then, you would need to copy (e.g. Update) hte new field to the values in the old field. So far, you are about half way home. Now, via code, delete the old field. Re-Crreate the old filed with the properties to accomodate the new field. Almost home. Copy the New field contents back to the re-created original field. Finally. Delete the old field.

Or, just do it in the design view of the table?



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Rohdem]

Thanks Michael

I am trying to set this up to run overnight, so changing it in design view myself is not really an option.

What I finally ended up doing was importing the data, then exporting it to a new table with the proper field sizes. It takes longer, but what do I care if I'm running it when I'm not here anyway!!
Mike Rohde
rohdem@marshallengines.com