Re: Adjusting Field Size

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 10/06/04 

Date: Wed, 6 Oct 2004 18:39:36 -0400

You can't change the size using DAO. Your only option is to add a new field
of the correct size, run an update query to transfer the data from the old
field to the new one, delete the old field then rename the new field.

You can check the size of the field as datdatabase.TablesDefs("New
Records").Fields("CompanyName").Size

You appear to know how to add a new field, so I'll skip over that (the only
change you need to make to your sample code is
Set tdfNewRecords = datdatabase.TablesDefs("New Records"), rather than the
CreateTableDef method you're using)

The update query would be as simple as:

datdatabase.Execute "UPDATE [New Records] SET [NewCompanyName] =
[CompanyName]", dbFailOnError

Deleting the old field would be:

tdfNewRecords.Fields.Delete "CompanyName"

You can then rename the new field:

tdfNewRecords.Fields("NewCompanyName") = "CompanyName" 

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Michael A. Gunther" <guntherm@chuckals.net> wrote in message
news:10m82a72bh0bf95@corp.supernews.com...
> Can you adjust a field size on the fly of a tabledef using DAO??
>
> The field CompanyName was created and is too small. I need it to be 40
chars
> not 30. Can I use code to look at the size and if it's 30 then either 1.
> delete field and recreate field with right size, or 2 Just change the
field
> size itself. Example of code would be helpful please.
>
> Dim tdfNewRecords As DaO.TableDef
> Set tdfNewRecords = datdatabase.CreateTableDef("New Records")
>
> With tdfNewRecords
>
>       .Fields.Append .CreateField("GM Account", dbText, 25)
>       .Fields("GM Account").AllowZeroLength = True
>       .Fields.Append .CreateField("RECID", dbText, 15)
>       .Fields("RECID").AllowZeroLength = True
>       .Fields.Append .CreateField("CompanyName", dbText, 30)
>       .Fields("CompanyName").AllowZeroLength = True
>
>       datdatabase.TableDefs.Append tdfNewRecords
>
>         Set idxTemp = .CreateIndex("PrimaryKey")
>           With idxTemp
>             .Fields.Append .CreateField("GM Account")
>             .Primary = True
>             .unique = True
>            End With
>           .Indexes.Append idxTemp
>
>
>     End With
>
>

Relevant Pages

  • Re: Adjusting Field Size
    ... however I thought you couldn't rename a field using DAO also? ... tabledefs to add fields on the fly for newer versions. ... > field to the new one, delete the old field then rename the new field. ... >> Dim tdfNewRecords As DaO.TableDef ...
    (microsoft.public.vb.database.dao)
  • Adjusting Field Size
    ... Can you adjust a field size on the fly of a tabledef using DAO?? ... The field CompanyName was created and is too small. ... Set tdfNewRecords = datdatabase.CreateTableDef ...
    (microsoft.public.vb.database.dao)