Get column names from Access crosstab query via ADO?

[Michael Edwards]

I am trying to develop a report from an Access 2000 crosstab query using
ADO. I will enter the column headings from the crosstab query into labels.

So far, in code, I connect to the current project and run the crosstab query
into an ADO recordset. I believe the column names should be exposed by the
ADO recordset but I cannot find any documentation on how to get the column
name.

Before I get suggestions on how to do it other ways, I know most of them;
however, I want to get away from the DAO code, the number of columns and
rows change from report to report run as well as the headings themselves.
There is no way to synthetically create the heading names by Date Functions
or serializing Functions. The number of columns will be between 1 and 100.

The bottom line question is -- how do I get the column names from the
crosstab query via ADO?

 

[MGFoster]

I am trying to develop a report from an Access 2000 crosstab query using
ADO. I will enter the column headings from the crosstab query into labels.

So far, in code, I connect to the current project and run the crosstab query
into an ADO recordset. I believe the column names should be exposed by the
ADO recordset but I cannot find any documentation on how to get the column
name.

The bottom line question is -- how do I get the column names from the
crosstab query via ADO?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The same way as in DAO, you use the Recordset Field's Name property:

Dim rs As ADO.Recordset
Dim fld As ADO.Field
... connect & set rs ...
for each fld in rs.Fields
debug.print fld.Name
next fld

returns the name of each column in the recordset.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFsd44echKqOuFEgEQKuowCfVPn65mCMhsfmG0BQv/nrUVeoiMsAoNDZ
8x/tP7H0fWfSjIAb7PKuzxcn
=RmxT
-----END PGP SIGNATURE-----

 

[Michael Edwards]

Thanks, I was brain dead, I had a mistake in my code by referring to
rs.fields.Name instead of fld.Name.

 

[Duane Hookom]

You might consider the crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This is much less code
and you could convert the simple DAO to ADO. You don't have to deal with
grabbing field names from any recordset. You actually pull the field names
from data since they start in your original data prior to becoming
field/column names in your crosstab.