Connecting Oracle to MSACCESS

Hi,

This post summarizes the most important and intresting feature of Oracle which I have tried.

Normally for data entry operators, we would enter the data for internal application in small database like MSACCESS,.... 

but the query feature of Oracle is easy, so if you want to maintain your datasource as MSACCESS and if you want to do any queries using Oracle SQL, this helps you.

Steps

1. Create a MDB file with a username and password

     I think everyone knbows to open access and create an MDB file.

2. Run the following scripts

     \Forms_home\OCA60\SQL\ACCESS  --> *ACCDMBLD.SQL* (Used to create EMP and DEPT table)

       \Forms_home\OCA60\SQL                    --> *DEMODATA.SQL* (Insert demo data into the tables)

3. Create a ODBC data source in the data sources management tab in windows

    1. Open the Data source managment tab

        Control Panel --> Administrative tools ---> Data Sources (ODBC) 

        

     

    2.  Click the Add button,*Create new datasource* screen opens

       

         

    3. Select Microsoft Access Driver(*.mdb) and click Finish.

     

    4. ODBC Microsoft access screen opens, give a name for the data source

        and click the OK button

            

      

    

    5.  Click  the selet button and select the corresponding MDB file

         and click the OK button

          

    6. The datasource would have been created and check the initial screen

      

        

 

4. Open the SQLPLUS.exe under \Forms_HOME\BIN

    

    

5. Connect with the username and password as follows

    username/password@ODBC:DATA_SOURCE_NAME

    

    

      

      

6. Select the data from tables in MSACCESS as we do it in Oracle.

     

    Select * from EMP;