How to track changes made in data tables between two access database files

Question

0.00/5 (No votes)

See more:

, +

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 25-Oct-23 21:40pm

Office Systems

Updated 27-Oct-23 5:00am

Deeksha Shenoy

v2

Add a Solution

Comments

Member 15627495 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 Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Gerry Schmitz · Accepted Answer · 2023-10-26T08:08:00

Solution 2

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).

Posted 26-Oct-23 8:08am

Gerry Schmitz

Comments

Office Systems 27-Oct-23 5:02am

Thanks so much @Gerry Schmitz the idea seems to work, much appreciated 👍