Page 1 of 1
Create Access DB from Oracle/SQL Tables
Posted: Thu 23 May 2013 21:19
by 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.
Re: Create Access DB from Oracle/SQL Tables
Posted: Fri 24 May 2013 10:27
by 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.
Re: Create Access DB from Oracle/SQL Tables
Posted: Fri 24 May 2013 14:15
by 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.
Re: Create Access DB from Oracle/SQL Tables
Posted: Mon 27 May 2013 08:19
by 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.