Field names

[Michael S. Montoya]

I have a crosstab query that I would like to send to Excel. The column
headings are the names of Suppliers used in a given time preiod. Of course,
crosstab queries change the field names to reflect the data in the given
time period.

What is the code to loop through all the fields of a query? I am guessing
this would be a way to handle this.

 

[Ron Weiner]

Michael

Here is a Sub that will print all of the recordsets fieldnames to the
immediate window.

Public Sub FieldNames(strSql As String)
Dim rs As ADODB.Recordset, i As Integer

Set rs = New ADODB.Recordset
rs.Open strSql, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
For i = 0 To rs.Fields.Count - 1
Debug.Print rs.Fields(i).Name
Next
rs.Close
Set rs = Nothing
End Sub

You would use it like this:

Call FieldNames("Select * from tblYourTable")

Ron W

 

[Jamie Collins]

I have a crosstab query that I would like to send to Excel. The column
headings are the names of Suppliers used in a given time preiod. Of course,
crosstab queries change the field names to reflect the data in the given
time period.

What is the code to loop through all the fields of a query? I am guessing
this would be a way to handle this.

Probably the easiest to way to create a new Excel table is to use the
SELECT..INTO syntax e.g. if you query looked like this:

SELECT MyTextCol
FROM MyTable
;

then add an INTO clause like this:

SELECT MyTextCol
INTO [Excel 8.0;HDR=YES;Database=C:\MyWorkbook.xls;].MyNewExcelTable
FROM MyTable
;

And if you just wanted the column headers, add a WHERE clause like
this:

SELECT MyTextCol
INTO [Excel 8.0;HDR=YES;Database=C:\MyWorkbook].MyExcelTable
FROM MyTable
WHERE 0=1
;

For an existing Excel table, use the INSERT INTO..SELECT syntax.

Jamie.

--