How to I check to see if a field exists in a table using VB?

[niftysam]

How to I check to see if a field exists in a table using VB?

I want to run
DoCmd.RunSQL ("ALTER TABLE " & test2 & " DROP COLUMN Branch Distance;")

but the field may not exists in all cases.

 

[ByteMyzer]

Try this code:

Public Function FieldExists(tableName As String, fieldName As String) As Boolean
On Error GoTo FieldExists_Exit

    Dim fld As Field

    For Each fld In CurrentDb.TableDefs(tableName).Fields
        If fld.NAME = fieldName Then FieldExists = True
    Next

FieldExists_Exit:
End Function

If MyTable has a field named Field7, then:
?FieldExists("MyTable","Field7")
should return True.

If, however, MyTable does not have a field named Field7, then:
?FieldExists("MyTable","Field7")
should return False.

 

[niftysam]

I am having a problem with this code. It always returns False. I am using Access 2000. Any ideas?


For Each fld In CurrentDb.TableDefs(tableName).Fields
If fld.Name = fieldName Then FieldExists = True
Next

 

[dan-cat]

Are you calling the function correctly?

Your code should look something like

If FieldExists("myTableName","myFieldName") = True Then
' Field exists
Else
' Field does not exist
End If

The function that Mr Byte kindly supplied is asking for variables - so you need to make sure you parse them through correctly.

 

[niftysam]

If FieldExists(Me!Combo2, "Branch") = True Then
DoCmd.RunSQL ("ALTER TABLE " & Me!Combo2 & " DROP COLUMN Branch ;")
End If


Yes, I know I keep looking at it thinking it will look. Another weird thing is that in debug mode if I roll over this if statement, the little popup says TRUE = TRUE?? but it still doesn't execute the docomd.

I know the answer is going to be something so easy, but I just can't see it.

 

[dcx693]

I honestly don't know what's wrong with the code, but I can suggest something that might do the job just as well.

Why not attempt to run the code and use an On Error Resume Next statement to ignore the error if the field does not exist? You can use something like:

Sub DeleteMyField
    On Error Resume Next
    DoCmd.RunSQL ("ALTER TABLE " & test2 & " DROP COLUMN Branch Distance;")
End Sub

If the Branch Distance field does not exist, no error will be reported.

I do have a suggestion for the FieldExists function. You should terminate the testing when a name match is found. Why keep going through the field names?

Public Function FieldExists(tableName As String, fieldName As String) As Boolean
On Error GoTo FieldExists_Exit

    Dim fld As Field

    For Each fld In CurrentDb.TableDefs(tableName).Fields
        If fld.NAME = fieldName Then
           FieldExists = True
           Exit For  'Or Exit Function
        End If
    Next

FieldExists_Exit:
End Function

 

[niftysam]

Yup, that was exactly what I thought, but I just wish I could have figured it out the other way. It seems like relying on the on error mechanism isn't the strongest solution...but i was tired of being stuck

Thanks for the help!!! I couldn't get through this project without y'all!!

Sam

 

[The_Doc_Man]

Don't know if this works for ODBC, but for local tables you could try to get a property of the object and see if it succeeded.

If field XYZ exists, it has an entry in the fields collection of the tabledef.

So if you wanted to see if field XYZ existed in table ABC, you might be able to look up its name properly using its collection membership.

...
LName = ""
On Error Resume Next
LName = TableDefs("ABC").FieldDefs("XYZ").Name
if LName = "XYZ" then (goto field-exists)
else
goto field-is-junk
end if
...

Because if the field exists by that name, you KNOW it has a name attribute.

Look up the items in the Help Files to verify their structure. Don't trust me to have gotten it exactly right. I'm shooting from the hip at the moment.