Modifying Column Data Format Using Alter Table Add Column

0
votes

I am trying to modify field size of the data type in an new column in an Access table using Excel VBA. I have the following code that works:

InsertDataCommand = "ALTER TABLE " & TableName & " ADD COLUMN " & ColumnName & " "
Select Case DataType
    Case Is = "TEXT"
        InsertDataCommand = InsertDataCommand & DataType & "(" & DataLength & ")"
    Case Is = "NUMBER"
        InsertDataCommand = InsertDataCommand & DataType
End Select

AccessObject.CurrentProject.Connection.Execute (InsertDataCommand)

My issue is some of my numbers require a Double format and some require a Long Integer format. I can not find any documentation for how to specify that like I can with the TEXT field length. Any thoughts?

1
vbaexcelms-access

1 Answers

1
votes

Ok, I figured it out. It was surprisingly simple.

InsertDataCommand = "ALTER TABLE " & TableName & " ADD COLUMN " & ColumnName & " "
Select Case DataType
    Case Is = "TEXT"
        InsertDataCommand = InsertDataCommand & DataType & "(" & DataLength & ")"
    Case Is = "LONG INTEGER"
        InsertDataCommand = InsertDataCommand & DataType
    Case Is = "DOUBLE"
        InsertDataCommand = InsertDataCommand & DataType
    Case Is = "MEMO"
        InsertDataCommand = InsertDataCommand & DataType
    Case Is = "DATE"
        InsertDataCommand = InsertDataCommand & DataType
End Select

AccessObject.CurrentProject.Connection.Execute (InsertDataCommand)

I thought the Number type was going to have to specify the same kind of statement that the Text type did, but alas it did not.