Change Field size with VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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
 
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
 
TC said:
Just for my interest: why do you wn to do this through code (as opposed
to manually)?
Click to expand...

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>
 
Yes, that is certainly a legitimate use. But I can't help thinking, if
that is what he was doing, he'd be beyond the stage of asking that
question. So my bet is, he's doing it on a regular basis for some other
reason. Hopefully he will post back & explain!

Cheers,
TC (MVP Access)
http://tc2.atspace.com
 
Hi TC.
Sorry for not getting back to you sooner. I just had two end of the world
disaters on Monday. I just finished fixing the last problem an hour ago. :)

The reason for doing this is simple. I have two people using our Magbid2000
program and they live in different states.

I am in need of changing a field size on those computers. I thought the
easyest way would be to change the field size with VBA. However I could not
find the code in the help file. So I asked for help.

I think I will try Chris's Ideal. I dont think it will work because the
field is indexed! but I am not sure at this point. I have too many projects
running through my head to be sure right now.

I write with the results.

Scott Burke
 
Back
Top