field alias when running SQL on sheet ranges

R

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
 
B

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)
 
R

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
 
B

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)
 
R

RB Smissaert

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

RBS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top