field alias when running SQL on sheet ranges

[RB Smissaert]

Is it possible to the header fields when running SQL on sheet ranges?
The connection is like this:

strSheetConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.FullName & ";" & _
"Extended Properties=Excel 8.0;"

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=strSheetConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

Tried all sorts of constructions, but nil worked sofar.

Also, would it be possible to run SQL on sheet ranges without saving the
workbook first?
I suppose not as SQL always runs on disk (files) structures rather than data
in memory.

RBS

 

[Bob Phillips]

To what the header fields? Do you mean see them? If so, the recordset has a
Fields collection property that you can iterate through.

On the second point, you are correct, it has to be saved.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RB Smissaert]

What I mean is this:
In normal SQL you can do:

SELECT
E.TERM_TEXT FIELDALIAS
FROM
ENTRY E
WHERE
E.READ_CODE = 'G25..'

This means you will get FIELDALIAS in your output header, rather than
TERM_TEXT.

How would you do the same in range SQL?
You could of course loop through the fields in the recordset and replace the
fields names in the sheet output, but it would be easier if you could do it
in SQL.

RBS

 

[Bob Phillips]

Oky, I understand.

Use the As operator

SELECT
E.TERM_TEXT AS FIELDALIAS
FROM
ENTRY E
WHERE
E.READ_CODE = 'G25..'


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RB Smissaert]

Thanks Bob, that is the one I forgot about.
Works nicely even with Interbase.

RBS