I want to make a temporary table in Excel memory to run SQL statements on.
Is this possible. I see examples of a Select into with results going into a
worksheet. Is it possible to create a temporary table that I could then run
futher select statements against? If so, how and what variable type is
defined "DIM"?

Thanks!

Try SELECT ....... FROM ......... INTO ARRAY [ArrayNameHere without
brackets]

The array need not be defined with DIM, it is created automatically with
above select statement.

Another way is to use connection object and recordset object as under:

Dim myConn As Connection, myRec As RecordSet
Set myConn = New Connection
Set myRec = New RecordSet
myConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[file-path w/o
brackets]"

Now use your one kind of select statement as under:-
myRec.Open "Your SELECT STATMENT -1 (Without INTO)" , myConn, _
adOpenKeyset, adLockOptimistic, adCmdText
So the select data is in myRec RecordSet (in memory)

When done with above and you need another SELECT statment:

Set myRec = Nothing
Set myRec = New RecordSet
myRec.Open "Your SELECT STATMENT -2 (Without INTO)" , myConn, _
adOpenKeyset, adLockOptimistic, adCmdText

and so on.

Sharad

"dave k" wrote in message
...
I want to make a temporary table in Excel memory to run SQL statements on.
Is this possible. I see examples of a Select into with results going into
a
worksheet. Is it possible to create a temporary table that I could then
run
futher select statements against? If so, how and what variable type is
defined "DIM"?

Thanks!

Sharad Naik wrote:
I want to make a temporary table in Excel memory to run SQL
statements on.

Try SELECT ....... FROM ......... INTO ARRAY [ArrayNameHere
without
brackets]

The array need not be defined with DIM, it is created automatically
with
above select statement.

I don't see how this could work. The OP is referring to Excel data, so
I would expect you answer to use Jet SQL syntax. I'm not aware of Jet
having an Array keyword (and how does the array move from SQL engine to
memory?) Jet has a SELECT..INTO..FROM syntax but the INTO must precede
the FROM clause. Your SQL looks more like FoxPro SQL. Even if it did
work, you can't use a SELECT on an in-memory array...

Another way is to use connection object and recordset object

I don't see how this works either. This creates an in-memory recordset
which can in turn be output as an in-memory array, however neither of
which can be subsequently queries using a SELECT statement, which is
what the OP asked for. ADO.NET has the concept of in-memory tables but
the same is not true for ADO classic, for which a recordset is
essentially a sequential cursor.

Jet's SELECT..INTO..FROM can be used to create a temporary Excel
workbook (.xls) on *disk* which can be subsequently queried then
Killed.

Jamie.

--

Thanks a lot Jamie for pointing this out, I indeed completely missed it out
that
I was mixing the ForPro SQL with Jet ; when replied.

Your suggestion of putting into temporary excel file is of course good and
very simple to use,
but I thought OP didn't want it either, since he must have considered adding
an temporary worksheet and deleting it when done.

He can't further query RecordSet using the SELECT statement, and that's
why I told him, create RecordSet based of required SELECT crieteria,
work with it when done, Set RecordSet to Nothing and build again
with next SELECT statement.

Sharad


"onedaywhen" wrote in message
ups.com...

Sharad Naik wrote:
I want to make a temporary table in Excel memory to run SQL
statements on.

Try SELECT ....... FROM ......... INTO ARRAY [ArrayNameHere
without
brackets]

The array need not be defined with DIM, it is created automatically
with
above select statement.

I don't see how this could work. The OP is referring to Excel data, so
I would expect you answer to use Jet SQL syntax. I'm not aware of Jet
having an Array keyword (and how does the array move from SQL engine to
memory?) Jet has a SELECT..INTO..FROM syntax but the INTO must precede
the FROM clause. Your SQL looks more like FoxPro SQL. Even if it did
work, you can't use a SELECT on an in-memory array...

Another way is to use connection object and recordset object

I don't see how this works either. This creates an in-memory recordset
which can in turn be output as an in-memory array, however neither of
which can be subsequently queries using a SELECT statement, which is
what the OP asked for. ADO.NET has the concept of in-memory tables but
the same is not true for ADO classic, for which a recordset is
essentially a sequential cursor.

Jet's SELECT..INTO..FROM can be used to create a temporary Excel
workbook (.xls) on *disk* which can be subsequently queried then
Killed.

Jamie.

--

Sharad Naik wrote:

He can't further query RecordSet using the SELECT statement, and
that's
why I told him, create RecordSet based of required SELECT crieteria,
work with it when done, Set RecordSet to Nothing and build again
with next SELECT statement.

An alternative to creating multiple recordsets in this way would be to
use the MSDataShape provider (coupled with a regular data provider) and
the SHAPE syntax to create a single hierarchical recordset (i.e. a
recordset of recordsets) in one hit.

I think we're guessing a bit here, though. Perhaps the OP would like to
outline or detail his needs. It could be that using the Recordset
object's Filter property would suffice, as this has the effect of
re-querying the recordset as
SELECT * FROM <recordset WHERE <Filter

Jamie.

--

Thanks for all the input. Here is more information about what I want to do.

Run a query with about 5 calculated fileds. Have this query create a
temporary table in memory.
Use this new temporary table to perform additional queries against other
tables (primarily INNER JOINs).
I would prefer to keep the NEW table in memory and not in a worksheet if
possible.
I would also prefer to have this new table vs. embedding the query into the
subsequent queries for speed and for subsequent ease.

I just don't know if it is possible to have a temporary table in memory from
an INTO statement. When I use SELECT...INTO...FROM I get an undefined object
error.

Thanks for the help.
Dave



"onedaywhen" wrote:

Sharad Naik wrote:

He can't further query RecordSet using the SELECT statement, and
that's
why I told him, create RecordSet based of required SELECT crieteria,
work with it when done, Set RecordSet to Nothing and build again
with next SELECT statement.

An alternative to creating multiple recordsets in this way would be to
use the MSDataShape provider (coupled with a regular data provider) and
the SHAPE syntax to create a single hierarchical recordset (i.e. a
recordset of recordsets) in one hit.

I think we're guessing a bit here, though. Perhaps the OP would like to
outline or detail his needs. It could be that using the Recordset
object's Filter property would suffice, as this has the effect of
re-querying the recordset as
SELECT * FROM <recordset WHERE <Filter

Jamie.

--