Updating a Azure SQL Linked Table From Local Tables (1 Viewer)

[Stildawn]

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.

 

[theDBguy]

Hi. How many users will be using your application? If more than one, then I would probably spend more time figuring out a permanent solution, than a workaround one. What is the actual problem you're trying to work around? Is it the speed?

If you have more than one user, how will you synchronize all the changes made be all of them. If I read your code correctly, it would seem to me all changes made by another user can be overwritten by the data from whoever last upload their changes back to Azure. If so, that can't be a good thing, is it?

 

[Stildawn]

You are correct that it could be overwritten, but it will be a non-issue in the short term, many users will "view" the data via the main form etc, but only 1 or 2 will actually update any data and highly highly unlikely at the same time. The updates will likely only be once or twice a month, so accuracy of data isn't a massive concern and can be handled as simply as a "We have updated the database" email.

And those update users would need password access to do the updates so all the "view" users wont be able to accidentally update anything.


The main issues is that it went from a local database over LAN, which obviously would have worked perfectly with just the standard Access split Frontend/Backend, to a database over WAN/WEB, in which performance was so bad it was unuseable, we are most likely going down Azure SQL route, but obviously I need to do "alot" of optimising work as it wasn't originally built around a WAN Azure SQL model.

Unfortunately I need to get a workable solution into the hands of users over the next few days, my solution above will work fine as the initial database data will be very small (increasing over time), and it will give me the time to work on a proper performance/optimsation for Azure SQL / WAN / WEB (or even a whole new front end if required, just using the already created Azure SQL tables).

 

[Stildawn]

Further to my issue, feel a bit foolish but by turning on the warnings I actually got some useful information:

This is the error message I get on each of the not working insert commands, 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.

 

[theDBguy]

You are correct that it could be overwritten, but it will be a non-issue in the short term, many users will "view" the data via the main form etc, but only 1 or 2 will actually update any data and highly highly unlikely at the same time. The updates will likely only be once or twice a month, so accuracy of data isn't a massive concern and can be handled as simply as a "We have updated the database" email.

In that case, I think maybe you can simplify your approach a bit if you could modify the tables right now and add a DateModified field in all of them. Then, all you have to do is pull only those records that were actually modified, rather than pull the complete set.

Just a thought...

 

[Stildawn]

Thats a good idea, and will save on the loading times as I can only pull the new data each time rather than the whole lot.

Any ideas on the key violations, I have looked over my local tables in Access and looked in SQL Management Studio for the SQL tables but I can't see where they are different from the working FRT_Table?

 

[theDBguy]

Any ideas on the key violations, I have looked over my local tables in Access and looked in SQL Management Studio for the SQL tables but I can't see where they are different from the working FRT_Table?

Rather than chase your tail on this, perhaps you could try starting over by manually copying the tables locally, so that you can concentrate on implementing the new syncing system.

Just a thought...

 

[Stildawn]

How would I do that?

Delete the Azure SQL database and start again with SSMA?

 

[theDBguy]

How would I do that?

Delete the Azure SQL database and start again with SSMA?

No. Just, create a new FE, link the tables to Azure, and then create the local tables. Now, you no longer have to worry about your code of deleting and repopulating your local tables. Instead, create your new code for only pulling the updated records and hope you don't get the same errors.

 

[Stildawn]

Oh no that won't work.

The front-end is very involved, months and months have gone into it.

 

[Minty]

Hi - As I said here
cross posted -Access Forums Thread
and as the @theDBguy has suggested you should fix the main issue not mess around with this.

 

[Stildawn]

I dont have time, I only have 2 days now

And optimizing will take me months.

 

[Minty]

Having had time to look at your example db your problem appears to be the Date field data types that the Migration assistant has choosen in Azure.

If you look at your tables in Design mode they are showing as Date/Time Extended. Change theses in Azure to either SmallDateTime or DateTime and most of your issues should disappear.

For proof open the List8 row source and try opening it in datasheet view.
Union query Master_Data2 won't open either.

 

[theDBguy]

Oh no that won't work.

The front-end is very involved, months and months have gone into it.

What does the design of the FE have to do with it? I was just talking about the data. Whatever code you're using for the synchronization, all I'm saying is simplify it.