I have a table "Employees" with a field "Emplcode".

Can I change the Field size of "Emplcode" form 3 to 6 with VBA?
Without loosing existing information!

Scott Burke

Yes, you can increase the field size over-riding the field size in the table
definitions without losing existing text. However, the field size cannot
exceed the field size in the table definition.
--
Hopefully helpful,


"Scott Burke" wrote:

> I have a table "Employees" with a field "Emplcode".
>
> Can I change the Field size of "Emplcode" form 3 to 6 with VBA?
> Without loosing existing information!
>
> Scott Burke

Hi Dick. Maybe I am reading this wrong. Did you just say "yes" you can but
you cant go larger than max size of a text field?
If so can you give me a sample code on how to change the field size?

Thank You.
Scott Burke

"Dick D" wrote:

> Yes, you can increase the field size over-riding the field size in the table
> definitions without losing existing text. However, the field size cannot
> exceed the field size in the table definition.
> --
> Hopefully helpful,
>
>
> "Scott Burke" wrote:
>
> > I have a table "Employees" with a field "Emplcode".
> >
> > Can I change the Field size of "Emplcode" form 3 to 6 with VBA?
> > Without loosing existing information!
> >
> > Scott Burke

You can't change the size of an existing field without generating an
error, AFAIK. You can, however, create a temp field of the right size,
copy data over, delete the old one, create the new one, copy the data
over (to preserve field names), and delete the temp field. Any indexes
or relationships will generate and error, however.

Sub FieldChange()
'This will fail if the field is a part of any index or
relationship.....
'Additional code is needed if that is a possibility
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Contacts")
Set fld = tdf.CreateField("LastName1", dbText, 255)
tdf.Fields.Append fld
dbs.Execute "Update Contacts set lastname1 = lastname"
tdf.Fields.Delete "LastName"
tdf.Fields.Refresh
Set fld = tdf.CreateField("LastName", dbText, 255)
tdf.Fields.Append fld
dbs.Execute "Update Contacts set lastname = lastname1"
tdf.Fields.Delete "LastName1"
tdf.Fields.Refresh
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
End Sub



Chris Nebinger

Run the following DDL:

ALTER TABLE Employees ALTER COLUMN Emplcode Text(6)

If your database isn't split, you could use:

CurrentDb.Execute "ALTER TABLE Employees ALTER COLUMN Emplcode Text(6)",
dbFailOnError

If your database is split, you need to run it against the backend, not the
current database:

Dim dbBackend As DAO.Database
Dim strBackend As String
Dim strDDL As String

strBackend = "full path to backend database"
strDDL = "ALTER TABLE Employees ALTER COLUMN Emplcode Text(6)"

Set dbBackend = OpenDatabase(strBackend)
dbBackend.Execute strDDL, dbFailOnError





--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Scott Burke" <(E-Mail Removed)> wrote in message
news:E93AC641-DE79-4099-83AF-(E-Mail Removed)...
>I have a table "Employees" with a field "Emplcode".
>
> Can I change the Field size of "Emplcode" form 3 to 6 with VBA?
> Without loosing existing information!
>
> Scott Burke

Hi Douglas. Nice try but It does not work on link tables. It Figures!

Chris, I have not tried your ideal yet. Maybe on Monday.

Have a good weekend.
Scott Burke



"Douglas J. Steele" wrote:

> Run the following DDL:
>
> ALTER TABLE Employees ALTER COLUMN Emplcode Text(6)
>
> If your database isn't split, you could use:
>
> CurrentDb.Execute "ALTER TABLE Employees ALTER COLUMN Emplcode Text(6)",
> dbFailOnError
>
> If your database is split, you need to run it against the backend, not the
> current database:
>
> Dim dbBackend As DAO.Database
> Dim strBackend As String
> Dim strDDL As String
>
> strBackend = "full path to backend database"
> strDDL = "ALTER TABLE Employees ALTER COLUMN Emplcode Text(6)"
>
> Set dbBackend = OpenDatabase(strBackend)
> dbBackend.Execute strDDL, dbFailOnError
>
>
>
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Scott Burke" <(E-Mail Removed)> wrote in message
> news:E93AC641-DE79-4099-83AF-(E-Mail Removed)...
> >I have a table "Employees" with a field "Emplcode".
> >
> > Can I change the Field size of "Emplcode" form 3 to 6 with VBA?
> > Without loosing existing information!
> >
> > Scott Burke
>
>
>

Linked tables are tougher. You have to change CurrentDB to
OpenDatabase(strFileName). Also, you will probably have to delete the
link and create it again.


Chris

Sure it does.

That's the second case I talked about:

>> Dim dbBackend As DAO.Database
>> Dim strBackend As String
>> Dim strDDL As String
>>
>> strBackend = "full path to backend database"
>> strDDL = "ALTER TABLE Employees ALTER COLUMN Emplcode Text(6)"
>>
>> Set dbBackend = OpenDatabase(strBackend)
>> dbBackend.Execute strDDL, dbFailOnError

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Scott Burke" <(E-Mail Removed)> wrote in message
news:4B1BE5EC-F619-4E90-AC08-(E-Mail Removed)...
> Hi Douglas. Nice try but It does not work on link tables. It Figures!
>
> Chris, I have not tried your ideal yet. Maybe on Monday.
>
> Have a good weekend.
> Scott Burke
>
>
>
> "Douglas J. Steele" wrote:
>
>> Run the following DDL:
>>
>> ALTER TABLE Employees ALTER COLUMN Emplcode Text(6)
>>
>> If your database isn't split, you could use:
>>
>> CurrentDb.Execute "ALTER TABLE Employees ALTER COLUMN Emplcode Text(6)",
>> dbFailOnError
>>
>> If your database is split, you need to run it against the backend, not
>> the
>> current database:
>>
>> Dim dbBackend As DAO.Database
>> Dim strBackend As String
>> Dim strDDL As String
>>
>> strBackend = "full path to backend database"
>> strDDL = "ALTER TABLE Employees ALTER COLUMN Emplcode Text(6)"
>>
>> Set dbBackend = OpenDatabase(strBackend)
>> dbBackend.Execute strDDL, dbFailOnError
>>
>>
>>
>>
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Scott Burke" <(E-Mail Removed)> wrote in message
>> news:E93AC641-DE79-4099-83AF-(E-Mail Removed)...
>> >I have a table "Employees" with a field "Emplcode".
>> >
>> > Can I change the Field size of "Emplcode" form 3 to 6 with VBA?
>> > Without loosing existing information!
>> >
>> > Scott Burke
>>
>>
>>

Just for my interest: why do you wn to do this through code (as opposed
to manually)?

TC (MVP Access)
http://tc2.atspace.com

"TC" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Just for my interest: why do you wn to do this through code (as opposed
> to manually)?

Seems like a reasonable request to me, TC.

If you've deployed your application to a client at a remote site, having a
script to make any table changes required for the next version of the
application seems logical. I just finished a two-part column for Access
Advisor about how to do this, so I hope my readers agree with me! <g>

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)