Change Database values using MS Access (JDE E1 Oracle)

For people who have problems with SQL, there’s an easier way of changing backend database values. How about using MS Access where you can directly type in the values, and it gets updated or you can Insert, Update Delete & you can export, import…. and all.

So here it goes for setting up MS Access with Oracle Database Backend for JD Edwards EnterpriseOne. (I’ve put the screen shots for my EnterpriseOne 9.0 Standalone Client.)

  1. You will need to have Oracle OBDC drivers installed for using MS Access or any other similar software (Even MS Excel etc) to use.
    • The download link for the same – http://www.oracle.com/technology/software/tech/windows/odbc/index.html
    • Download ODBC Driver version 10.2.0.2.0 , and Extract it to C:\ of your hard disk.
    • Finally you will have the Drivers at “C:\ORA10202”
    • Now let’s install these drivers using the Oracle Universal Installer. You can get to this using the Programs Menu as shown in screen shot below.
      OracleUniversalInstaller
    • Once you start the Universal Installer, Click NEXT.
    • Select the Products.xml from the folder you have extracted – C:\ORA10202\Disk1\stage\products.xml, and then Click NEXT.
      InstallerSelected
    • Select Typical, and Continue clicking NEXT.
    • Select the Oracle Home where you have the Oracle Installed.
      In the case of Standalone E9, you will have the Oracle Home installed in C:\oracle\E1Local,. which might default automatically.
    • Once done, we Click INSTALL, and Finish it off.
  2. You now have your Oracle ODBC Drivers Installed, its wise to Restart the System.
  3. We need to create the ODBC connection for the connectivity. For this, navigate to “Control Panel -> Administrative Tools -> Data Sources (ODBC)”
  4. In it, select the SYSTEM DSN tab, and Click Add.
  5. Select the Oracle 10.2.0 driver, and click Finish.
    SystemDSN
  6. Here Enter a Data Source Name, and description.
  7. Now select the TNS Service Name from the drop down list, which will have all the Oracle details which are the ones populated in your tnsnames.ora file.
    ODBC Driver
  8. For JDE EnterpriseOne Standalone 9.0, use the following User name password
    JDEDATA900 / JDEDATA900
    You may use the Database Username/password which you may be using for your Database.
  9. Test the connection. If not successful, try changing the parameters and do it again.
  10. Save the DSN connection.
  11. Open MS Access (any version), and Create a Blank Database.
  12. Right Click inside the Database file, and Select LINK TABLES.
  13. In the File Type drop-down, select ODBC Database.
  14. Click the Machine Data Source, where you can see all your ODBC data sources.
    Select the one you just created in steps 6-10, and click OK
  15. Enter the password for the UserID, and click OK
  16. This will now list all your tables from the Datasource.
  17. Select the Table/s you want to view/update/insert/delete, and click OK (you could tick Save Password to avoid the hassle of entering it everytime you open it.)
  18. Yes you have Done it. Now you can open the table, to View it, Update/Insert/Delete/ or what ever you want.
    AccessWithOracle

NOTE: Access (sometimes) does not give you warnings or Messages during Update/Delete/Insert, so please be careful with Data.

4 thoughts on “Change Database values using MS Access (JDE E1 Oracle)

  1. Rijo Reply

    That is a good one. But Access loads tables slowly. Doing a Find too is very slow.

  2. Rama M Reply

    If you know, can you write an article to interface TOAD with Oracle and what are the advantage compared to MS Access.
    Thanks

    • Deepesh M Divakaran Post authorReply

      Hi Rama,
      TOAD is basically a SQL and Database Administration tool for Oracle (or DB2, SQL Server). Connection to Oracle will be using the credentials, and role. TOAD requires separate license, while MS Access comes along with the MS Office suite, which most people have installed on, thus making MS Access more available. Toad is more robust and secure compared to Access though.

Leave a Reply