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.Co unt - 1
Set tdf = db.TableDefs(in tI)
' Skip system tables
If Left(tdf.Name, 4) <"MSys" Then
tdf.Fields.Appe nd tdf.CreateField ("Source_tbl ", dbText,
15)
strSQL = "UPDATE [" & tdf.Name & "] SET Source_tbl = " _
& "'" & tdf.Name & "';"
DoCmd.SetWarnin gs False
DoCmd.RunSQL strSQL
DoCmd.SetWarnin gs True
End If
Next

End Sub

Thanks,

Tim Mills-Groninger

[Tom van Stiphout]

Re: Check for field in tabledefs prior to append

On Tue, 22 Apr 2008 14:11:20 -0700 (PDT), timmg
<tmillsgroninge r@gmail.comwrot e:

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.Co unt - 1
Set tdf = db.TableDefs(in tI)
' Skip system tables
If Left(tdf.Name, 4) <"MSys" Then
tdf.Fields.Appe nd tdf.CreateField ("Source_tbl ", dbText,
>15)
strSQL = "UPDATE [" & tdf.Name & "] SET Source_tbl = " _
& "'" & tdf.Name & "';"
DoCmd.SetWarnin gs False
DoCmd.RunSQL strSQL
DoCmd.SetWarnin gs True
End If
Next
>
>End Sub
>
>Thanks,
>
>Tim Mills-Groninger

[timmg]

Re: Check for field in tabledefs prior to append

On Apr 23, 12:27 am, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:
...

>
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