Using Excel VBA to Copy an ADODB Recordset to Multiple Worksheets

Microsoft VBA logo

In adding functionality to an ancient Excel VBA reporting app for an Oracle database, I decided to set up a temporary worksheet to use as a place to store data before copying it to multiple other worksheets rather than copying directly from the recordset multiple times.

Basically, this VBA procedure takes an ADODB recordset and a Boolean value as parameters. The recordset is the data source, and the Boolean indicates whether or not the data will be copied to a particular worksheet. The temporary worksheet is cleared before the recordset is copied into it, and is cleared again after the data is copied to the other worksheet(s).

Public Sub CopyToSheets(ByRef rs As ADODB.Recordset, ByVal bCopyToSecondSht As Boolean)
    Dim MainSht As Worksheet, TempSht As Worksheet, Rng As Range, c As Range, lastRow As Long, intNextRow As Integer

    Set MainSht = ActiveWorkbook.Worksheets(cMainSht)
    Set TempSht = ActiveWorkbook.Worksheets(cTempData)

    TempSht.Cells.Clear

    rs.Open
    TempSht.Cells(1, 1).CopyFromRecordset rs
    rs.Close

    lastRow = TempSht.Range("A" & Rows.count).End(xlUp).Row
    Set Rng = TempSht.Range("A1:A" & lastRow)
    For Each c In Rng
        intNextRow = NextRowNumber(MainSht)
        c.EntireRow.Copy MainSht.Cells(intNextRow, 1)
        If bCopyToSecondSht Then
            Dim SecondSht As Worksheet
            Set SecondSht = ActiveWorkbook.Worksheets(cSecondSht)
            intNextRow = NextRowNumber(SecondSht)
            c.EntireRow.Copy SecondSht.Cells(intNextRow, 1)
        End If
    Next c
    TempSht.Cells.Clear
End Sub

ADODB Issues in VB.NET with an Oracle Database

Oracle Database logo

During my recent VB6 to VB.NET Forms upgrade adventure, I had to wade through quite a bit of code that, while still functional, should be upgraded further when time permits.  Most of this code is used for data access, and being that it was written back in the VB6 / Classic ASP days, Active Data Objects (ADO) was used rather than ADO.NET, which is the newer .NET equivalent of ADO.
A common feature of ADO which was widely used in this project was the Recordset.  An ADO Recordset Object is defined by Microsoft as an object that “represents the entire set of records from a base table or the results of an executed command.  At any time, the Recordset object refers to only a single record within the set as the current record.” (Link)

One of the problems in using the recordsets object with VB.NET and Oracle is that if the recordset is not closed and garbage collected once the program is done with it, an Oracle error reporting too many open cursors (ORA-01000) is often thrown.  Many Web sites say that one possible fix to this error is to increase the number of allowed open cursors, but this solution only hides bad code.  Instead, you must make certain that the object is closed, set to Nothing, and garbage collected.

To resolve this problem, I created a module that contains methods to destroy serveral types of objects: ADODB Connections, ADODB Recordsets, Excel applications, and Scripting FileSystemObjects.

Module modDestroyObjects

Public Sub DestroyConnection(ByRef cn As ADODB.Connection)
If cn IsNot Nothing Then
If Not cn.State = 0 Then
cn.Close()
End If
cn = Nothing
CollectGarbage("ADODB.Connection")
End If
End Sub

Public Sub DestroyRecordset(ByRef rs As ADODB.Recordset)
If rs IsNot Nothing Then
If Not rs.State = 0 Then
rs.Close()
End If
rs = Nothing
CollectGarbage("ADODB.Recordset")
End If
End Sub

Public Sub DestroyExcelApp(ByRef xlApp As Microsoft.Office.Interop.Excel.Application)
If Not (xlApp Is Nothing) Then
For i As Integer = xlApp.Workbooks.Count To 1 Step -1
xlApp.Workbooks(i).Close(False)
Next i
xlApp.Quit()
xlApp = Nothing
CollectGarbage("Excel.Application")
End If
End Sub

Public Sub DestroyFileSystemObject(ByRef fso As Scripting.FileSystemObject, ByVal str As String)
If Not (fso Is Nothing) Then
If fso.FileExists(str) Then
fso.DeleteFile(str)
End If
CollectGarbage("FileSystemObject")
End If
End Sub

Private Sub CollectGarbage(ByVal strName As String)
GC.Collect()

'' Uncomment below to see a Message Box each time Garbage Collection is manually invoked.
'MsgBox("Object " & strName & " has been collected.", MsgBoxStyle.OkOnly)
End Sub

End Module

By calling the appropriate method, the object is removed from memory immediately, rather than waiting for automatic garbage collection to take place.  While this does increase system overhead somewhat, I noticed no decrease in performance.  On an older / slower computer, this increase in overhead could be noticeable.  However, this ensures that — at least until I can rewrite the code using ADO.NET — the program can run many queries without throwing the dreaded ORA-01000 error.