ADO question

[Robert Crandal]

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!

 

[GS]

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.

 

[Robert Crandal]

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!

 

[norie]

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.

 

[Robert Crandal]

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.

 

[AB]

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.

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.

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


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 -

 

[norie]

Robert

No, closing the <b>recordset</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...

 

[Robert Crandal]

I have several hundred workbooks. I want to extract data from
all workbooks and ADO seems to be faster than opening and
closing each workbook. BTW, each workbook that I'm getting
data from contains over 300K bytes. So, each file is big and
contains macros. Wouldnt it be faster just to use ADO in
this case?

 

[AB]

Robert,

I still stand by my earlier post - it seems that you're just missing a
standalone connection object variable.
Do you have that?

 

[norie]

AB

What do you mean exactly?

As far as I can see there are no variables declared in the OP's code
and only a few given values.

szConnect could be anything, the prefix sz suggests a string the
Connect could mean it's a connection.

I suppose it could be a connection string of we combine the 2.

 

[AB]

Exactly - i also believe it's a connection string.
As per my earlier post/code, I believe Robert should:
Dim cn as ADODB.Connection
Set cn= new ADODB.Connection
cn.open (put all the connection bits in here)

and then use the cn variable/object for oppening the recordset on it,
like:
rst.open "mysql", cn
' do the thing
rst.close

at this point in time rst will be closed but the standalone cn
variable (connection object) will still be open ready to have other
recordsets opened up using it, i.e., go again laik:
rst2.open "mysql2", cn

And so forth - i.e., the cn varible would remain open as long as the
code needs it and then various recordsets can be opened/closed using
this object without any impact on the connection's state.

 

[Robert Crandal]

Here is my code so you can see what I'm doing:
================================================================
Dim szConnect As String
Dim rsData As ADODB.Recordset
Dim szSQL As String
Dim szFile As String

szConnect = szProvider & szDataSrc & szFile & szProps

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

szFile = ThisWorkbook.Path & "\" & "data.xlsx"
szConnect = szProvider & szDataSrc & szFile & szProps
szSQL = "SELECT * FROM [Sheet1$A1:A1]"

' Create the Recorset object and run the query.
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

' Do stuff with recordset here

rsData.Close
Set rsData = Nothing
===============================================================
As a side note, the variables "szProvider", "szDataSrc" and "szProps" are
defined in a separate module. They basically consist of the connection
string.

In response to your question, I was not using the "connection object
variable" that
you mention earlier, mostly because I was unaware. I basically copied and
pasted
the above code from a sample ADO file.

So, should I create a connection object, then use the "open" and "close"
commands
to begin new queries?

Thank you!



Robert,

I still stand by my earlier post - it seems that you're just missing a
standalone connection object variable.
Do you have that?

 

[MerseyBeat]

I have several hundred workbooks. I want to extract data from
all workbooks and ADO seems to be faster than opening and
closing each workbook. BTW, each workbook that I'm getting
data from contains over 300K bytes. So, each file is big and
contains macros.

A 300KB file is miniscule. Besides, if you don't need the macros to run
when you are trying to extract the data, you can make that happen very
easily.

 

[AB]

Try this:

Dim cn as ADODB.Connection
Dim rst as ADODB.Recordset
Set cn= new ADODB.Connection
cn.open '<==(put all the connection bits in here)
set rst= new ADODB.Recordset
rst.open sql, cn
'do your stuff with rst
rst.close
set rst=nothing
cn.close'<==You'd be closing the connection only at this point here.
set cn=nothing

Then, when your'e closing your rst - you'll still have your cn object
that you can reuse ro any other recordsets.

 

[GS]

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,

 

[Robert Crandal]

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