My VBA code which makes an ADO connection to an Excel 2007
file typically looks like this:

'================================================= ==========
' Create the Recorset object and run the query.

szSQL = "SELECT * FROM [Sheet1$A1:A1]"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

' Extract data from rsData recordset object

rsData.Close
Set rsData = Nothing
'================================================= =========


So, my question is, once I have an open ADO connection, is there any way to
make
another SQL query before running the "rsData.Close" command?? Or, do I
simply
need to close the connection and then run the Open command with a new
"szSQL"
string command??

thanks!

Robert Crandal pretended :
My VBA code which makes an ADO connection to an Excel 2007
file typically looks like this:

'================================================= ==========
' Create the Recorset object and run the query.

szSQL = "SELECT * FROM [Sheet1$A1:A1]"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

' Extract data from rsData recordset object

rsData.Close
Set rsData = Nothing
'================================================= =========


So, my question is, once I have an open ADO connection, is there any way to
make
another SQL query before running the "rsData.Close" command?? Or, do I
simply
need to close the connection and then run the Open command with a new "szSQL"
string command??

thanks!

You can leave the connection open and run new SQLs whenever you need
them. So then, you could pull data from several worksheets and store it
in separate recordsets if desired.

In this case, you need to close the connection when your project is
shutting down.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

That's the problem.... I don't know HOW to run a new SQL command on
an open connection. What is the command to run a new SQL? Is there
some sort of "Execute" method that runs from a recordset object or
something?

Thanks!


"GS" wrote in message
...

You can leave the connection open and run new SQLs whenever you need them.
So then, you could pull data from several worksheets and store it in
separate recordsets if desired.

In this case, you need to close the connection when your project is
shutting down.

All you should need to do is close the existing recordset, but don't
close the connection and use code similar to what
you've already posted for the next query.



szSQL = "SELECT * FROM [Sheet1$A1:A1]"


Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText


' Extract data from rsData recordset object


rsData.Close

szSQL = "SELECT * FROM [Sheet1$A2:A2]"

rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText

' Extract data from rsData recordset object


rsData.Close

....

Set rsData = Nothing

By the way why does your SQL appear to only be for one cell?

It doesn't make a lot of sense to use ADO to extract such small
amounts of data from a worksheet.

In your code below, when you call "rsData.Close" the first time,
doesn't that close the connection?? It looks like you are
closing the first connection, then making a new connection with
a new query. Is that right?

BTW, I will only be extracting data from a couple cells on different
sheets, thats why I am using references line "$A1:A1" or "D12:12".
I was hoping to extract this data as quickly as possible....so I figured
it would make sense (to me) to merely extract the individual data
that I need without closing the connection to the same file with a
series of "Open" and "Close" commands. I will also be extracting the
same data from a couple hundred Excel 2007 files, so I am trying
to consider all possible ways to speed up my search process.


"norie" wrote in message
...
All you should need to do is close the existing recordset, but don't
close the connection and use code similar to what
you've already posted for the next query.



szSQL = "SELECT * FROM [Sheet1$A1:A1]"


Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText


' Extract data from rsData recordset object


rsData.Close

szSQL = "SELECT * FROM [Sheet1$A2:A2]"

rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText

' Extract data from rsData recordset object


rsData.Close

...

Set rsData = Nothing

By the way why does your SQL appear to only be for one cell?

It doesn't make a lot of sense to use ADO to extract such small
amounts of data from a worksheet.

I could be wrong, but the bit you're missing seem to be another object
varialbe of type ADODB.Connection
You should go like:
Dim cn as ADODB.Connection
Set cn= new ADODB.Connection
cn.open (put all the connection bits in here)
Now - you've got your connection object open.
Then, when you run your
rst.open sql
instead of the szConnect (which i'm guessing is just a string and not
an object) you'd use the newly created (and opened) cn object.
Then, when your'e closing your rst - the connection object will retain
it's state (open in this case).

That is, if i havnet misunderstood your setup.



On Sep 30, 11:36*pm, "Robert Crandal" wrote:
In your code below, when you call "rsData.Close" the first time,
doesn't that close the connection?? * It looks like you are
closing the first connection, then making a new connection with
a new query. *Is that right?

BTW, I will only be extracting data from a couple cells on different
sheets, thats why I am using references line "$A1:A1" or "D12:12".
I was hoping to extract this data as quickly as possible....so I figured
it would make sense (to me) to merely extract the individual data
that I need without closing the connection to the same file with a
series of "Open" and "Close" commands. * I will also be extracting the
same data from a couple hundred Excel 2007 files, so I am trying
to consider all possible ways to speed up my search process.

"norie" wrote in message

...



All you should need to do is close the existing recordset, but don't
close the connection and use code similar to what
you've already posted for the next *query.

szSQL = "SELECT * FROM [Sheet1$A1:A1]"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText

' Extract data from rsData recordset object

rsData.Close

szSQL = "SELECT * FROM [Sheet1$A2:A2]"

rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText

' Extract data from rsData recordset object

rsData.Close

...

Set rsData = Nothing

By the way why does your SQL appear to only be for one cell?

It doesn't make a lot of sense to use ADO to extract such small
amounts of data from a worksheet.- Hide quoted text -

- Show quoted text -

Robert

No, closing the <brecordset</b does not close the connection - well
it shouldn't be.

I'm also still a bit confused why you would want to use ADO to extract
only a few cells from a worksheet.

The easiest, probably the quickest and maybe the least problematic way
to do that would be to open the workbook, get the data, close the
workbook...

Hi Robert,
Sorry I haven't been able to reply due to being hospitalized. I'm out
on a day pass right now and so I wanted to let you know.

Glad to see you're getting help. Hope you're able to resolve something.

I'll look in when I can...

regards,

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi bud.... so sorry to hear about your hospitalization.
Excel programming probably hospitalized you, huh?
Just kidding.... Hope you get better! 8)

"GS" wrote in message
...
Hi Robert,
Sorry I haven't been able to reply due to being hospitalized. I'm out on a
day pass right now and so I wanted to let you know.

Glad to see you're getting help. Hope you're able to resolve something.

I'll look in when I can...

regards,

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc