Making code run faster with Azure SQL (1 Viewer)

Morning all

I've recently upsized my test Access db and are currently learning of various ways of making it running faster.

I have got a number of scenarios where I add new records to various tables in VBA by using AddNew with DAO Recordset.

My understanding is that currently, when I open a recordset, all data from a specific table is transferred to the Front End database and then the new data row is added. Therefore, I am wondering what best practice is - whether it's an INSERT query or a different way of using the Recordset?

My understanding is that currently, when I open a recordset, all data from a specific table is transferred to the Front End database and then the new data row is added.

Click to expand...

not sure where you got that idea from.

many inexperienced users will use a table as the recordsource to a form, which is generally bad practice for tables with more than a few records and which is what you may be referring to. Either way, this occurs when the form is opened, not when you add a new record.

also unbound forms (which is what you appear to have) can be slower than bound forms, depending on the basis you are connecting to the back end

have you considered using a bound form with data entry set to yes and using a recordsource query to return the records actually required?

see this link about access performance tips
https://www.access-programmers.co.uk/forums/showthread.php?t=291269&highlight=sql+server

Azure is on the web, so consequently you do need to minimise traffic between the FE and BE.

Also consider using stored procedures

mrtn said:

I have got a number of scenarios where I add new records to various tables in VBA by using AddNew with DAO Recordset.

My understanding is that currently, when I open a recordset, all data from a specific table is transferred to the Front End database and then the new data row is added.

Click to expand...

There is an option dbAppendOnly (I think) for OpenRecordset. If you use that, no data will be pulled to the client when opening the recordset.
Internally that is equal to opening a recordset with SELECT * FROM yourTable WHERE 0=1;