Geting filds caption from a recordset

Hi,

I'm trying to automatically print the content of a table from a Jet/SQL7 database on a form.

I've constructed the tables and assigned a caption to each field in the database.
I've built an SQL statement which fills an ADODB.Recordset with all the information I need.

Now the following step is the one I have problems with.
I need to get to the Captions I entered in to the table definition in order to write the table's (Recordset's) header.

Who do I reach the captions I defined in the database threw the Recordset I created ?

Thanks

Erez

you could use the ADOX Library (VB6 + SP3 i think). be sure to reference 'Microsoft ADO Ext. 2.5 for DDL and Security' when trying something like this:

code:

Public Sub LoadDefaults(ByVal Table As String) As Variant
On Error GoTo Err_LoadDefaults

Dim x As New ADOX.Catalog, t As New ADOX.Table
Dim c As New ADOX.Column

Set x.ActiveConnection = GetConnection()
Set t = x.Tables(Table)

For Each c In t.Columns
Debug.Print c.Name & vbCrLf & _
c.Type & vbCrLf & _
c.DefinedSize & vbCrLf & _
c.Properties("Default")
Next

Set c = Nothing
Set t = Nothing
Set x = Nothing

Exit Sub

End Sub

best regards

Hi,

Ok, I did as Sascha suggested and it works it prints out the name property of the columns.

It gave the same result as <Recordset>.Fields("<FieldName>").Name

But,

Since I don't want my user to receive as the headers of his table names like "fldID", "fldCustNum" etc I defined a Caption for each column in the database (AI "ID", "Customer Num" etc') What I need is to reach the captions of the table ("ID", "Customer Number" etc').


Is one of the properties contains the Caption of the fields ?

Can I reach these captions ?

Thanks

Erez

i am afraid there is no property for that in the collection, but i have 2 suggestions:

1. you could choose a strategy for your column names, for instances take 'fldCustomerNum' and then access the .Name property, delete the first 3 chars and put an empty space before every capital letter. takes a bit of time, but has only to be done once, when loading the form.

2. don't access your tables directly, use views where you can change the column's name, something like:
'Select fldCustNum As [Customer Number] From tblCustomer'

best regrads

OK,

using SELECT <fldName> as <Caption> worked !

A little harder then usual but that’s way we’re getting so much money for what we’re doing ;-)

Thanks

Erez