Forum Discussion
Updating a Azure SQL Linked Table From Local Tables
We sort of moved onto a different aspect here. Why would the non-unique values come into play at this point? Where are they coming from? I'm pressed for time, and I think I need to re-study the entire discussion to see where I lost the plot. Later today.
- The original Master_Data2 is a query, it's actually the result of 3 queries, that combines lots of data from the tables together.
If required (due to valid dates of data) the queries will make two rows with the same ID (from the FRT_Table) but with different data in the other columns.
But since this set of queries only need to be run when the admin does an update, I'm trying to eliminate every user running it locally by storing the result in a Master_Data2_Temp table, and transferring this to Azure SQL so that all users share the same one.
So yeah often the ID column will have duplicates."The original Master_Data2 is a query, it's actually the result of 3 queries, that combines lots of data from the tables together."
That's a fundamentally different kind of task from the original one as I understood it, which was to copy down data to use locally in order to speed up local processing, and then restoring that same data back to the server. That involves one table to one table movement of data, not aggregation of data.
You'll need to stick to that original protocol, IMO. Copy each table down to its corresponding local Access table, and then restore each table individually back to its corresponding remote SQL server table.
I get the idea of making it available to users dynamically, but that's not really feasible in this scenario.
Besides, if users are also working with a shared Access back end accdb, and not individual accdbs on their own desktops, then the local Access data will not need to be replicated across all of those users' computers anyway. This migration to and from SQL Server is a separate and conceptually different process.
- The Master_Data2 query only needs to be run when admin updates, about 2 -3 times per month.
Currently with the bad design (which I am working on a complete rebuild, but need to keep this current version working as well as it can) the Master_Data2 query takes about 2 -3 mins to run, and its only going to get worse as data gets added.
Currently on application open, it pulls all data from the 4 main tables on SQL into Local tables, and then runs the Master_Data2 query (which takes ages) it does this for every user on every load. I need to eliminate this as much as possible.
So since the Master_Data2 query only needs to be run on admin update, I want to just store the result until the next admin update. This is what I am trying to achieve.
Running it locally based on the tables is not ideal as its very slow each time.
I might try deleting the dbo_Master_Data2_Temp table and relink it and declare a PK and then delete the PK and see if that works.