«

»

Nov 27 2009

Change Database values using MS Access (JDE E1 Oracle)

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

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

    For JDE EnterpriseOne Standalone 9.1 use the following username/password (case sensitive)
    JDEDATA910 / JDEData910

    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.

Published under the license.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
(Visited 361 times, 1 visits today)

About the author

Deepesh M Divakaran

Deepesh MD is more of a Gizmo Geek, and a techie.
A very experienced Technical Consultant on Oracle JD Edwards EnterpriseOne, and OneWorld. As a consultant he has worked for wide range of industries, from Manufacturing, Media, Oil & gas etc. and has worked on re-engineering modules, and areas of customization.
With a touch of class and understanding towards what ever he does, he is often regarded by his colleagues as the one stop answer for all Technical/Software related queries.

4 comments

Skip to comment form

  1. Rijo

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

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  2. Ahmad

    hi,

    my problem is on step 5,

    i only find Oracle for E1Local,

    i try to use it but no username is working (JDEDATA910) as i’m using 9.1 standalone.

    can you help please

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    1. Deepesh M Divakaran

      Hi Ahmad,

      Please revisit the point 8. I have updated the credentials

      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  3. Ahmad

    i even try to use sys

    but it gives me the error that sys should be as sysdba and i don’t know what i write in the username to achieve that

    help please

    Thanks

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)

Leave a Reply