ADO in Excel
 

I have a long Excel VBA sub which creates several ADO recordsets from
another open workbook in succession.
Upon completion, the sub closes the source workbook. Despite that, the VBE
still shows the source workbook open!
If I run the subroutine several times (and then close the source workbook
programmatically or manually), several instances of one and the same
workbook appear in the VBE. Eventually, the system runs out of resources and
the computer hangs.

I realize that I am failing to destroy one of the ADO objects somewhere but
I do kill the ADO connection, set the recordsets to Nothing etc.

What can I be possibly be doing wrong??

Thanks!

FK

ADO in Excel
 

Hi Francine

Set .........= Nothing

Do you use this at the end of your macro???


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Francine Kubaka" wrote in message news:BW9db.6471$o21.4784@edtnps84...
I have a long Excel VBA sub which creates several ADO recordsets from
another open workbook in succession.
Upon completion, the sub closes the source workbook. Despite that, the VBE
still shows the source workbook open!
If I run the subroutine several times (and then close the source workbook
programmatically or manually), several instances of one and the same
workbook appear in the VBE. Eventually, the system runs out of resources and
the computer hangs.

I realize that I am failing to destroy one of the ADO objects somewhere but
I do kill the ADO connection, set the recordsets to Nothing etc.

What can I be possibly be doing wrong??

Thanks!

FK

ADO in Excel
 

Yes!
That's what I do after each retrieval:

rs.Close 'Closing Recordset
cn.Close 'Closing ADODB Connection
Set rs = Nothing 'Destroy Recordset Object
Set cn = Nothing 'Destroy Connection Object

Do you know which objects are re-useable in the ADODB collections?

For example, I create a connection to Workbook A.

strConn = "......"
SQL = "SELECT APPLES....."
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open strConn
rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

Now I need to run a different query on the same Workbook:

Right now, I just do this:

SQL = "SELECT ORANGES....." 'I am creating a new SQL statement
rs.Close 'Close the old Recordset
rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText 'Open a
new recordset

Then, I need to run a query on a different Workbook:

strConn = "......" 'Create New Connection String
cn.Close
rs.Close
Set rs= Nothing
Set cn = Nothing
SQL = "SELECT PEARS....."
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open strConn
rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

That's what I do. And this seems to create persistent connections......

Is there something wrong? Am I creating duplicates somewhere? Am I failing
to destroy some objects?

Thanks!

FK








"Ron de Bruin" wrote in message
...
Hi Francine

Set .........= Nothing

Do you use this at the end of your macro???


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Francine Kubaka" wrote in message
news:BW9db.6471$o21.4784@edtnps84...
I have a long Excel VBA sub which creates several ADO recordsets from
another open workbook in succession.
Upon completion, the sub closes the source workbook. Despite that, the
VBE
still shows the source workbook open!
If I run the subroutine several times (and then close the source
workbook
programmatically or manually), several instances of one and the same
workbook appear in the VBE. Eventually, the system runs out of resources
and
the computer hangs.

I realize that I am failing to destroy one of the ADO objects somewhere
but
I do kill the ADO connection, set the recordsets to Nothing etc.

What can I be possibly be doing wrong??

Thanks!

FK

ADO in Excel
 

Look here for example codes
http://www.erlandsendata.no/english/...php?t=envbadac

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Francine Kubaka" wrote in message news:cOidb.11868$o21.615@edtnps84...
Yes!
That's what I do after each retrieval:

rs.Close 'Closing Recordset
cn.Close 'Closing ADODB Connection
Set rs = Nothing 'Destroy Recordset Object
Set cn = Nothing 'Destroy Connection Object

Do you know which objects are re-useable in the ADODB collections?

For example, I create a connection to Workbook A.

strConn = "......"
SQL = "SELECT APPLES....."
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open strConn
rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

Now I need to run a different query on the same Workbook:

Right now, I just do this:

SQL = "SELECT ORANGES....." 'I am creating a new SQL statement
rs.Close 'Close the old Recordset
rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText 'Open a
new recordset

Then, I need to run a query on a different Workbook:

strConn = "......" 'Create New Connection String
cn.Close
rs.Close
Set rs= Nothing
Set cn = Nothing
SQL = "SELECT PEARS....."
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open strConn
rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

That's what I do. And this seems to create persistent connections......

Is there something wrong? Am I creating duplicates somewhere? Am I failing
to destroy some objects?

Thanks!

FK








"Ron de Bruin" wrote in message
...
Hi Francine

Set .........= Nothing

