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

Oracle Database logo

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

Some Good Resources on Database Performance Optimization and Tuning

database symbol

Database performance optimization is a skill that is very important for both the database administrator and the database developer. I decided to put together a few links here that may be useful in disseminating information about database tuning and query optimization on various database systems, both relational and non-relational. I’ll be updating this from time to time as I come across new resources.

Microsoft SQL Server:

Optimizing Databases
SQL Database Performance Tuning for Developers

Oracle Database:

Database SQL Tuning Guide

PostgreSQL:

Tuning Your PostgreSQL Server

MySQL:

How to optimize a MySQL database

MongoDB:

Optimization Strategies for MongoDB

database symbol