Forum Discussion
Updating a Azure SQL Linked Table From Local Tables
The way I would explore doing that is to add a field to each of the Access table to hold the values for the PKs from the Azure tables. These would not change, but would be used to link back to the Azure tables when doing the updates/appends. Would that work?
This is because when the admin runs an update (lots of data at once, via spreadsheets) the IDs are created locally, and the queries are run locally on that data.
But the ID changes when saved to Azure SQL tables.
This doesnt matter as currenly on open the Azure SQL tables replaces the local tables and the queries are run again, but I'm trying to save the query data as well so that it doesn't need to be run on opening each time, only when the admin updates/adds data.
Just a thought, if I removed the PK from the Azure SQL tables, would that mean I could push in the Access ID numbers?
- Ah ok so we can still use PK in SQL but just not INDENTITY?
I thought the two were linked.
Issue is that on the Master_Data2_Temp table the ID needs to allow duplicates, which if I'm not mistaken means it cant be a PK? If you don't identify the Primary Key on a table when linking it in Access, then it is not updateable. That's the most likely reason.
We want to remove the Identity property, but leave the Primary Key itself.
And when Linking the table, Access wants us to identify the PK manually.- Sorry posted too soon.
I get that error on:
CurrentDb.Execute "DELETE * FROM dbo_Master_Data2_Temp", dbFailOnError + dbSeeChanges
It wont let me run the delete on that table for some reason, all the other tables work.
I did create that table myself manually in SSMS the other ones were created by the Access to SQL migration tool way back when I did that.
I also noticed that in the Access frontend, that table "dbo_Master_Data2_Temp" when opened the delete record option is greyed out and I cannot added a record directly into the table.
All the other dbo tables I can delete / add as per usual.
I'm guessing I missed a setting or something when I create it? Hi George
Ok so I did the below:
Created a copy of my current Azure SQL Database, called it Testbed
1. Removed ID PK on FRT / Additionals / Locals / Transits
2. Removed IDENTITY on ID on FRT / Additionals / Locals / Transits
3. Added LastModified column on FRT / Additionals / Locals / Transits
4. Run Create Indexs on FRT / Additionals / Locals / Transits
5. Change ID index to Unique on FRT / Additionals / Locals / Transits6. In Access, added LastModified field to FRT / Additionals / Locals / Transits
I believe this means its all setup to work, but my code below (to update Local to SQL) gets a runtime 3086 - Could not delete from specified tables - on the bolded line:
Public Function UpdateToSQL() ' Deletes all SQL records from tables CurrentDb.Execute "DELETE * FROM dbo_FRT_Table", dbFailOnError + dbSeeChanges CurrentDb.Execute "DELETE * FROM dbo_FRT_Additionals_Table", dbFailOnError + dbSeeChanges CurrentDb.Execute "DELETE * FROM dbo_Locals_Table", dbFailOnError + dbSeeChanges CurrentDb.Execute "DELETE * FROM dbo_Transits_Table", dbFailOnError + dbSeeChanges 'Uploads data to SQL from local tables CurrentDb.Execute "INSERT INTO dbo_FRT_Table SELECT FRT_Table.* FROM FRT_Table;", dbFailOnError + dbSeeChanges CurrentDb.Execute "INSERT INTO dbo_FRT_Additionals_Table SELECT FRT_Additionals_Table.* FROM FRT_Additionals_Table;", dbFailOnError + dbSeeChanges CurrentDb.Execute "INSERT INTO dbo_Locals_Table SELECT Locals_Table.* FROM Locals_Table;", dbFailOnError + dbSeeChanges CurrentDb.Execute "INSERT INTO dbo_Transits_Table SELECT Transits_Table.* FROM Transits_Table;", dbFailOnError + dbSeeChanges 'Deletes Master_Data2_Temp table CurrentDb.Execute "DELETE * FROM Master_Data2_Temp", dbFailOnError CurrentDb.Execute "DELETE * FROM dbo_Master_Data2_Temp", dbFailOnError + dbSeeChanges 'Runs Master_Data2 and adds to temp table CurrentDb.Execute "INSERT INTO Master_Data2_Temp SELECT Master_Data2.* FROM Master_Data2;", dbFailOnError CurrentDb.Execute "INSERT INTO dbo_Master_Data2_Temp SELECT Master_Data2_Temp.* FROM Master_Data2_Temp;", dbFailOnError + dbSeeChanges End FunctionIn that case, yes, you can use the Access generated AutoNumber PKs. Keep in mind that this requires very consistent processing, but it can be done.
What you'd have to do is remove the Identity Property from the PK field in the Azure tables, and allow ACCESS to create those values and then assign them to the records in Azure during appends. The risk there, of course, is a failed or partial append or update going out of synch. Given the situation that seems more acceptable, though.
