Delete fields referring to field number

[thh]

I need to trim a lot of tables. Is it possible to delete table fields with reference to the field number instead of field name?

The command 'tblMyTable.Fields.Delete ("FieldName")' takes a string (field name) as input. I rather like to - in a loop - delete say,

Sub DeleteFields()
    For i = 40 To 60
        tblMyTable.Fields.Delete Field(i) ' Not correct syntax
        Next i
End Sub

thh

 

[llkhoutx]

Take a look at "ALTER TABLE". That's what you want.

Note that you can't programmatically delete a column that's part of an index; you have to first use te "DROP CONSTRAINT."

 

[thh]

Good point about the index, llkhoutx. Otherwise it seems that the trick is to pull out the field name in a loop, and input the fieldname in the delete statement:

Sub DeleteFields()
'Make a copy of table for testing:
DoCmd.CopyObject , "MyTableCopy", acTable, "MyTable"

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strFieldName As String
    Dim strTableName As String
    
    Set db = CurrentDb
    Set tdf = db.TableDefs("MyTableCopy")
    
    strTableName = db.TableDefs("MyTableCopy").Name
    Debug.Print "Fields deleted in table " & strTableName & ":"
    
    'Delete starting field every time, since fields to the right 
    'of deleted field move left:
    For I = 40 To 60
        strFieldName = tdf.Fields(40).Name
        Debug.Print strFieldName
        tdf.Fields.Delete (strFieldName)
    Next I

End Sub

thh