Forum Discussion
Updating a Azure SQL Linked Table From Local Tables
Right. In SQL Server, it's possible to designate the int Datatype as the PK without having it autogenerate the values. That's controlled by the Identity property. In Access, as we know, the AutoNumber is the functional equivalent, but to create a non-autonumber PK, we have to change to the Long Integer datatype.
If the value in a field can't be unique, then, no it's not a candidate to be a Primary Key, although it can still be designated as the Foreign Key to another, related table and it can be indexed.
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.