Create Access DB from Oracle/SQL Tables

[arusoft]

We use both Oracle and SQL server. We create Access backup of few tables which is then used for data entry in disconnected mode. We then sync it back to ORACLE/SQL Server database.

Right now we are doing this manually by going to each record and inserting to access database.

Can you please suggest if you have better way by using Unidac components to achieve the above.

Thanks.

[AlexP]

Hello,

For data synchronization you can use our CRBatchMove component, that allows transfer data from one DataSet to another automatically. Moreover, in this component, you can choose behaviour modes on finding identical records -

Code: Select all

TCRBatchMove.Mode = (bmAppend, bmUpdate, bmAppendUpdate, bmDelete)

You can find more detailed information about this component in the UniDAC documentation.

[arusoft]

Thanks for reply. Sorry I am not able to find detailed document about this component.

for example I want to know how to create access DB from oracle DB for one or multiple tables. And the after changes are done to access DB, I will want to use it to update ORACLE DB.

Also I want to know what logic dose this components uses.

Thanks.

[AlexP]

Hello,

There is a sample below demonstrating data transfer from an Oracle table to MS Access and vice versa.
When using CRBatchMove, data from the Source table are transferred to the Destination table with mapping fields by name ( CRBatchMove1.FieldMappingMode = mmFieldName) or by index (CRBatchMove1.FieldMappingMode := mmFieldIndex), dependently on the chosen mode: CRBatchMove1.Mode = (bmAppend, bmUpdate, bmAppendUpdate, bmDelete). You can also set appropriate behaviour on errors in the AbortOnProblem and AbortOnKeyViol options.

Code: Select all

program pBatchMove;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  UniProvider,
  OracleUniProvider,
  Uni,
  CRBatchMove,
  ODBCUniProvider,
  AccessUniProvider;

var
  ConnectionOra: TUniConnection;
  TableOra: TUniTable;
  ConnectionAccess: TUniConnection;
  TableAccess: TUniTable;


procedure MoveData(Source: TUniTable; Destination: TUniTable; Mode: TCRBatchMode; ClearDest: boolean = false);
var
  BatchMove: TCRBatchMove;
begin
  if ClearDest then
  try
    Destination.Connection.ExecSQL('DELETE FROM ' + Destination.TableName);
  except

  end;

  BatchMove := TCRBatchMove.Create(nil);
  try
    BatchMove.Source := Source;
    BatchMove.Destination := Destination;
    BatchMove.Mode := Mode;
    try
      BatchMove.Execute;
    except
      on E: Exception do
        Writeln(E.Message);
    end;
  finally
    BatchMove.Free;
  end;
end;

begin
  ConnectionOra := TUniConnection.Create(nil, 'Server=ORCL1020;UID=scott;PWD=tiger');
  try
    ConnectionOra.ProviderName := 'Oracle';
    ConnectionOra.Connect;
    TableOra := TUniTable.Create(nil);
    try
      TableOra.Connection := ConnectionOra;
      TableOra.TableName := 'DEPT';
      TableOra.KeyFields := 'DEPTNO';
      TableOra.Open;

      ConnectionAccess := TUniConnection.Create(nil, 'Database=d:\master.MDB');
      try
        ConnectionAccess.ProviderName := 'Access';
        ConnectionAccess.Connect;
        TableAccess := TUniTable.Create(nil);
        try
          TableAccess.Connection := ConnectionAccess;
          TableAccess.TableName := 'DEPT';
          TableAccess.KeyFields := 'DEPTNO';
          TableAccess.Open;
          //Data transfer from Oracle to MS Access (with clearing the MS Access table)
          MoveData(TableOra, TableAccess, bmAppend, True);
          //data modifying in MS Access
          TableAccess.First;
          TableAccess.Edit;
          TableAccess.FieldByName('DNAME').AsString := 'TEST';
          TableAccess.FieldByName('LOC').AsString := 'TEST';
          TableAccess.Post;
          //new record insertion to MS Access
          TableAccess.Append;
          TableAccess.FieldByName('DEPTNO').AsInteger := 50;
          TableAccess.FieldByName('DNAME').AsString := 'NEW TEST';
          TableAccess.FieldByName('LOC').AsString := 'NEW TEST';
          TableAccess.Post;
          //data transfer from MS Access to Oracle
          //the new data is inserted, the existing data is updated
          MoveData(TableAccess, TableOra, bmAppendUpdate);
        finally
          TableAccess.Free;
        end;
      finally
        ConnectionAccess.Free;
      end;
    finally
      TableOra.Free;
    end;
  finally
    ConnectionOra.Free
  end;
end.