Field name to Record? 1

[gnt]

Is there anyway to run a query that lists the field names in a table? For example, a table with fields "Mon", "Tues", "Wed" would query like this:

Fields
Mon
Tues
Wed

Am I dreaming here???

 

[cmmrfrds]

There are a number of ways - here is one.

Function catalogTCCat()
'-- 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

Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables

If tb.Type = "TABLE" Then
'If tb.Type = "LINK" Then
Debug.Print "table name = "; "-------"; tb.Name; "--------"; tb.Type

For Each cl In tb.Columns
Debug.Print "property name = "; cl.Name
Next
End If
Next
End Function

 

[gnt]

Thanks! That was quick!
My real problem is, I'm trying to run a query with calculations, and I need these field names to actually be records in a query. Does that make sense? I'm sure that I can get it to work the way you described, and I'll keep trying to figure out how. But if you know a quick way to generate this query, please share!
Thanks again-

 

[cmmrfrds]

Okay, I see. What I showed was how to read the catalog to get the field names. Any SQL query returns meta data, which includes a fields collection, which are the fields that are used in the select statement. I will attach an example of using the fields collection. The example only uses the fields but you can go ahead and process the data like any recordset.

Example.

Function GetFieldNames(theTable As String) As Long
Dim SqlString As String, RSMT As New Recordset, cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim indx As Integer, Upper As Long
''Open the table and the fields are accessible
RSMT.Open theTable, cnn, adOpenStatic, adLockReadOnly

Upper = RSMT.Fields.Count - 1
ReDim glbFieldNameList(Upper)
Debug.Print "field Count = "; RSMT.Fields.Count

For indx = 0 To (RSMT.Fields.Count - 1)
Debug.Print "field Number = "; indx
'' glbFieldNameList(indx) = RSMT.Fields(indx).Name
Debug.Print "field Names = "; RSMT.Fields(indx).Name
Debug.Print "field Type = "; RSMT.Fields(indx).type
Next '-- end for

GetFieldNames = Upper ' Returns number of elements
'---- number of occurrances in Array
'''GetFieldNames = (Upper = UBound(glbFieldNameList)) ' Returns number of elements
End Function

 

[gnt]

Thank you!
I'll definitely hold on to this function!