Updating a Azure SQL Linked Table From Local Tables

Copper Contributor

Mar 29, 2021

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 column

Code 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;", dbFailOnError

I'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

George_Hepworth

Silver Contributor

Mar 29, 2021

Stildawn

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.

Timot90

Copper Contributor

Apr 13, 2021

George_Hepworth

Hi again George, its me from above, just can't seem to get this website to login under my personal account so I was forced to start this one under what I assume is my work account.

Anyway, regarding my fix we did above.

Moving forward I wanted to do a bit more optimisation to get things to run smoother in this current version, my issue is that with the code we worked out below:

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.[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 + dbSeeChanges
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 + dbSeeChanges
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 + dbSeeChanges
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;", dbFailOnError + dbSeeChanges

End Function

Public Function UpdateFromSQL()
' Deletes all local records from tables
CurrentDb.Execute "DELETE * FROM FRT_Table", dbFailOnError
CurrentDb.Execute "DELETE * FROM FRT_Additionals_Table", dbFailOnError
CurrentDb.Execute "DELETE * FROM Locals_Table", dbFailOnError
CurrentDb.Execute "DELETE * FROM Transits_Table", dbFailOnError

'Uploads data from SQL to local tables
CurrentDb.Execute "INSERT INTO FRT_Table SELECT dbo_FRT_Table.* FROM dbo_FRT_Table;", dbFailOnError
CurrentDb.Execute "INSERT INTO FRT_Additionals_Table SELECT dbo_FRT_Additionals_Table.* FROM dbo_FRT_Additionals_Table;", dbFailOnError
CurrentDb.Execute "INSERT INTO Locals_Table SELECT dbo_Locals_Table.* FROM dbo_Locals_Table;", dbFailOnError
CurrentDb.Execute "INSERT INTO Transits_Table SELECT dbo_Transits_Table.* FROM dbo_Transits_Table;", dbFailOnError

End Function

This is working, however what is happening is that the ID fields are being deleted each time and the Azure SQL tables are creating new ones each update, so the ID changes from run to run.

Now this isnt a problem currently, however the further optimising I want to implement requires the ID's to remain the same from run to run.

Is there any way we could pass the ID's from the local tables to Azure SQL without them changing? Ideally using the ID's generated by Access local tables?

George_Hepworth
Silver Contributor
Apr 13, 2021
Timot90
While it is possible to do what you suggest with pushing locally generated Primary Key values back into the SQL Azure tables, it is not simple. I'm not sure I'd even want to go that route because it would involve a separate transactionin which you'd have to disable the Identity_Insert on the Azure tables, in turn, run the append query, the re-enable the Identity_Insert on the Azure table, disable it on the next Azure table, run that append, and re-enable it again.... Four times. And that risks things going out of synch. You'd want to wrap the whole thing in a transaction so that all four have to complete in order to commit that transaction. Doable, but complex.
For that reason, I think I'd lean more towards considering the SQL Azure tables' Primary Keys to be the master values and only use temporary key values locally in the Access tables. But that runs into the fact that you are also transitioning from the Access tables to the new environment.

I'm going to go ask someone I know who has done something similar in his own projects to see if he has any insights.

Stildawn
Copper Contributor
Mar 29, 2021
Ah thanks heaps that was easy.

Everything seems to be working so thank you very much.

Forum Discussion

Updating a Azure SQL Linked Table From Local Tables

Resources