Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005

    Unanswered: Querying SAP with Access?

    At my last job, we had PeopleSoft with Oracle behind the scenes and they gave us all Oracle access so that we can query the tables directly. I was able to create automated scripts for us that would query the data dynamically, loop through, etc all for us. My current job has SAP, with Oracle behind the scenes, but there doesn't seem to be a way to hook up to the tables directly. I've heard that this is a violation of SAP's licensing terms as only their program should be the one accessing the tables. They keep pushing their Business Warehouse package, which in my opinion, sucks big time. Has anyone had any success in hooking up Access directly to the SAP/Oracle environment to query data out?

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    I don't have first-hand experience with SAP (just SPSS), but there are a couple of ways I use to link tables into MSAccess from different data sources:

    1. See if there is a driver for ODBC since MSAccess works very well with ODBC. I once had to get a Progressive driver (which I had to get from Progressive themselves) to use to link the tables from a Unix-based application which used Oracle (the frontend was done in 4GL). You may want to google for a driver for SAP. I'm not sure if one exists but it's worth a shot. It sounds like SAP doesn't support one (or encourage using one) and it would be a 3rd party driver.

    2. If I can't link the tables into MSAccess using some kind of driver, I see if I can export the data from the program into a usable (importable) format.

    I recall working with the QuickBooks data set (I received the special drivers since QuickBooks is a proprietary database.) It was not a fun task but I eventually got the driver to work and was able to link the tables into MSAccess. QuickBooks now has an "export" feature to export the data (even though certain fields are missing.) They have a "weird" structure so they wanted to avoid other's from seeing/linking to their tables directly due to possible updates which don't follow the coding rules and can mess up the data.

    I didn't like the Oracle environment. I found it to be a lot more complex and time-consuming doing simple tasks versus SQL Server. But you can link MSAccess tables to an Oracle back-end db. I again, used Progress but you may find MS has come out with a driver for Oracle somewhere.

    I see a few Business Warehouse packages which try to design packages to "fit all needs". These rarely work as you typically need to design your warehouse package specific to your environment and the type of data you work with.
    Last edited by pkstormy; 10-02-09 at 01:29.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jun 2004
    Arizona, USA
    Oracle has an ODBC driver. MS has an ODBC driver, and has had one since (at least) 2000 - I was connecting to Oracle using their driver then.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts