View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Sharad Naik Sharad Naik is offline
external usenet poster
 
Posts: 212
Default SQL - SELECT INTO making temporary Excel table

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!