Click here to Skip to main content
16,021,181 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I'm fetching data from Microsoft access database file into PostgreSQL Database, then later some changes are added into Microsoft access database file and then re-uploaded into PostgreSQL Database as the latest dataset file with changes, now the challenge is how can I compare from previous state and then, if there is change how can I track these changes made between the previous data and the current data re-uploaded into PostgreSQL using spring boot as a back-end technology.

Any suggestions will be much appreciated.

What I have tried:

I tried to write a query to go through all records, but it takes lot of time, something that is not very efficient.
Posted
Updated 27-Oct-23 5:00am
v2
Comments
[no name] 26-Oct-23 4:49am    
You have to implement a 'synchronisation' between your two DBs.

trigger on both could be a great help. they are often internals for each DB, it's same as a query, but with 'condition' launching a transaction ( or other processes ).

on 'insert' / or on 'update' / or on 'delete' could be the events achieving your goal.
The syntax of internal Triggers are so close from sql query.

It's the more reliable tool for you to achieve, because triggers keep nest in your DB, as resources.

To make comparing some fields, you can use Hashes and DateTimes as new columns in your two db.
It's effiscient and act as good clue, for every fields, or rows in your db.
It's less to 'select' too.
Dave Kreskowiak 26-Oct-23 11:11am    
Access doesn't support triggers.
Office Systems 26-Oct-23 5:10am    
Thanks so much @Member15627495 for your quick response however I'm so sorry for asking this can you please try to explain it in a more simple way so that I can get more clarification on how I can implement proper mechanism to handle the scenario.

1 solution

Add a "timestamp" to the Access records; and use a new timestamp when you update and insert. You can't "delete" initially; you'll need to add a "delete" flag (and timestamp) so you can export "deleted" records; and purge / archive them later. You "sync" at some given time by importing / exporting everything for a given timestamp (range).
 
Share this answer
 
Comments
Office Systems 27-Oct-23 5:02am    
Thanks so much @Gerry Schmitz the idea seems to work, much appreciated 👍

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900