Remove Fields From MDB programmatically

Posted by VIJI Saturday, July 4, 2009

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

Set Td = Nothing

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

Set AccessDb = Nothing
End Function



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"

Same artcicle available under

0 Responses to Remove Fields From MDB programmatically

Post a Comment