07 May 2009

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;

    
    
    
     
 

     

     

4 comments:

  1. After step which database we have to connect is not given.

    ReplyDelete
    Replies
    1. In this articale we are creating a new connection to a MDB and naming the connection as ACCESS_DB.

      While connecting to the database , we have to give it like

      username/password@ODBC:DATA_SOURCE_NAME

      where DATA_SOURCE_NAME is the ODBC connection which you have created.


      So it would be like username/password@ODBC:ACCESS_DB

      I hope this helps

      Delete
  2. HOW TO CREATE USER NAME & PASSWORD.

    ReplyDelete
    Replies
    1. We have to create a username and password within MSACCESS for the MDB file to be created using the users and group wizard.

      Delete