I have a table that has 248 text fields wrt a spec that I have to comply with. There is no data in the table thus far.

When the original table was designed, the field lengths were set to 255 and we now need to set them to 150 or perhaps a different value.

I wrote some code in a test db so that the field data lengths could be reduced. The code worked.

I transferred the code to the actual db. The code will work up to a small number of fields and then debugs to run time error 3190 i.e. too many fields. If I compact\repair the db the code then moves on further doing more fields than before and so on. Hence only by compacting\repairing the db about 20 times did I get all the fields changed.

The question is, WHY. Here's the main snippet of code: -

For Each tdf In CurrentDb.TableDefs
If tdf.Name = Me.txtTableName Then
For Each fld In tdf.Fields
If DataType(fld.Type) = "Text" Then
CurrentDb.Execute "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & fld.Name & "] TEXT(" & intFieldSize & ");"
x = x + 1
Me.lblFieldNo.Caption = "Field " & x
Me.Repaint
End If
Next
End If
Next

It errors on line CurrentDb.Execute ...

Why not just write a query that alters the table in its entirety?
Or if as you say the table is empty then drop the original and create the replacement - both by SQL instead of VB?

You mean a Select query using the Left function of all the fields? If that's what u mean, then yes I could have done that.

My question really is that the code works and eventually solved the problem, but why does it give me the error that it does?

Why not just append the data into a table that has the desired field lengths?

Yes I could have done that as well Ken. However I'm just curious as to why my code would only work in stages whenever I compacted the db?

Are there other fields beside the ones you wish to alter? Do the number of columns exceed 255 total?

Go to http://office.microsoft.com/en-us/access/HP051868081033.aspx then click on 'Table'

I think there is a setting you can change to help, it eludes me at the moment but I think it's something like 'number of open files' or something like that. Maybe some else can jump it with the correct way to tweak this... :)