Change a field type programatically using VBA Access 2007

Question

I have a routine that imports an Excel table, changes several column (field) names, adds an index and several new fields. However, the indexed field imports from Excel as a Double and I would like to change it to Long. This code snippet works all except for changing the type:

Set tdf = db.TableDefs(TableName) ' Modify the new MBDA List table
For Each fld In tdf.Fields
    Select Case fld.Name ' Change the name of specific fields
        Case "MBDA Piece ID #"
            fld.Name = "MBDA Piece ID"
            fld.Type = dbLong ' change the type from Double to Long
        Case ">>Grade Level"
            fld.Name = "Grade"
        Case Else
    End Select
Next fld
NumFlds = i

Answer 1

You can't change the type of a table field once it's part of a table. Setting fld.Type only works when adding new fields to a table.

Your code probably gives "Error 3219: Invalid Operation" ?

But you can do it with DDL (Data Definition Language) SQL, with an ALTER TABLE - ALTER COLUMN statement:

db.Execute "ALTER TABLE [MBDA List] ALTER COLUMN [MBDA Piece ID] LONG" 

Edit: Here's an overview of DDL data type names vs. DAO constants (e.g. dbLong): http://allenbrowne.com/ser-49.html