Connecting to Oracle 12g with the Instant Client and Excel VBA

Excel References

One rather old application I’ve supported for several years loads data from Excel spreadsheets into a reporting database. These Excel spreadsheets have always been manually updated by several users. However, because the data that the users are entering into these spreadsheets are first being entered into another separate application database, these users have been doing double-entry – a redundant process which can be easily remedied by various means.

Ideally, the solution for this problem would be to extract the data from the application database and load it into the reporting database using an SSIS package. Unfortunately, that would require some redevelopment of the application which loads data into the reporting database, and we (and the customers) have no bandwidth for that. So I came up with a quick workaround that made everyone happy – using a VBA macro to automatically populate the spreadsheets with data when the users open them.

The tricky part here was getting Excel to connect to Oracle with the least amount of work having to be done on the users’ PCs, which are remote from my location.

First of all, since these users don’t require SQL Plus or any development tools for Oracle, the full client software was unnecessary. Also, the Oracle Instant Client could be pushed to the users with Altiris Deployment Solution.

I had the Instant Client software installed on the PCs, then I added the requisite database connection information to the tnsnames.ora file.

Nota bene: In the Instant Client (or at least in our setup, using version 11.2.0.4) the tnsnames file is in
C:\oracle\instantclient_11_2_0_4 rather than in C:\oracle\product\11.2.0.4\client_1\NETWORK\ADMIN as it often would be in the full Oracle client.

The connection in VBA was simple enough, but not immediately obvious – notice that the connection string includes “Microsoft ODBC Driver for Oracle” rather than an Oracle driver; even though this is used, no ODBC connection needs to be set up in the ODBC Data Source Administrator. It is only imperative that the proper entries exist in the tnsnames.ora file, and that the Microsoft ActiveX Data Object Library is installed and referenced in Excel. (Add References by navigating to Tools –> References in the VBA editor in Excel.)

Excel References

In a subroutine, this code was used to connect to the database and pull data.

    Dim SQL_String As String
    Dim dbConnectStr As String
    Dim con As New ADODB.Connection
    Dim recset As New ADODB.Recordset
    Dim strUid As String
    Dim strPwd As String
    Dim strEnv As String
    Dim strDSN As String
    Dim iRow As Integer    
    

    strEnv = "prod"
    strUid = "username"
    strPwd = "password"

    If strEnv = "prod" Then
        strDSN = "(prod database net_service_name* from tnsnames)"
    Else
        strDSN = "(dev database net_service_name* from tnsnames)"
    End If
          
    dbConnectStr = "Driver={Microsoft ODBC for Oracle}; " & _
            "Server=" & strDSN & ";" & _
            "uid=" & strUid & ";pwd=" & strPwd & ";"
       
    con.ConnectionString = dbConnectStr    
    con.Open   
    
    SQL_String = "(insert SQL query here)"
           
    recset.Open SQL_String, con

    iRow = 0 
    Do While Not recset.EOF
         'Have a loop here to go through all the fields
        Sheet1.Range("A" & iRow).Value = recset.Fields("colname") ' colname = Column Name from SQL query
        
        ' &c. ...

        iRow = iRow + 1
        recset.MoveNext
    Loop

    recset.Close
    con.Close

* net_service_name

ADODB Issues in VB.NET with an Oracle Database

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.

%d bloggers like this: