Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006
    Posts
    2

    Unanswered: Connecting MS ACCESS from ORACLE

    This question is worth 125 Points


    Hi Experts:

    Scenerio:
    __________________________________________________ ___________________________________________
    I am using Oracle 10g 10.2.0.1.0 on Windows XP:

    I am trying to access a "Microsoft ACCESS" database using Oracle.


    The MS ACCESS data base name is "ACCESS_1" and it has only one table "Employees".
    The MS ACCESS and Oracle are both located on the same machine.
    I do not know how to access the MS ACCESS database, hence I do not know the User Id and Password for the MS ACCESS database "ACCESS_1".
    I created this table "Employees" on "ACCESS_1" database by goint to "START|ALL PROGRAMS| MICROSOFT ACCESS|Create a new database using" . I created Employees table there.

    __________________________________________________ ____________________________________________

    I performed as follows to connect the MS ACCESS from ORACLE:

    The Location of Employees table:
    ================================

    1. The Employees table in access_1 (Microsot Access) database is as follows:

    ID enmae enumber deptno manger Salary
    1 Muhammad 100 10 Riyaz 10000
    2 Fareed 101 15 Rashida 350000
    3 Farhaad 102 20 Riyaz 350000
    4 Huma 103 25 Rukhsana 230000
    5 Rashida 104 30 Riyaz 275000





    The INITACCESS.ORA File, itís creation and itís Location:
    ================================================== =======

    2. The C:\Oracle\Product\Orcl\hs\Admin\initaccess.ora file is copied from C:\Oracle\Product\Orcl\hs\Admin\inithsodbc.ora and is edited as follows:


    # HS_FDS_CONNECT_INFO = <odbc data_source_name>
    # HS_FDS_TRACE_LEVEL = <trace_level>
    HS_FDS_CONNECT_INFO = access1
    HS_FDS_TRACE_LEVEL = OFF



    #
    # Environment variables required for the non-Oracle system
    #
    #set <envvar>=<value>




    The updated LISTENER.ORA, itís location and contents (Part of the contents):
    ==================================================

    3. The listener.ora file is located at C:\Oracle\Product\Orcl\Network\Admin and is updated as follows:




    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = C:\oracle\product\orcl)
    (PROGRAM = extproc)
    )

    (SID_DESC =
    (SID_NAME = access1)
    (ORACLE_HOME = C:\oracle\product\orcl)
    (PROGRAM = hsodbc)
    )
    )
    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = RIYAZDESKTOP)(PORT = 1521))
    )
    )



    The updated TNSNAMES.ORA File, itís location and contents (Part of itís contents)
    ================================================== ===

    4. The tnsnames.ora file is loacted at C:\Oracle\Product\Orcl\Admin and is updated as follows (added the access1 portion only)

    access1 =
    (DESCRIPTION =
    (Address = (PROTOCOL = TCP) (HOST = RIYAZDESKTOP) (PORT = 1521))
    (CONNECT_DATA = (SID = access1))
    (HS=OK)
    )



    The creation of database link ďACCESS1Ē as follows:

    5. The database link is created as follows:
    ==============================

    SQL> create database link access1 using 'access1';

    Database link created.




    The failed portion when tried to access the database:
    ==================================


    WHEN TRIED TO ACCESS THE access_1 database using the newly created database link I got the following:

    SQL> select table_name from all_tables@access1;
    select table_name from all_tables@access1
    *
    ERROR at line 1:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [Generic Connectivity Using ODBC][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL State: IM002; SQL Code: 0)
    ORA-02063: preceding 2 lines from ACCESS1

    ************************************************** ******
    I donot know what am I doing wrong and what to do. Please reply. THANKS
    ************************************************** ******

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Allright, but did you create a system DSN named access1 that points towards your access MDB file ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •