Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: Check for field in tabledefs prior to append

Posted on 2008-04-22 23:11:20 by 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

Report this message

#2: Re: Check for field in tabledefs prior to append

Posted on 2008-04-23 07:27:41 by Tom van Stiphout

On Tue, 22 Apr 2008 14:11:20 -0700 (PDT), timmg
<tmillsgroninger@gmail.com> 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

Report this message

#3: Re: Check for field in tabledefs prior to append

Posted on 2008-04-23 15:02:37 by timmg

On Apr 23, 12:27=A0am, Tom van Stiphout <no.spam.tom7...@cox.net> wrote:
=2E..
>
> 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

Report this message