Sync Remote Database
Access Web Sync Seminar

Hi, You could create a log file of the changes made during the week or whatever time you want and update from there. You said the test database id was a copy of the main one. Maybe COPY it?  HTh John

Is it a split db?
Are you changing Table fields, adding data
Creating new Forms, updating Forms etc

Alex is asking the key question here: are you only adding records, or are existing records also being edited or deleted? That makes a big difference.

My first choice would be to split the database, if you haven't already. Keep your daily live data in a production back end, and have a separate sandbox back end containing a copy of that data. Your sandbox front end can then be used for forms, reports, queries, and VBA experimentation without touching the production database.

Once a month, if you just want the sandbox brought back to the current live state, the simplest and most reliable method is usually to make a fresh copy of the production back end and replace the sandbox back end with it. Make a backup of the old sandbox first, of course, in case it contains test records or other work you want to keep.

If you need to preserve test data in the sandbox while importing only new production records, then use append queries based on a unique primary key. For example, append records from the production table only where that primary key doesn't already exist in the sandbox table. That works well for new records, but updates and deletions require additional logic. At that point, you're building a synchronization process, which is more complicated than it first appears.

So my practical recommendation is:

1. Split the database if it isn't already split.
2. Keep development objects in a separate sandbox front end.
3. Periodically replace the sandbox data back end with a fresh copy of the production data.
4. Keep any test-only data in separate tables, if possible, so it isn't lost during the refresh.

That is usually much easier and safer than trying to keep two independently edited copies synchronized.

But check out the videos that Alex pointed to. I do cover a lot of different techniques in those.

Thank you for these very detailed explanations.
It is not (yet) a split database and I surely will consider that.
I immediately followed Alex' hint and watched the Sync Remote Database video which I found very informative. I think it will be the first thing to explore further and before going into detail of Richard's recommandation.
To sum up, this was exactly what I was looking for, and I am sure I will follow these leads.
Thank you a lot

Stefan I've been thinking about a similar situation.  I have a database with multiple front-ends and one back-end.  The back-end is on a server that goes off line every once in a while.  I've been thinking about how to allow Users to keep working using temp files in the front-end and then sync with the back-end when the server comes back on line.  You can really go down a rabbit hole thinking about the table(s) setup, change tracking, and VBA code needed to resync tables (new records, modified records, etc.).  I still haven't drank enough coffee to get it all straight.....  LOL!!!  LLAP!

A few observations from the trenches: Sometimes I need to synch not just whole records, but individual field data.
Deleting the record isn't always an option given that many records have relationships that won't allow a delete (for later append).
If the two tables had their fields created in a different sequence order, or there is a slight mismatch in names or number of fields, that Home>Paste>Paste append can be very stubborn.

Sam Some months ago I started working on a group of VBA routines to handle synching up and down a chain of BE sites, because of the immediately above listed problems. I'm still falling, falling down the deep rabbit hole every time I return to the code.

Sam and Thomas, exactly. Once you move beyond "copy the whole back end and replace it," synchronization gets complicated very quickly.

You have to account for new records, edits on either side, deletions, conflicting edits to the same record, related records, temporary primary keys for offline-created records, and whether you are syncing an entire record or only fields that changed. Then you need a reliable way to track all of that, usually with change timestamps, user information, and some kind of sync log.

For an occasional sandbox refresh, I would still avoid that rabbit hole and just replace the sandbox back end with a fresh production copy. But for Sam's situation, where users may need to work while the server is unavailable, a real offline sync design is needed. It can certainly be done, but it needs to be designed carefully from the beginning.

Replication IDs become your best friend.

Access used to have a utility that compares two databases, but Microsoft removed it in June 2026:

     https://techcommunity.microsoft.com/blog/accessblog/access-announces-retirement-of-database-compare-tool-in-june-2026/4503597

It was a pretty primitive utility anyway from my experience of it.  There are third-party paid utilities at the bottom of the webpage below, but some are expensive:

     https://office-watch.com/2026/microsoft-access-database-compare-end/

Syncing two databases is quite non-trivial as others have said.  It's usually done by big corps that want to sync multiple database servers in different locations.  Advanced database tools like SQL Server already have built-in abilities to "replicate" databases for such purposes.

Kevin I haven't used Sequel Server, and my friend with experience in it is still sleeping. I'm curious if its replicate feature could handle a situation such as: "Update from this database to that one all the new records in the tbl_3_Element table and overwrite different field values if the UdtUsr field isn't 103"?

Thomas  You can create "rules" as to what to replicate or not, or you can replicate everything.  For instance, you may want to replicate everything except fields that contain sensitive data, such as a credit card number field.

Kevin is right that SQL Server has several built-in replication options, but Thomas's specific example is getting into custom business-rule territory.

SQL Server has a Change Tracking feature that can be enabled on a database and its tables to identify rows that have changed. A rowversion field is another useful tool: SQL Server automatically changes its value whenever the row is inserted or updated. Those make it much easier to ask, "What has changed since my last sync?" Change Data Capture is another option for more detailed tracking.

However, a rule like "copy new tbl_3_Element records, but only overwrite changed fields when UdtUsr isn't 103" is not something I would expect standard replication to handle exactly as stated without additional setup. SQL Server replication can filter what gets replicated, and Merge Replication has conflict detection and resolution options, but applying conditional field-by-field update rules usually means custom replication procedures or your own sync logic.

That's the part that sends us down the rabbit hole. New records are relatively easy, especially when you use Replication IDs (GUIDs) so independently created records won't get the same primary key. Tracking edits is manageable with a LastUpdated timestamp, user ID, and/or rowversion. But once both databases can edit the same record, you must decide which change wins, whether to merge individual fields, and what happens to related records and deletes.

For my own databases, I do some replication between the office database and the website database. For example, all of these comments get copied down from the server every 10 minutes, or whatever the scheduled cycle happens to be, so I have a local copy if something happens to the website. Since that is primarily a one-way process, it is much simpler. I can track when records were added or modified and pull down only the changes.

Access has table-level data macros that can help track changes, but I don't particularly trust them. I would rather use a form's AfterUpdate event, or controlled VBA update routines, to maintain fields such as LastUpdated and UpdatedBy. That works fine when you control the direction of the updates.

But true two-way synchronization, especially offline synchronization like Sam described, is a whole separate beast. I could easily do a two-hour class just on collision detection and conflict resolution.