Forum Discussion
Updating a Azure SQL Linked Table From Local Tables
You are probably encountering a conflict with Primary and Foreign Keys on the three related tables, but we need to see the actual error message. Currently, you are hiding the error messages, so you don't know what is happening.
Comment out this line:
DoCmd.SetWarnings False
Then you will see the actual error and you can share it with us to help diagnose the problem better.
Well now I feel foolish haha I didnt think to turn back on the warning messages, mainly becuase the 4 tables are so similar that if the first one worked the others should too.
Anyway after turning on the warnings I got the below three warnings on each of the ones (in order of how they appear in the VBA) that failed:
So they all seem to be key violations, I'm very new to using Azure SQL database so no idea why these three would have an issue and FRT_Table wouldnt, I used the SSMA tool to migrate my tables to Azure SQL, and as far as I can tell they went in fine, its really odd as FRT_Table and the rest are very very similar.
Any ideas on how to fix?
Stildawn I'm going to start by recommending an alternative way to run Action queries in VBA, in order to deal with this particular problem.
Instead of DoCmd.RunSQL "Query"
Use CurrentDB.Execute "Query", dbFailOnError
What that does is execute the query silently (no confirmation messages) unless there is an error. That would have revealed the problem here, PLUS you don't have to worry about turning off warnings and then turning them back on.
That said, the problem appears to be a single record in each case. I would have expected more than one. Did you only add one new record in the original table?
What's going on is possibly that the way new Primary Key values are generated using Identity in SQL Server/SQL Azure as opposed to Access. This is a fairly common problem, by the way.
Access generates new values for AutoNumbers whenever a record is STARTED in the table. That's one reason we can lose values: you start a new record, but cancel before saving it. That value of the AutoNumber is lost.
SQL Server, on the other hand, only generates the new value for an Identity when a record is SAVED. That means it doesn't exist until the save is complete.If the second through fourth queries depend on that value for a PK saved as an FK, that may be the problem. However, that is speculation based on similar experience in the past.
Another possibility is that there is a problem with the values used for Foreign Keys in the second through fourth tables. I would have to see the whole picture to know for sure, which is, of course, not possible.
I think what you'll have to do is set a breakpoint in your code and step through it one line at a time, inspecting each result as you go to identify exactly what values are being generated and how they are being used. It's tedious, but I know of no better way both to figure out the problem AND to get a better understanding of how Access interacts with SQL Server/SQL Azure.- Thanks will update my code to the currentdb.execute versions.
It's showing a single record as a problem because I'm only putting one record in to test, when there are more it still fails on all of them.
I dont think I have FK in any of the tables (not that I'm entirely sure what FKs are), but all 4 tables have no relationships set at all, and certainly not between them.
I think it might be the difference in how the PK autonumber are generated between the two, but in saying that why does the first table work but not the others? The PK in all four tables are just the default ID PK that access autocratic when you make a table for the first time.
