Forum Discussion
Updating a Azure SQL Linked Table From Local Tables
Hi All
I am working on a work around to get my access database ready to deploy as its needed urgently.
The work around that I want to use, is to upon form open have the Access frontend pull all data from 4 linked Azure SQL tables into the local tables, then the users uses the forms etc from the local table data (a copy of Azure SQL data), this means performance is good as its working locally, but with a increased loading time as it grabs the current data from Azure SQL.
Now the pull from Azure SQL VBA code works fine and is as below:
Private Sub Form_Load()
'Will turn on the below + maybe add more to get the final look I want
'DoCmd.ShowToolbar "Ribbon", acToolbarNo
' Deletes all local records from tables
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM FRT_Table"
DoCmd.RunSQL "DELETE * FROM FRT_Additionals_Table"
DoCmd.RunSQL "DELETE * FROM Locals_Table"
DoCmd.RunSQL "DELETE * FROM Transits_Table"
'Uploads data from SQL to local tables
DoCmd.RunSQL "INSERT INTO FRT_Table SELECT dbo_FRT_Table.* FROM dbo_FRT_Table;"
DoCmd.RunSQL "INSERT INTO FRT_Additionals_Table SELECT dbo_FRT_Additionals_Table.* FROM dbo_FRT_Additionals_Table;"
DoCmd.RunSQL "INSERT INTO Locals_Table SELECT dbo_Locals_Table.* FROM dbo_Locals_Table;"
DoCmd.RunSQL "INSERT INTO Transits_Table SELECT dbo_Transits_Table.* FROM dbo_Transits_Table;"
DoCmd.SetWarnings True
End Sub
On the other end when a user needs to save any changes to the local tables and send that to the Azure SQL then I use this code, which is literally the above but in reverse:
Sub UpdateSQLServer()
' Deletes all SQL records from tables
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM dbo_FRT_Table"
DoCmd.RunSQL "DELETE * FROM dbo_FRT_Additionals_Table"
DoCmd.RunSQL "DELETE * FROM dbo_Locals_Table"
DoCmd.RunSQL "DELETE * FROM dbo_Transits_Table"
'Uploads data to SQL from local tables
'This one works
DoCmd.RunSQL "INSERT INTO dbo_FRT_Table SELECT FRT_Table.* FROM FRT_Table;"
'These lines dont work??
DoCmd.RunSQL "INSERT INTO dbo_FRT_Additionals_Table SELECT FRT_Additionals_Table.* FROM FRT_Additionals_Table;"
DoCmd.RunSQL "INSERT INTO dbo_Locals_Table SELECT Locals_Table.* FROM Locals_Table;"
DoCmd.RunSQL "INSERT INTO dbo_Transits_Table SELECT Transits_Table.* FROM Transits_Table;"
DoCmd.SetWarnings True
End Sub
My issues is that as noted in the code above, my first insert line: DoCmd.RunSQL "INSERT INTO dbo_FRT_Table SELECT FRT_Table.* FROM FRT_Table;" works flawlessly but the other 3 lines do not work and for the life of me I dont understand why?
Any ideas on what I am missing?
I know that the above is a bad band-aid work around, but I only have a few days left before deployment is required so I just want to do this asap, which will then give me plenty of time to optimse the whole database for working with Azure SQL directly.
36 Replies
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.
- So how do I get around non-unique and the runtime error issue?
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.
- Unfortunately, not that I know of.
Hi George
Thanks for all your help on this, did the above and have two scenarios:
Using the old DoCmd.RunSql code it now works perfectly after I removed the ID field from the inserts into SQL tables.
However using the CurrentDb.Execute code it works grapping from SQL to local tables, but on the upload to SQL tables I get below error:
Run-Time Error 3622
You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY columnCode is as below:
' Deletes all SQL records from tables CurrentDb.Execute "DELETE * FROM dbo_FRT_Table", dbFailOnError CurrentDb.Execute "DELETE * FROM dbo_FRT_Additionals_Table", dbFailOnError CurrentDb.Execute "DELETE * FROM dbo_Locals_Table", dbFailOnError CurrentDb.Execute "DELETE * FROM dbo_Transits_Table", dbFailOnError 'Uploads data to SQL from local tables CurrentDb.Execute "INSERT INTO dbo_FRT_Table SELECT FRT_Table.[POL Name], FRT_Table.[POD Name], FRT_Table.Carrier, FRT_Table.[Contract Type], FRT_Table.Contract, FRT_Table.[20GP Cost], FRT_Table.[40GP Cost], FRT_Table.[40HC Cost], FRT_Table.[Valid From], FRT_Table.[Valid To], FRT_Table.Notes FROM FRT_Table;", dbFailOnError CurrentDb.Execute "INSERT INTO dbo_FRT_Additionals_Table SELECT FRT_Additionals_Table.Carrier, FRT_Additionals_Table.[20GP BAF], FRT_Additionals_Table.[40GP BAF], FRT_Additionals_Table.[40HC BAF], FRT_Additionals_Table.[20GP GRI], FRT_Additionals_Table.[40GP GRI], FRT_Additionals_Table.[40HC GRI], FRT_Additionals_Table.[20GP PSS], FRT_Additionals_Table.[40GP PSS], FRT_Additionals_Table.[40HC PSS], FRT_Additionals_Table.[20GP MISC], FRT_Additionals_Table.[40GP MISC], FRT_Additionals_Table.[40HC MISC], FRT_Additionals_Table.[Valid From], FRT_Additionals_Table.[Valid To], FRT_Additionals_Table.Notes FROM FRT_Additionals_Table;", dbFailOnError CurrentDb.Execute "INSERT INTO dbo_Locals_Table SELECT Locals_Table.Carrier, Locals_Table.[POD Name], Locals_Table.[20GP THC], Locals_Table.[40GP THC], Locals_Table.[40HC THC], Locals_Table.[BL Flat Fee], Locals_Table.[SEC Fee], Locals_Table.[SEC Fee Currency], Locals_Table.[Valid From], Locals_Table.[Notes] FROM Locals_Table;", dbFailOnError CurrentDb.Execute "INSERT INTO dbo_Transits_Table SELECT Transits_Table.POLName, Transits_Table.PODName, Transits_Table.Carrier, Transits_Table.Transit, Transits_Table.Direct FROM Transits_Table;", dbFailOnErrorI'm not too fussed at the moment because I have a working version using the DoCmd.Runsql code, however I did notice that CurrentDb.Execute seems much faster so would prefer to use that if possible.
Thanks again
I believe the technical term is "oh crap, I forgot."
Yes, when working with SQL Server linked tables, that option is required.
CurrentDB.Execute "SQLStringGoesHere", DBFailOnError + dbSeeChanges
Sorry.
- It's not really code per se. It's the SQL that you run. I believe that I saw at least one other place where you actually used that same method to append selected fields. However, you could also do this as a saved query and just execute that query.
- Yeah I know how to do it, was just wonder if there was a exclusion way rather than an inclusive way if that makes sense
Well, no time to confirm, but I "THINK" the problem is that you are using the wildcard * in your append queries to select ALL fields from the source, local, tables to insert to the SQL Azure tables, and that includes the "ID" field, which is the AutoNumber (or in SQL Azure, the Identity). If the local "ID" is also called "ID" in the linked table, that's not going to work. SQL Azure is much tougher on unique values for Identity datatypes.
Instead, write the SQL to include SPECIFICALLY all fields except that "ID" field. Let SQL Azure generates its own unique Identity values. See if that works. If not, I'll dig into it this afternoon.
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.
