Code vs Query

[mte17]

I have a question that I need an answer: Why use code to manipulate data in tables (i.e., recordsets...) vs using queries or sql? I see a lot of sample code involving recordsets and moving between records but can't the same be accomplished using queries? Thanks in advance.

 

[Fornatian]

Where possible use a query rather than code to perform a task, the reason is that the database engine pre-optimises a query, a coded query however has to be read from the code step by step so the engine does not know the quickest way to run the query until it has done so. This occurs each time the code is run.

In some instances however it is required to use recordsets instead of queries, where for instance the query is built dynamically from user details in a form whose recordsource changes dynamically. This a trade-off between the query speed and the no of stored querydefs that would be required for say a form that can reflect 10 tables data. Would you rather build say 50 queries OR build some dynamic SQL which reflects the 50 queries which may be run.

I do agree that the use of code instead of the provided method is often to the detriment of the program.

 

[raskew]

If the solution requires looping through a record-set, code is the only way to go (there's no looping mechanism applicable to queries).

In some other situations where a query solution could apply, it's just sometimes easier and more understandable to use a few lines of code rather than to craft a series of complex queries.