Re: [INTERFACES] Replication between MS Access and PostgreSQL

--- Nicolas Huillard <nhuillard(at)ghs(dot)fr> wrote:
> This description is a way to export tables from Access to Postgres.
> I know how to do this.
> What I'm looking for is a tool to synchronize the data between the
> same table inside Access and Postgres, ie : a new record in
> Postgres should be transfered to Access, and the same the other
> way.
>
> Thanks.

If you only need to transfer new rows and don't care about
updated or deleted rows, it's pretty easy to do, especially if you
have an identifier in your table generated by a sequence
(or the date it was inserted).

in Postgres :

table DATA (rowid, other_columns), index on rowid
table LAST_CHECK (rowid) -- one row in this table

select @rowid1=rowid from LAST_CHECK
select @rowid2=max(rowid) from DATA
select * from DATA where @rowid1 < rowid <= @rowid2
update from LAST_CHECK set rowid=(at)rowid2

(Sybase-like syntax for variables, sorry ...)

And something similar in Access.
You must have a cron job which runs this script periodically
and insert the resultset from the third instruction into Access.
It doesn't need to be transactional.
The both databases should generate identifiers on non-overlapping
ranges, for example negative in Access, replace max by min for the
other script and reverse the comparison order.

Alain