"Reverse" Crosstab query OR loop thru column names in code

[laakins]

I have a table/query/recordset that has several columns. I want to match the names of the columns to other values. If the name matches I want to take the name of the column and make it a data value in a column of another table - sort of a reverse crosstab query only instead of taking values & making column headers out of it I want to take column headers & make them values.

 

[cmmrfrds]

There are a number of ways to obtain meta data on table definitions. You can also obtain meta data such as field names on a resultset.

ADO example.

Function catalogTC()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As Column
Dim pp As Property

Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables
Debug.Print "table name = "; "-------"; tb.Name; "--------"; tb.Type


If (tb.Type = "TABLE" And tb.Name = "Categorys&quot Then
For Each cl In tb.Columns
Debug.Print "name = "; cl.Name
For Each pp In cl.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value
Next
Next
End If
Next

End Function

Find field names in Resultset.

Function FindFieldNames(theTable As String, nameToCheck) As String

Dim foundOnes As String, RSMT As New Recordset, cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim indx As Integer

''Open the table and the fields are accessible
RSMT.Open theTable, cnn, adOpenStatic, adLockReadOnly

For indx = 0 To (RSMT.Fields.Count - 1)
If RSMT.Fields(indx).Name = nameToCheck Then
foundOnes = theTable & " -- " & nameToCheck
Debug.Print "Table and Field = "; foundOnes
End If
Next '-- end for

FindFieldNames = foundOnes ' Returns the ones found if wanted
End Function