AdoDB connection vs. OracleInProc Server and RecordSet

Question

I was wondering if someone could explain the difference between an ADODB.Connection and creating an Oracle session through "OracleInProcServer.XOraSession". Is one better than the other in certain cases?

From what I have read in ADODB cnn.Open "Pubs", "MyUserName", "MyPassword" and OraSession you put in openDatabase(database_name, database_version, database_displayname, database_size).

How do the connections differ?

Also with the ADODB connection you can open a recordset and save with a rs.Open and a rs.GetRows. Is there a way to save the values of the query to then display in the Excel with the OraSession connection?

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
verses
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("", "", 0&)

Thanks! Maggie

Answer 1

Overall, both are tools to connect databases to programming environments. According to online docs, OracleInProcServer is an OLE Automation Object. Hence, after install it comes available as a VBA reference. ADODB (part of ADO) is a general database API and the other is a specific Oracle API. See further points below. Do note: I am not familiar with OracleInProcServer but below gathers from above source.

ActiveX Data Objects (ADO)

• Generializable API: capable of swapping out connection strings and maintaining same consistent codebase across both OLEDB providers and ODBC drivers such as Oracle, MySQL, SQL Server, PostgresSQL, more; see example of MS Access and SQLite interchange;
• Full-featured database API for working between application layer and backend database including calling stored procedures, action queries, working with recordsets, etc.;
• COM or .DLL object not limited to VBA but any language that can make a COM interface (i.e,. Java, PHP, Python, R) as initialized with CreateObject("ADODB.Connection");
• Since it integrates into a programming language, it can connect to the Excel object library (i.e., workbooks, worksheets, ranges), even Access/Word/Outlook/PowerPoint apps;
• Microsoft is the vendor and maintainer; API is available for free download. However, usually no additional installation is required as ADO ships with MS Office software or Windows OS;
• Widely known in the industry with time-enduring problem and solutions available in many tutorials, books, and online searches.

OracleInProcServer

• Specific API: Customized and tailored solution specifically for Oracle (proprietary software) and no other database, so possibly is keen to Oracle-specific methods;
• Appears to be a capable database API working with database, dynaset, and field objects;
• COM or .DLL object not limited to VBA but any language that can make a COM interface as seen with initialization: CreateObject("OracleInProcServer.XOraSession");

• Docs show dynasets can work with Excel workbooks, looping across records and fields (very similar to ADO) to display data:

  'Display Data 
  For Rownum = 2 To myDynaset.RecordCount + 1 
    For Colnum = 0 To fldcount - 1 
      ActiveSheet.Cells(Rownum, Colnum + 1) = flds(Colnum).Value 
    Next 
    myDynaset.MoveNext 
  Next 
  

• Oracle is the vendor and maintainer and installation (possibly paid) is required; maybe it is installed with Oracle client and other components; plus users may be able to call someone for help with this product (check service agreement/terms);

• Likely a small user base that may not have sophisticated issues resolved or solutions widely known or available.

Which one is better? This ultimately comes down to your preference or project needs. For simple tasks of connecting to database, calling queries, and outputting to Excel any above will do.