See if Field exists

[MikeKohler]

I have a database that brings in a linked table through an odbc connection, when I bring it in I have a module that creates a field. What I am asking is if there is a way to see if that field exists such that it would look something like this

If field does not exist
then create field
Else

Thanks, Mike Kohler
mkohler@telusplanet.net

 

[KF]

You could try and add the field and if it is there then the following code will not add it as Access will only let you add a field to a table once.

Dim MyWorkspace As Workspace
Dim MyDatabase As DATABASE
Dim MyField As Field
Dim MyTableDef As TableDef

Set MyWorkspace = DBEngine.Workspaces(0) ' Get current workspace.
Set MyDatabase = MyWorkspace.OpenDatabase(YourPathannDatabaseNameHere) ' Open database.
Set MyTableDef = MyDatabase![YourTableNameHere]

Set MyField = MyTableDef.CreateField("YourFieldNameHere", dbBoolean)

MyTableDef.Fields.Append MyField ' Append field to collection.

Hope this helps,

 

[MikeKohler]

Thanks for the reply, but I already know how to create a field. My problem was that I am still adjusting the code that I have written, it would be convenient that I didn't have to delete the fields that I have created every time I run the code. I am adding things like creating indexes etc.
Thanks, Mike Kohler
mkohler@telusplanet.net

 

[MisterC]

Maybe something like this will work for you -
assuming you have a recordset variable called rst:

DIM fld       AS field
DIM AddField  AS Boolean
    AddField = True

FOR EACH fld in rst.fields
   IF fld.name = "MyFieldName" THEN
      AddField = False
      EXIT FOR
   END IF
NEXT

IF AddField = True THEN
  'Add the Field...

--------------
A little knowledge is a dangerous thing.

 

[MikeKohler]

Hey, thanks for the answer, that was exactly what I was looking for.
Michael Kohler
mkohler@telusplanet.net