How to "order by" a column and "Include Column Name with query"?

Question

Hi, I am trying to run a sql query in excel and I want to :

1. order the query result by my column "Stationname"
2. include the column names with the query

Right now it is returning all the columns without the column name, and the end users do not know what it is.

Could someone please help? I am stuck! Below is my current code:

    strQuery = "select pipelineflow.lciid lciid, ldate, volume, capacity, status, " & _
        "pipeline, station, stationname, drn, state, county, owneroperator, companycode, " & _
        "pointcode, pointtypeind, flowdirection, pointname, facilitytype, pointlocator, " & _
        "pidgridcode from pipelineflow, pipelineproperties " & _
        "where pipelineflow.lciid = pipelineproperties.lciid " & _
        "and pipelineflow.audit_active = 1 " & _
        "and pipelineproperties.audit_active =1 " &
_
        "and pipelineflow.ldate " & dtInDate & _
        "and pipelineproperties.stationname = '" & Stationname & "' "

Answer 1

For ordering just put

Order By stationname

at the end of the Query.

You can iterate through the column names by using:

rst(1).Name

where rst is your recordset, and the number is the index of the column.

Answer 2

For part 1 of your question, add an ORDER BY clause to your query. In this case: order by stationname

Part 2: Not sure why column names aren't being included in your query. You can explicitly name a column using something like the following (purely an example):

select mycolumn as "MyCustomizedColumnName" from mytable

That allows you to give columns names of your choosing. Having said that, you shouldn't be required to do so for every column, so I suspect something else is going on in your case.

---

I should probably add that a stored procedure (rather than dynamic SQL) will yield better runtime performance.

Answer 3

To sort your query results , use 'ORDER BY' at the end of the query. The last lines of your query would look like this

"and pipelineproperties.stationname = '" & Stationname & "' " & _
"ORDER BY pipelineproperties.stationname"

The column heading are returned in your query data, but not automatically written to the Excel worksheet. The code snippet below shows how to loop through the recordset's column headings and write them to the active cell's row.

'rst' refers to your recordset, update the name as required.

If Not rst.EOF Then
    For x = 0 To rst.Fields.Count - 1
     With ActiveCell.Offset(0, lcount)
        .Value = rst.Fields(x).Name
     End With
    Next
End If

Make sure that you offset down from the active cell when writing the query results to the worksheet, otherwise your headings will be overwritten by the data.

Activecell.Offset(1,0).CopyFromRecordset rst