0 Replies Latest reply on Jun 25, 2014 11:54 PM by Siebpaul-Oracle

    Migrating away from OO4O for VBA Applications?

    Siebpaul-Oracle

      I have used OO4O off and on since 1993, but this 32-bit component will not be supported on 64 bit Windows.

      The two options for migration referenced in various posting are Oracle products and so I'm looking for advice.

       

      Oracle Provider for OLE DB

      There is clear documentation on using OLE DB with VBA applications for Microsoft Office. OLE DB uses the Component Object Model (COM), which has been around for a very long time.

       

      Oracle Data Provider for .NET, or ODP.NET

      However, there is no clear documentation for using ODP.NET with VBA in MS Office applications such as Excel or Word. I don' know if it even can be used in a VBA application. I believe you have to migrate VBA applications to Visual Basic in either Visual Studio for .NET or Visual Studio Express (which is free!!!).

       

      Recommendations for Migration?

      Any recommendation for a migration path needs a little bit of information about what I'm trying to accomplish. I currently use Excel to convert fixed format text files, with header/footers/subtotals that are ignored, and load then data to an Oracle database. I save the both the text and XLSX files for auditing and verification purposes, which the end users like. I use a "control" workbook to support the data driven application which supports the addition of new text file formats by adding a new worksheet that stores the necessary data for processing each type of text file.   I looking the following capabilities:

      1. Support for Unicode is required, which I believe both of the above products have.
      2. Really nice to have: the ability to insert multiple records in one trip from the client to the database server.
      3. Nice to have: the ability to maintain a single file (Excel VBA enabled workbook) instead of a Excel spreadsheet file and a VB.NET application file.
      4. Performance is strictly a nice to have, within limits. I don't care if it takes 20 minutes instead of 5 minutes to load 100,000 records, but it can't be too slow.

       

      Thanks,

      Paul