Field caption

Anybody knows how to retrieve the caption of Access 2000 database field? And maybe there is a way to read the description of the field that written on the right side of table design layout?

Thanks in advance

Use the Field object (part of the Fields collection).

For example.
Loop thru all fields in the table and print the name and type

Set rcsTest = YourDB.OpenRecordset("Your Table")

with rcsTest
For i = 0 To .Fields.Count - 1
Debug.Print .Fields(i).Name & ",";
Select Case .Fields(i).Type
Case 4
Debug.Print "Long Integer" & ",";
Case 8
Debug.Print "Date/Time" & ",";
Case 10
Debug.Print "Text" & ",";
Case 12
Debug.Print "Memo" & ",";
End Select
next i
.close
end with

Hope this is of assistance
Adrian.

Thanks for answering, but it's not that. I can loop through all properties of the field, but "Caption" and "Description" don't appear between them. It's information stored somewhere inside .mdb file, the question is "where?". Any ideas?

I am using adodc to retrieve a recordset from a SQL Server database. Using VB I can access the name property of the field.

My sql statement looks like this : "select dbname as name, dbage as age"

adodc.recordset.fields(n).name and it shows me the fields name as defined in my sql statement (i.e. name and age instead of dbname and dbage).

Maybe the access fields work the same way. I'm not sure but it's worth a try!

I think you have to do something like CreateProperty to create the "Description" property and add it to the field's property collection, then you can access the field descriptions in your tables. I was never able to do it, myself.

Access has this to say...

Quote:

---

For example, Microsoft Access defines the Description property of a TableDef object. If this property hasn't already been set from table Design view, you must use the CreateProperty method to create the property and then append it to the Properties collection in order to set the property from Visual Basic.

A Microsoft Access–defined property is automatically added to the Properties collection when it is first set from the Microsoft Access window. If the property has already been set in this way, then you don't need to add it to the Properties collection.

---

and you can access the pre-set caption by

Code:

---

Dim tb As TableDef
Set tb = db.TableDefs("Table")
MsgBox tb.Fields("MyField").Properties("Caption")
etc.

---

If the caption is not preset you would use

Code:

---

Dim prpNew as Property

Set prpNew = .Fields("MyField").CreateProperty("Caption", dbText, "My Caption Here")
.Fields("MyField").Properties.Append prpNew

---

Note that properties are added to the individual fields as separate properties, so it is perfectly possible that some will have been pre-set and therefore accessible from VBA and others will not be pre-set, so you must Error trap and use CreateProperty where necessary. Check the Access help on CreateProperty - it is quite good.

Cheers,

Paul.

P.S.
Tx to jmcswain for putting us on the right track <respect>

Hi, Paul!
Example you provided works fine with DAO, and I have to deal with ADO. But thanks for giving the hint. I found the following way to access field properties:

Dim db as New ADODB.Connection
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;"

Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = db

Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim i As Long
Set tbl = cat.Tables("tblCity")

For Each col In tbl.Columns
Debug.Print col.Name, col.Type
For i = 0 To col.Properties.Count - 1
Debug.Print col.Properties(i).Name & " = " & col.Properties(i).Value
Next
Next col

This really returns the value of "Description", while caption is still "hidden". To say the truth, description is quite enough for me, but from the pure interest, where the hell do they store that "Caption" value? It's an existing property, not my custom one...