Add and delete fields using VBA (1 Viewer)

[jimday1982]

Is it possible to add and delete fields in my tables using VBA? I've been looking for the syntax everywhere but can't seem to find it. Anyone know?

 

[ghudson]

Try this...

'Delete the [ID_Number] field from the "tEmployee" table
Public Sub DeleteTableField()
    DoCmd.RunSQL ("ALTER TABLE tEmployee " & "DROP COLUMN ID_Number;")
End Sub

'Create the new [ID_Number] AutoNumber field in "tEmployee" table
Public Function AddTableField()
    Call CreateAutoNumberField("tEmployee", "ID_Number")
End Function

Function CreateAutoNumberField(ByVal strTableName As String, ByVal strFieldName As String) As Boolean
On Error GoTo Err_CreateAutoNumberField
'ensure reference is made to DAO
    
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim tdef As DAO.TableDef
    
    Set db = Application.CurrentDb
    Set tdef = db.TableDefs(strTableName)
    Set fld = tdef.CreateField(strFieldName, dbLong)
    With fld
        .Attributes = .Attributes Or dbAutoIncrField
    End With
    With tdef.Fields
        .Append fld
        .Refresh
    End With
    
    CreateAutoNumberField = True
    
Exit_CreateAutoNumberField:

    Set fld = Nothing
    Set tdef = Nothing
    Set db = Nothing
    Exit Function
    
Err_CreateAutoNumberField:
    CreateAutoNumberField = False
    With Err
        MsgBox "Error " & .Number & vbCrLf & .Description, vbOKOnly Or vbCritical, "CreateAutonumberField"
    End With
    Resume Exit_CreateAutoNumberField
    
End Function

I have never had to do anything else with adding fields so you will have to look a little deeper for the different field types.

HTH

 

[jimday1982]

Thanks, I'm using the following code:

DoCmd.RunSQL ("ALTER TABLE Orders DROP COLUMN Flag;")

I'm getting an error 3211 - the table can't be locked because its already in use. Any ideas?

 

[ghudson]

You can not alter the design of the table while it is open or a record source [query / form] is open [linked] to the table.