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.
     
   • 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.
     
   • 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.
   
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.
   
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.
    

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