Testing backend table for existence of a field.

[Gibson]

Is it possible to determine if a field exists in a table through code. Let
me explain. I have a database system with a backend and a frontend. I want to
update the backend by adding a new field into a table. When I open the main
form I want to test the backend table for the existence of the field. (There
may be no data in the field) If the field does not exist in the table then
my code adds the field to the table. I know how to test for data in a field,
I'm not sure how to test for existence of the field itself.

Anybody give me some clues. Thanks.

 

[John Vinson]

Is it possible to determine if a field exists in a table through code. Let
me explain. I have a database system with a backend and a frontend. I want to
update the backend by adding a new field into a table. When I open the main
form I want to test the backend table for the existence of the field. (There
may be no data in the field) If the field does not exist in the table then
my code adds the field to the table. I know how to test for data in a field,
I'm not sure how to test for existence of the field itself.

Anybody give me some clues. Thanks.

Open the Tabledef for the field and trap the error if the field isn't
in the Fields collection:

Public Sub CheckField(Tablename As String, Fieldname As String)
Dim db As DAO.Database
Dim tdf As DAO.Tabledef
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.Tabledefs(Tablename)
On Error GoTo Proc_Error
Set fld = tdf.Fields(Fieldname)
Proc_Exit:
Exit Sub
Proc_Error:
If Err.Number = < whatever the error is > Then
MsgBox "Field " & FieldName & " does not exist"
Resume Proc_Exit
Else
<normal error handling>
End If
End Sub

John W. Vinson[MVP]

 

[Gibson]

Great idea. I trapped the error and in the error procedure I put the code to
add the field and it works great. Thanks for the help!

 

[mscertified]

Public Function ifexists(strCol as string) As Boolean
Dim x As Variant
On Error GoTo ER
x = DLookup(strCol, "tblCodes", "Code = 0")
Ifexists = true
Exit Function
ER:
' MsgBox Err.Description, vbOKOnly, "Error: " & Err.Number
Ifexists = false
End Function

-Dorian