Check for field in tabledefs prior to append

[timmg]

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

[Tom van Stiphout]

On Tue, 22 Apr 2008 14:11:20 -0700 (PDT), timmg
<tm*************@gmail.comwrote:

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

[timmg]

On Apr 23, 12:27*am, Tom van Stiphout <[e-mail]:
...

>
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