I have the following question. I have migrated a "traditional build" Access frontend application with a SQL server backend to Azure SQL. The linking goes fine, however the performance often leaves something to be desired. I managed to link the tables directly to the Azure SQL by an ODBC connection with ActiveDirectoryInteractive authentication which uses a twofactor authentication.
At the time, I built the application traditionally, with various subforms, comboboxes and the forms are all binded directly to a table or query.
After some research, I now understand that a traditional build Access is not handy when it comes to using a remote SQL as a backend. And the best solution would be redesining it. However, this is a customer app and that customer will not be happy if the entire application has to be rebuilt all over again.
In order to improve the performance I have replaced queries whenever possible with pass trough queries for the comboboxes and subforms. There is a form with a heavy load with multiple subforms each on a separate tab. I have limited the loading of these subforms so that only when the user clicks on the tab the corresponding subform is loaded. I am trying to minimize data trafic between the Access frontend and the Azure backend in every possible way.
I read in another post that instead of binding a table directly to a form, I need to redesign these forms with single record queries, so that criteria are applied to select and return one record and a time and requery that one-record recordset.
But how does that work? I tried it with a form by loading a record set with one record in the form, which works fine, however the form is not updateable any more. The user off course needs to be able to update the data in the form. So what's the solution? Create a separate form with a temporary table in case the user wants to update the form and switch between the read and this update form? Or is there a better approach for this?
