Add and delete fields using VBA

jimday1982

Registered User.
Local time
Today, 19:54
Joined
May 13, 2004
Messages
81
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?
 
Try this...
Code:
'Delete the [ID_Number] field from the "tEmployee" table
Public Sub DeleteTableField()
    DoCmd.RunSQL ("ALTER TABLE tEmployee " & "DROP COLUMN ID_Number;")
End Sub
Code:
'Create the new [ID_Number] AutoNumber field in "tEmployee" table
Public Function AddTableField()
    Call CreateAutoNumberField("tEmployee", "ID_Number")
End Function
Code:
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
 
Thanks, I'm using the following code:

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?
 
Last edited:
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.
 

Users who are viewing this thread

Back
Top Bottom