Samples of VBA code I'm using in my daily tasks.

Friday, March 12, 2010

Does filed exist in Access table

Sample call: test = FieldExist(CurrentDb.Name, "EmployeeTable", "EmployeeNbr")

'-------------------------------------------------------------------------
' FieldExists: Determine if a field exists in an Access table
'-------------------------------------------------------------------------
Public Function FieldExists(DatabaseName As String, _
    TableName As String, FieldName As String) As Boolean
    'DataBaseName is the file/path name of the database
    'with the field you want to test
    'tablename is the table, fieldname is the field
    'if database or table does not exist, an error is raised
    
    Dim oDB As Database
    Dim td As TableDef
    Dim f As Field
    
    On Error GoTo ErrorHandler
    
    Set oDB = Workspaces(0).OpenDatabase(DatabaseName)
    Set td = oDB.TableDefs(TableName)
    
    On Error Resume Next
    Set f = td.Fields(FieldName)
    FieldExists = Err.Number = 0
    oDB.Close
    
    Exit Function
    
ErrorHandler:
    
    If Not oDB Is Nothing Then oDB.Close
    Err.Raise Err.Number
    Exit Function
    
End Function
'---------------- END FieldExits  ---------------------------------------

No comments:

Post a Comment