Retrieve the Caption property of a field

[Adam Goossens]

Hi all,

Does anybody know how I can read the value of the Caption property of a
SQL Server field (as set by Access) at run time?

I need to export some data to Excel (I can't assume it'll always be one
kind of data - I want to be able to pass any SQL string and spit out an
Excel document) and I'm thinking of using the Caption property of the
various fields to store a "user friendly" name, aka "Vendor Name"
instead of "fldVendorName".

Or, if anybody has any alternate suggestions for what I wish to do, I'd
be glad to hear them!

Many thanks,
-Adam.

 

[pjgalloway]

I have not worked with captions on a SQL table but with Access tables the
only trick is that it will bomb if there is not a caption defined for the
field so you need some error handling to deal with that case to perhaps just
use the field name. Note that if you are referencing both ADO and DAO as I
am, then you do need to use a DAO field variable and not an ADO field
variable.

On Error GoTo HandleErrors
Dim strCaption as string
Dim db As Database
Dim tdf As TableDef
Dim fld As DAO.Field
Set db = CurrentDb()
Set fld = tdf.Fields("yourfield")
strCaption = ""
On Error Resume Next
strCaption = fld.Properties("Caption")
On Error GoTo HandleErrors
If strCaption = "" Then strCaption = fld.Properties("Name")