This discussion is archived
2 Replies Latest reply: Jan 18, 2013 12:17 AM by 985673 RSS

URGENT: Synchronizing Oracle and Microsoft Access

976901 Newbie
Currently Being Moderated
Hi everyone,

I really need help on this.

I was asked to perform synchronization between Oracle and Access. Lets say if I added or updated any data in Oracle, at the same time the Access database will update the same changes too. The sync should be made in real-time, no intervals. Before this, I thought that database links and database synchronize are the same thing, but I was wrong. I have tried integrating Oracle and Access using database link, but my idea was rejected by my colleague, saying that sync and link are different.

Could anyone please suggest me anything to synchronize these databases? Does synchronizing involve any triggers or stored procedure?

Oh please I've been looking for the answer for a week :'(
  • 1. Re: URGENT: Synchronizing Oracle and Microsoft Access
    kgronau Guru
    Currently Being Moderated
    Your requirement is: "if I added or updated any data in Oracle, at the same time the Access database will update the same changes too"

    A possible solution is the database gateway for ODBC. With a suitable ODBC driver it allows you to connect from an Oracle database to the MS Access database using a database link based on the Database Gateway for ODBC (=DG4ODBC).
    When you have a working database link you can then define a trigger that starts an autonomous transaction (autonomous transaction is mandatory as DG4ODBC is not able to participate in distributed transactions) that replicates the content changes from the Oracle database to the MS Access database. It is happening almost in real time as the trigger fires as soon as the record in the Oracle table is manipulated and the work committed. Then itoens the connection to the MS Access database and pushes the change also to MS Access.


    Maybe just as a thought ... I don't know why you need to store the content also in MS Access tables. Instead of storing the content in the table, why not just linking the Oracle tables into MS Access and then using the ODBC connection in Ms Access and everytime someone opens the table in MS Access it connects through the ODBC driver to the Oracle database and fetches the content directly from there. It is surely another option when it is not explicit needed to store the content also in the MS Access file.


    - Klaus

    Edited by: kgronau on Nov 30, 2012 6:50 AM


    Here an example for a trigger that fires when updating an Oracle table and starts an autonomous transaction:


    MS Access with a sample table:
    ===================
    CREATE TABLE emp_sal
    (
    EMPNO NUMERIC(4),
    SAL NUMERIC(7,2));


    Oracle table:
    ========
    DROP TABLE emp_sal;

    CREATE TABLE emp_sal
    (
    EMPNO NUMBER(4),
    SAL NUMBER(7,2));

    and now the trigger:
    CREATE OR REPLACE TRIGGER dg4odbc_repl AFTER INSERT ON emp_sal
    FOR EACH ROW
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    INSERT INTO "emp_sal"@DG4ODBC_EMGTW_1123_DB
    VALUES( :new.empno, :new.sal);
    COMMIT;
    END;
    /

    -- Note the commit, otherwise risk ORA-6519

    When now inserting a record:
    insert into emp_sal values (1234,'1200.89');

    I can see at the MS Access side the record:
    select * from "emp_sal"@DG4ODBC_EMGTW_1123_DB;
    EMPNO SAL
    ----- -------
    1234 1200.89



    ATTENTION:
    in the trigger we have a commit as we have started an autonomous transaction so a rollback won't work and only "deletes" the record in the Oracle database
    insert into emp_sal values (1384,'1200.89');

    rollback;
    The Oracle table still contains just one record:
    select * from emp_sal;
    EMPNO SAL
    ----- -------
    1234 1200.89

    But the MS Access file two:
    select * from "emp_sal"@DG4ODBC_EMGTW_1123_DB;
    EMPNO SAL
    ----- -------
    1234 1200.89
    1384 1200.89
  • 2. Re: URGENT: Synchronizing Oracle and Microsoft Access
    985673 Newbie
    Currently Being Moderated
    Hello,

    I hope that you have already solved your problem but if not let me suggest you a very good tool that is very easy to use and that could save you lot of time.
    Synchronization can become a very tricky procedure especially when you are trying to sychronize two different platforms like your Access with Oracle database and most of synchronizing software are very expensive and not powerful enough to be customized. There is a. innovating software tool the CNS Media GateWay which is both cheap and very easy to implement. The CNS Media GateWay (MGW) is an innovative synchronization tool that strives to combine the cost-effectiveness and flexibility of custom code and the power and maintainability of integration suites. You can connect from any custom application like Oracle Database to Access, MS SharePoint, MS Exchange (2000 – 2013), , Dynamics CRM, Dynamics NAV and many more via the CNS ODBC and JDBC driver, thus accessing, for example, the entire Exchange mailbox from your custom application.

    for more info visit: http://www.connecting-software.com

    Thank you

    Nikos

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points