Update all columns without column names

Question

Is there any way to write a function that could be run in a query on a single column that would modify all textual columns without including the column names as input parameters?

Example: Table with [last], [first], [middle], [address], [date]

Update - results would be MyUcase of [last], [first], [middle], and [address].

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Update all columns without column names
Date: 5 June 1998 11:46

Jusy,

Yes there is such a way:

- create a function:

Function MyChangeCase(byval vstrTableName as string, _
			 byref rdbs as database)
 dim tdf as tabledef
 dim fld as field
 dim strSql as string
 dim blnTextFldsFound as boolean

 strSql = "update [" & vstrTableName & "] set "
 blnTextFldsFound = False
 set tdf = rdbs.tabledefs(vstrTableName)

 for each fld in tdf.fields
    if fld.type = dbText then
      strSql = strSql & "[" & fld.name & "] = MyUCase([" & fld.name & "],"
      blnTextFldsFound = True
    endif
 next
 
if blnTextFldsFound then
  strSql = Left(strSql, len(strSql)-1)       
  rdbs.execute strSql
endif

end function

or you can use recordet the similar way invetigating its Fields collection.

HTH,
Shamil