field alias when running SQL on sheet ranges
 

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

field alias when running SQL on sheet ranges
 

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" wrote in message
...
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

field alias when running SQL on sheet ranges
 

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" wrote in message
...
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" wrote in message
...
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

field alias when running SQL on sheet ranges
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

field alias when running SQL on sheet ranges
 

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

RBS


"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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