MS Access : Remove Fields From MDB programmatically

The following VBA RemoveFieldFromMSACCESSTable code is used to remove Field From Ms access Database programmatically.

About the Function

'The following function is used to remove Field From MS Access database
'It Accepts Three Parameters
'1. Database Path - Complete Path of MS Access Database
'2. TableName - Table Name of Ms Access Database to remove from
'3. FieldName - FieldName to be removed




Function RemoveFieldFromMSACCESSTable(ByVal AccessDBPath, ByVal AccessTableName As String, AccessFieldName As String) As Boolean


'Declaration of Variables
Dim AccessDB As Database
Dim AccessDBPath As Variant
Dim Td As TableDef

On Error Resume Next

'Retrieve the Path of AccessDB based on TableName

'Open the Access Database
Set AccessDB = OpenDatabase(AccessDBPath) 'if linked table
If Err <> 0 Then
'failed to open back end database
Exit Function
End If

End If

'Retrieve table
Set Td = AccessDB.TableDefs(AccessTableName)

If Err <> 0 Then
'failed to get table

GoTo End
End If

'Delete the Field

With Td
'delete field

.Fields.Delete AccessFieldName
If Err <> 0 Then
'failed to delete field - probably doesn't exist

GoTo End

End If

End With

RemoveFieldFromMSACCESSTable = True 'defaults to false if it fails to get here

'clean up
End:

Set Td = Nothing

'Close the Database
If Not AccessDB Is Nothing Then AccessDB.Close

Set AccessDb = Nothing
End Function



SAMPLE USAGE

Public Sub RemoveField()

'sample call:

If (RemoveFieldFromMSACCESSTable("C:\TEST\Test.mdb", "Employee", "Additional Info") Then

MsgBox "Field Removed SuccessFully"

End If

End Sub

The above call is used to delete From "C:\TEST\Test.mdb" database, the table name is Employee and the field name is "Additional Info"


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: