I am currently in process of upsizing to SQL backend for an access DB , but in the mean time I 'm trying to make the Access frontend as compatible as possible.
When navigating the DB I have a 'Find' form to find records on various forms and at present open them with a filter to the specific record in the OpenForm method. All of the secondary ('Detail') forms have query recordsources. Would it be more efficient (and make upsizing easier) to have the SQL recordsource for the forms set on the fly when movng from the Find form? I have tried without success to find out what is best practice.
when we filter a form for a specific set of records, ms access has to load the whole recordset to memory, go thru every single record and show us the subset (filtered records). This will not be a performance problem if the database & client are on the same machine.
when database & client are on 2 separate machines and linked by network, filtering a form requires the whole recordset transfered over the network from database server to client machine while using SQL statement, the query is run by database server and only the subset of records is traveling along the wire to client machine.
in short, in client/ server environment (the one you'd like to upsize) the recommendation is SQL statements and not filtering