Update to External Database

[JRodko]

My database requires a number of external databases and at the end of each day are updated to an unlinked (often offline) master database using append queries to the external database. Data only ever goes from satellite to master.

The way this currently works is when a new record is created or modified in the satellite database the (ModifiedDate) field is automatically updated to the current time and the (AppendedDate) field is left blank. Each record also has a replication ID (not used as primary key).
When I append new records to the master database, the Append query searches for instances where ModifiedDate is later than AppendedDate and appends them to the master database. The (AppendedDate) is then updated to the current time so that next time these records are skipped.

The problem I just realized is that if any data is modified in the satellite database AFTER those records have been appended, I will get a key violation because there is already an existing replicationID in the master database from when that record was originally appended.



I'm not sure how to get around this. Like I said I cannot link the tables because these databases are often stored offline and the master gets updated manually when I want it to. Perhaps there is a way to overwrite the replication ID occasionally (I know this defeats the purpose of having a replicationID), or I could run update queries based on the Date fields and joined on the replicationID? I don't know how to do the latter however.

Thank you.

[isladogs]

In a similar situation, I update the master database from 'satellites' using a scheduled task in the middle of the night and LOCK the source database whilst the transfer is occurring.
You will probably need to do this on the server or wake up the satellite PC to run the task automatically.

This should get around the key violation issue.
If that's not a feasible approach modify the approach to
a) append only unmatched records that don't exist in the master
b) update any existing records so that later changes are included

Having said all that, I would suggest changing the setup to avoid the need for any of this.
Have your master database in use at all times on a server so its tables become linked tables in your FE and scrap the satellite methods

[JRodko]

Thanks ridders52.

I am not sure I follow but I will investigate this path. It is the updating existing records to the master that I am struggling with. I know how to select those that need to be updated based on a timestamp but I don't know how to update an external table reliably.

[isladogs]

Thanks ridders52.

I am not sure I follow but I will investigate this path. It is the updating existing records to the master that I am struggling with. I know how to select those that need to be updated based on a timestamp but I don't know how to update an external table reliably.

I'm not sure exactly what you need help with here.
In case it helps I've attached 2 items:
a) An outline of an approach that I use
b) How I use Task Scheduler

If you just need help with the queries, I've also attached an example database
This has 2 tables: tblOld & tblNew
2 queries can be used to synchronise tblNew with tblOld - qryAppendNew & qryUpdate

However it also contains a clever query which combines the two - its called various names including an UPSERT query but I prefer the name UPEND query
It can't be used in all cases but its a useful tool to have.
If you want to know more about the UPEND query, see this page on my website: http://www.mendipdatasystems.co.uk/u...ery/4594428616

HTH

[JRodko]

Wow this is great thank you!

After I implement and test it I will follow up with my solution for the next person that searches this problem.

[isladogs]

You're welcome. Get back to me if you have any questions

However I've just discovered you have cross posted at https://www.utteraccess.com/forum/in...ic=2052066&hl=
Please read this link about the etiquette of cross posting https://www.excelguru.ca/forums/show...-cross-posters