Monday, February 8, 2010

How to Connect with Access Database from Forms Developer?

Environment:
WIndows XP
Oracle 10g XE
MS Access 2003
Forms 10g Rel-1


STEP I:
Create a simple access database. In this example, it is "C:\orafaq\Orafaq.mdb". This database contains a single table, called "emp" with the columns "ename" and "empno".

STEP II:
In the ODBC Data source administrator, you need to add a System DSN pointing to this database. So you select "MS Access Driver" and add the name "OrafAccess" in the data source name. Make sure you've added the correct .mdb file (orafaq.mdb)

STEP III:
Now we set up the HS init file: it's name is fixed (init.ora). It contains only two lines of code:

$ORACLE_HOME/hs/admin/initOrafAccess.ora:

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = OraFaccess
HS_FDS_TRACE_LEVEL = 0



STEP IV:
Next, I changed the listener.ora of my XE database:
$ORACLE_HOME/network/admin/listener.ora:
added in SID_LIST:

    (SID_DESC =
         (SID_NAME = OrafAccess)
         (ORACLE_HOME = )
         (program = hsodbc)
        )


STEP V:
$ORACLE_HOME/network/admin/tnsnames.ora:
The setup of the HSODBC is done, and we need to make it accessible for our Oracle users. Like any other database there has to be an entry in the TNSNAMES.ORA file:

ORAFACCESS =
   (description =
    (address = (protocol=tcp)(host=localhost)(port=1521))
     (connect_data = (sid=orafaccess))
     (hs=ok)
   )


STEP VI:
bounce the listener. You can do this befor step V, but I just did it in this order.

STEP VII:
SQL*Plus:


SQL: create database link orafaccess using 'ORAFACCESS';

Database link created.

SQL: desc emp@orafaccess
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 empno                                              NUMBER(10)
 ename                                              VARCHAR2(50)

SQL: create synonym access_emp for emp@orafaccess;

Synonym created.

SQL: desc access_emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 empno                                              NUMBER(10)
 ename                                              VARCHAR2(50)

SQL:


STEP VIII:
In Forms we make a basic form:
- data block wizard
- table Access_emp
- key mode UPDATEABLE (rowid won't work)
- empno = primary key
- layout wizard
- tabular design

I added double quotes (") around the column names in the property palette. I also added the following triggers:
ON-COMMIT:

BEGIN
   COMMIT_FORM;
END;

ON-LOCK:

BEGIN
   NULL;
END;

ON-DELETE:

BEGIN
   DELETE_RECORD;
END;

--- Step Complete ---

Ref For More : Click Here or  Click Here 


No comments:

Post a Comment