>>The MAX_CLAUSES is empirically found magic number :) If you have a huge table, and you make lots of changes, it may take a long time, so it's much better to do just one ALTER TABLE command. Actually, it's mostly about changing structure, not adding fields, but I think, the same rule still applies. I have DataDictionary application, which allows to add fields/delete fields/change structure in our main database. It's a quite compicated program, so I don't want to show it all, but this idea is borrowed from there.
>
>IC. I thought multiple 'add column' would syntax error.
>Cetin

Here is the code fragment from the program Modify_Structure. It works fine:

          if m.lnField=0 && New field
               lcMessage="In the table " +m.tcTableName+ CR + ;
                    "from Database " + m.tcDBC + CR + ;
                    "the field "+ m.lcFieldName + " is added now"
               set message to m.lcMessage
               wait window nowait set('message',1)
               tcStr= m.tcStr+ 'New field '+ m.lcFieldName + " was added" + CR
               llSomethingChanged=.t.
* NSL 5/11/00 - Remove the alter table tablename part because it is added when macro executed.
               lcCommand=;
                    " ADD COLUMN " + m.lcFieldName + " "+ DataDict.Field_Type
*                                                  lbChange=.t. && Need to add
* Save the new field name
               llNewField=.t.
               lnAddFlds=m.lnAddFlds+1
               dimension AddFlds[m.lnAddFlds] && Re-dimension array
               AddFlds[m.lnAddFlds]=m.lcFieldName
               select DataDict          && PKP: DEC 99,
* -- END CHANGE PKP JAN 2000
*--------------------------------------------------------------------------
               if not empty(DataDict.Field_Defa)
                    lcCommand=m.lcCommand+" DEFAULT "+ alltrim(DataDict.Field_Defa)
               endif
               if inlist(DataDict.Field_Type,"C","M") and DataDict.Field_Nocp
                    lcCommand=m.lcCommand+" NOCPTRANS"
               endif
          endif && End of new field

          if m.lnField>0 and DataDict.Field_Type<>laFields[m.lnField,2]
**  Field Type changed
               lbChange=.t.
               set message to ;
                    "In the table " +m.tcTableName+ CR+ ;
                    "from Database " + m.tcDBC+ CR+ ;
                    "the field "+ m.lcFieldName +" is changed now" +CR+ ;
                    "because the field type is different"
               wait window nowait set('message',1)
               tcStr= m.tcStr+ ;
                    "Field: "+m.lcFieldName+" type was changed"+CR
               llSomethingChanged=.t.
               llTypeChanged=.t.
          endif
          ********* This would not be a case, since this field is non-editable anymore
          do case
             case left(m.tcTableName,7)='BLDMSTR' and m.lnField>0 and not laFields[m.lnField,5] && BldMstr fields should accept nulls for each field
               lbChange=.t.
               set message to ;
                    "In the table " +m.tcTableName+ CR+ ;
                    "from Database " + m.tcDBC+ CR+ ;
                    "the field "+ m.lcFieldName +" is changed now" +CR+ ;
                    "because the field Null is different"
               wait window nowait set('message',1)
               tcStr= m.tcStr+ ;
                    "Field: "+m.lcFieldName+" Null was changed"+CR
               llSomethingChanged=.t.
               llNullChanged=.t.
         case left(m.tcTableName,7)<>'BLDMSTR' and m.lnField>0 and laFields[m.lnField,5] && State DBCs don't accept NULLs
               lbChange=.t.
               set message to ;
                    "In the table " +m.tcTableName+ CR+ ;
                    "from Database " + m.tcDBC+ CR+ ;
                    "the field "+ m.lcFieldName +" is changed now" +CR+ ;
                    "because the field Null is different"
               wait window nowait set('message',1)
               tcStr= m.tcStr+ ;
                    "Field: "+m.lcFieldName+" Null was changed"+CR
               llSomethingChanged=.t.
               llNullChanged=.t.
          endcase
*!*          if lnField>0 and  inlist(Field_Type,"C","M") and ;
*!*                    Field_Nocp<>laFields[lnField,6]
*!*               lbChange=.t.
*!*               wait window nowait ;
*!*               "In the table " +tcTableName+ CR+;
*!*               "from Database " + m.tcDBC+ CR+;
*!*               "the field "+ lcFieldName +" is changed now" +CR+;
*!*               "because the field Nocptrans is different"
*!*               tcStr= tcStr+ ;
*!*               "Field: "+lcFieldName+" Nocptrans was changed" +CR
*!*               llSomethingChanged=.t.
*!*         endif
          if  m.lnField>0 and ;
                    upper(alltrim(DataDict.Field_Defa))<>upper(alltrim(laFields[m.lnField,9]))
               set message to ;
                    "In the table " +m.tcTableName+ CR+;
                    "from Database " + m.tcDBC+ CR+;
                    "the field "+ m.lcFieldName +" is changed now" + CR + ;
                    "because the field default is different"
               wait window nowait set('message',1)
               tcStr= m.tcStr+      "Field: "+m.lcFieldName+" Default Value was changed" + CR
               llSomethingChanged=.t.
               lcCommand=" ALTER COLUMN "+m.lcFieldName+ ;
                    iif(!empty(DataDict.Field_Defa)," SET DEFAULT "+ ;
                    upper(alltrim(DataDict.Field_Defa))," DROP DEFAULT")
          endif
          if m.lnField>0 and not m.llTypeChanged and ;
                    inlist(DataDict.Field_Type,"C","N","F","B") and ;
                    (DataDict.Field_Len<>laFields[m.lnField,3] or;
                    DataDict.Field_Dec<>laFields[m.lnField,4])
               lbChange=.t.
               set message to ;
                    "In the table " +m.tcTableName+ CR+ ;
                    "from Database " + m.tcDBC+ CR+ ;
                    "the field "+ m.lcFieldName +" is changed now "+CR+;
                    "because field len was changed..."
               wait window nowait set('message',1)
               tcStr= m.tcStr+ ;
                    "Field: "+m.lcFieldName+" length was changed"+CR
               llSomethingChanged=.t.
               llLenChanged=.t.
          endif
* NSL 5/15/00 Added the check for lbChange back in - we do not want to alter table unless needed.
* NSL 5/15/00 I removed the lbchange flag here because we need the alter column code
*                    We will not use it if we do not have a lbChange flag later.
          if m.lbChange && Removed and lbChange PKP Jan 2000 need to check Validation at all times
* NSL 5/11/00 - Remove the alter table tablename part because it is added when macro executed.
               lcCommand= m.lcCommand + ;
                    " ALTER COLUMN " + m.lcFieldName + ;
                    iif(m.llTypeChanged or m.llLenChanged," "+ DataDict.Field_Type,"")
          endif
* -- Add in the field length
          if m.llLenChanged or m.llNewField or m.llTypeChanged
               do case
               case inlist(DataDict.Field_Type,"D","T","I","Y","L","M","G","P")
** Don't need to specify field len
               case DataDict.Field_Type="C" && Character
                    lcCommand=m.lcCommand +"("+transform(DataDict.Field_Len)+")"
               case inlist(DataDict.Field_Type,"N","F","B") && Numeric, or Floating, or Double
                    lcCommand=m.lcCommand+ " ("+transform(DataDict.Field_Len)+","+;
                         transform(DataDict.Field_Dec)+")"
               endcase
          endif
          if m.llNewField or m.llNullChanged
               lcCommand=m.lcCommand+iif(left(m.tcTableName,7)="BLDMSTR"," NULL"," NOT NULL")
          endif
          lcFieldName=lower(m.tcTableName)+'.'+ lower(m.lcFieldName)
          lcComment=left(alltrim(DataDict.Definition),255)
          lcCaption=alltrim(DataDict.header)
          select WorkFile
*--------------------------------------------------------------------------
* -- NSL 5/11/00 New code to do many alters at once
* -- Without this - after the alter of each field VFP would append all of the records
* -- For these preexisting DBC's this was much too long.
*--------------------------------------------------------------------------
          if m.lbVChange and "DROP CHECK" $ m.lcVCommand && Separate phrase for validations
               lcCommandStr=m.lcCommandStr+m.lcVCommand  && append new changes to the command string
               lnClauses = m.lnClauses + 1     && keep track of # clauses
          endif
          if !empty(m.lcCommand)
               lcCommandStr=m.lcCommandStr+ m.lcCommand  && append new changes to the command string
               lnClauses = m.lnClauses + occurs("ALTER", m.lcCommand) && keep track of # clauses
          endif
          if m.lbVChange and "SET CHECK" $ m.lcVCommand && Separate phrase for validations
               lcCommandStr=m.lcCommandStr+m.lcVCommand  && append new changes to the command string
               lnClauses = m.lnClauses + 1     && keep track of # clauses
          endif
*need more work here because need to do adds separate from modi's, etc. - maybe ok - test
****************
          if m.lnClauses >= ALTER_MAXFLDS     && max # fields per command line reached
               llRemaining=.t.
*=MESSAGEBOX('ALTER IS:'+lcCommandStr)  && NSL - Good to use when debugging
               set message to "Running ALTER TABLE up to clause # " + transform(m.lnClauses)
               llStop=ChangeTable(m.tcTableName, m.lcCommandStr)
               lnClauses = 0               && reset counter
               lcCommandStr=''               && Clear the string for the next command
          endif

And here is ChangeTable program:

********************************************************************
*  Description.......: ChangeTable - run alter command
*  Calling Samples...:
*  Parameter List....: tcTableName, tcCommandStr
*  Created by........: Nadya Nosonovsky 06/06/2001 10:16:59 AM
*  Modified by.......:
********************************************************************
lparameters tcTableName, tcCommandStr
local lcOldError, llError, lcTag
lcOldError=on('error')
on error llError=.t.
* invoke the ALTER TABLE – SQL Command with &-macro expanded tail
alter table (m.tcTableName) &tcCommandStr novalidate
do while m.llError and oValid.lContinue
     release laError
     =aerror(laError)
     if laError[1,1] = 1531 && cannot alter table because of index
             lcTag = laError[1,3]
          set message to 'Deleting tag '+m.lcTag
          delete tag (m.lcTag)
          llError = .f.
          alter table (m.tcTableName) &tcCommandStr novalidate
     else
          exit
     endif
enddo
if m.llError
** Exited
     aerror(laError)
     =messagebox("Error: " + transform(laError[1,1])+chr(13)+laError[1,2], 48,"Alter table error")
endif
*set message to ' '               && clear status bar msg.
on error &lcOldError
return m.llError