Tuesday 23 May 2017

Drop Column if Exists in MS Access

Function ifFieldExists(ByVal fieldName As String, ByVal TableName As String) As Boolean
Dim db As DAO.Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim strName As String
    Set db = CurrentDb
    Set tbl = db.TableDefs(TableName)
    For Each fld In tbl.Fields
        If fld.Name = fieldName Then
            ifFieldExists = True
            Exit For
        End If
    Next
End Function
Execution Process:
paste this code under a button
If ifFieldExists("Column_Name", "Table_Name") Then
db.execute "Alter table Table_Name drop column Column_Name"
Else
db.Execute "Alter table Table_Name add column Column_Name text"

End If

No comments:

Post a Comment