Do you use this at the end of your macro???


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Francine Kubaka" wrote in message
news:BW9db.6471$o21.4784@edtnps84...
I have a long Excel VBA sub which creates several ADO recordsets from
another open workbook in succession.
Upon completion, the sub closes the source workbook. Despite that, the
VBE
still shows the source workbook open!
If I run the subroutine several times (and then close the source
workbook
programmatically or manually), several instances of one and the same
workbook appear in the VBE. Eventually, the system runs out of resources
and
the computer hangs.

I realize that I am failing to destroy one of the ADO objects somewhere
but
I do kill the ADO connection, set the recordsets to Nothing etc.

What can I be possibly be doing wrong??

Thanks!

FK

ADO in Excel
 

Francine,

As my object variables are dimmed within the procedure, there is no need
to set them to nothing, as vba does this automatically when the
procedure exits.

Closing the connection should be sufficent.
Closing the RS after closing the CN leads to errors, you may close the
RS before closing the CN

You didnt mention your connection string, but that's isnt UNimportant :)
Please note that I'm using the OLEDB connect string, not ODBC

Tested with ado 2.1 and 2.7, and connect string works with excel2000+

Following is an adapted copy of a routine i use, where the data is
stored in named ranges.


Sub XLasRS()
'Needs reference to Microsoft ActiveX Data Objects
'Preferred version 2.7

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sC As String
Dim sQ As String
Dim i As Integer
Dim books, book

books = Array("c:\adodata1.xls", "c:\adodata2.xls")

For Each book In books
sC = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & book & _
";Extended Properties=Excel 8.0;"

'Assumes Reading from Named Ranges
sQ = " SELECT a.acctnr, b.period, a.acctname," & _
" a.linenr, l.linename, b.amount" & _
" FROM accounts a, balances b , lines l" & _
" WHERE a.linenr = l.linenr AND b.acctnr = a.acctnr"

'Connect
Set cn = New ADODB.Connection
cn.Open sC
'Read
Set rs = New ADODB.Recordset
rs.Open sQ, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
' optional ways of retrieving a recordset
' Set rs = cn.Execute("[A1:Z1000]") ' first worksheet
' Set rs = cn.Execute("[DefinedRangeName]") ' any worksheet
'Write
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets(Mid(book, 4))
.Cells.Clear
For i = 1 To rs.Fields.Count
.Cells(1, i).Value = rs.Fields(i - 1).Name
Next
.Cells(2, 1).CopyFromRecordset rs
End With
cn.Close
Application.ScreenUpdating = True
Next

End Sub






keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Francine Kubaka" wrote:

Yes!
That's what I do after each retrieval:

rs.Close 'Closing Recordset
cn.Close 'Closing ADODB Connection
Set rs = Nothing 'Destroy Recordset Object
Set cn = Nothing 'Destroy Connection Object

ADO in Excel
 

Check out this bug to see if it pertains to your problem.

http://support.microsoft.com/default...b;en-us;319998

Francine Kubaka wrote:
I have a long Excel VBA sub which creates several ADO recordsets from
another open workbook in succession.
Upon completion, the sub closes the source workbook. Despite that, the VBE
still shows the source workbook open!
If I run the subroutine several times (and then close the source workbook
programmatically or manually), several instances of one and the same
workbook appear in the VBE. Eventually, the system runs out of resources and
the computer hangs.

I realize that I am failing to destroy one of the ADO objects somewhere but
I do kill the ADO connection, set the recordsets to Nothing etc.

What can I be possibly be doing wrong??

Thanks!

FK

ADO in Excel
 

Thanks, Bill!! You nailed it!!!

Many thanks to everybody else for helpful comments and suggestions!!!

FK



"Bill Bell" wrote in message
news:3mEdb.453851$cF.145865@rwcrnsc53...
Check out this bug to see if it pertains to your problem.

http://support.microsoft.com/default...b;en-us;319998

Francine Kubaka wrote:
I have a long Excel VBA sub which creates several ADO recordsets from
another open workbook in succession.
Upon completion, the sub closes the source workbook. Despite that, the
VBE
still shows the source workbook open!
If I run the subroutine several times (and then close the source
workbook
programmatically or manually), several instances of one and the same
workbook appear in the VBE. Eventually, the system runs out of resources
and
the computer hangs.

I realize that I am failing to destroy one of the ADO objects somewhere
but
I do kill the ADO connection, set the recordsets to Nothing etc.

What can I be possibly be doing wrong??

Thanks!

FK