By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,955 Members | 1,768 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,955 IT Pros & Developers. It's quick & easy.

trying to change text field size in a table

P: n/a
Well I have an old database that all the text fields are set to 25-50
don't know why but I needed to change them all to 250.
Well I wrote some code to change the size of the fields in the tables
to 250 but I get a read only error when I try to change it.

Is there anyway to change the size in code?

thanks big time for any help
Oct 24 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
This is a little bit of a chore doing this programmatically, but what
you have to do is to create all new fields in your table (additional new
fields) and set the size there. Then you move the data to the new
fields and delete the old fields. If you only have 10 fields it is just
eaiser to do it manually by going into design view of the table and
changing the field size there. Or if your tables only have 10 fields
apiece but you have to do this to 50 tables - then programmactically is
less of a chore. Here is one way to do it:

Sub chgFields()
Dim DB As DAO.Database, tdf As DAO.TableDef
Dim fld As DAO.Field, i As Integer, fldArray As Variant
fldArray = Array("fld1", "fld2", ..., "fld10")
Set DB = CurrentDB
Set tdf = DB.TableDefs("yourTbl")
For i = 1 to 10
Set fld = tdf.CreateField("newfld" & i, dbText, 250)
tdf.Fields.Append fld
Next

DoCmd.Run Sql "Update yourTbl Set fld1 = newfld1, fld2 = newfld2, ...,
fld10 = new fld10"
For i = 0 to 9
tdf.fields.Delete fldArray(i)
Next

Set tdf = nothing
set DB = nothing
End Sub

Another way to do this is with Sql
Sub AddDropflds()
DoCmd.RunSql "Alter Table yourTbl Add newfld1 Text 250, newfld2 Text
250, ..., newfld10 Text 250)

DoCmd.RunSql "Alter Table yourTbl Drop Column fld1, fld2, ..., fld10"
End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Oct 24 '06 #2

P: n/a
sparks <js******@swbell.netwrote in
news:gl********************************@4ax.com:
Well I have an old database that all the text fields are set to 25-50
don't know why but I needed to change them all to 250.
Well I wrote some code to change the size of the fields in the tables
to 250 but I get a read only error when I try to change it.

Is there anyway to change the size in code?
CurrentDb.Execute "ALTER TABLE Table2 ALTER COLUMN f5 Text(250)"

--
Lyle Fairfield
Oct 24 '06 #3

P: n/a
On Tue, 24 Oct 2006 20:44:27 GMT, sparks wrote:
Well I have an old database that all the text fields are set to 25-50
don't know why but I needed to change them all to 250.
Well I wrote some code to change the size of the fields in the tables
to 250 but I get a read only error when I try to change it.

Is there anyway to change the size in code?

thanks big time for any help
In a Module:

Sub ChangeATable()
' Change the FieldSize property of all text fields
' in a table to 250 characters
Dim dbs As Database
Dim tdf As TableDef
Set dbs = CurrentDb
Dim fld As Field
Set tdf = dbs("YourTableName")
For Each fld In tdf.Fields
If fld.Type = 10 Then
dbs.Execute "ALTER TABLE YourTableName " _
& "ALTER COLUMN " & fld.Name & " Text(250);", dbFailOnError
End If
Next fld
dbs.Close
End Sub
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Oct 24 '06 #4

P: n/a
Thanks guys I did not know that AlterTable was in access 2003
This will help a lot

On Tue, 24 Oct 2006 16:03:40 -0700, fredg <fg******@example.invalid>
wrote:
>On Tue, 24 Oct 2006 20:44:27 GMT, sparks wrote:
>Well I have an old database that all the text fields are set to 25-50
don't know why but I needed to change them all to 250.
Well I wrote some code to change the size of the fields in the tables
to 250 but I get a read only error when I try to change it.

Is there anyway to change the size in code?

thanks big time for any help

In a Module:

Sub ChangeATable()
' Change the FieldSize property of all text fields
' in a table to 250 characters
Dim dbs As Database
Dim tdf As TableDef
Set dbs = CurrentDb
Dim fld As Field
Set tdf = dbs("YourTableName")
For Each fld In tdf.Fields
If fld.Type = 10 Then
dbs.Execute "ALTER TABLE YourTableName " _
& "ALTER COLUMN " & fld.Name & " Text(250);", dbFailOnError
End If
Next fld
dbs.Close
End Sub
Oct 25 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.