Check for field in tabledefs prior to append

The following code works well except I can't think of an elegant way
of checking for the existance of the named field prior to creating
it. Suggestions?

Public Sub pNewField()
'Add a new field to every table in the collection
Dim strSQL As String
Dim db As Database, tdf As TableDef, intI As Integer

Set db = CurrentDb

For intI = 0 To db.TableDefs.Count - 1
Set tdf = db.TableDefs(intI)
' Skip system tables
If Left(tdf.Name, 4) <> "MSys" Then
tdf.Fields.Append tdf.CreateField("Source_tbl", dbText,
15)
strSQL = "UPDATE [" & tdf.Name & "] SET Source_tbl = " _
& "'" & tdf.Name & "';"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
Next

End Sub

Thanks,

Tim Mills-Groninger

Re: Check for field in tabledefs prior to append

On Tue, 22 Apr 2008 14:11:20 -0700 (PDT), timmg
wrote:

You can walk the Fields collection to find out if a name exists.
The other thing you can do is to simply let it happen and have a good
error handler (which I'm sure you omitted here for simplicity :-) )
deal with the aftermath.

-Tom.


>The following code works well except I can't think of an elegant way
>of checking for the existance of the named field prior to creating
>it. Suggestions?
>
>Public Sub pNewField()
>'Add a new field to every table in the collection
>Dim strSQL As String
>Dim db As Database, tdf As TableDef, intI As Integer
>
> Set db = CurrentDb
>
> For intI = 0 To db.TableDefs.Count - 1
> Set tdf = db.TableDefs(intI)
> ' Skip system tables
> If Left(tdf.Name, 4) <> "MSys" Then
> tdf.Fields.Append tdf.CreateField("Source_tbl", dbText,
>15)
> strSQL = "UPDATE [" & tdf.Name & "] SET Source_tbl = " _
> & "'" & tdf.Name & "';"
> DoCmd.SetWarnings False
> DoCmd.RunSQL strSQL
> DoCmd.SetWarnings True
> End If
> Next
>
>End Sub
>
>Thanks,
>
>Tim Mills-Groninger

Re: Check for field in tabledefs prior to append

On Apr 23, 12:27=A0am, Tom van Stiphout wrote:
..
>
> You can walk the Fields collection to find out if a name exists.
> The other thing you can do is to simply let it happen and have a good
> error handler (which I'm sure you omitted here for simplicity :-) )
> deal with the aftermath.

Thanks, I pinged the group to see if there was a more elegant solution
than looping through the field name. Maybe error handling is it -
which I naturally ommitted for simplicity (being simple minded counts,
doesn't it?)

Tim Mills-Groninger