Which is better practice: complex SQL statements or Recordset manipulation in Access VBA?

Question

I'm doing some VBA development and I found creating SQLs quite efficient way of getting everything done (selecting and updating). But I got to this stage where my SQL statements contain complex Switches and WHERE conditions where I have another Selects to update appropriate records. Therefore, I create this SQLs and I simply run it via "CurrentDb.Execute strSQL" and it does everything fine.

The question is, why would I declare ADODB.connections etc, set recordsets, loop through it and manipulate the data one by one?

Answer 1

why would I declare ADODB.connections etc, set recordsets, loop through it and manipulate the data one by one?

No reason. If you can do it in plain SQL you can stick with it. But in MS Access even the SQL is often evaluated on the client, so from the performance perspective there is no big difference.

If you would use SQL Server Database as a backend, then that would make a difference.

Anyway, if your SQL gets too complex to understand (you will need to read it later!, won't you?), then you could really split it in smaller chains and functions.

Answer 2

I don't think I've seen a solution in Access that uses pure SQL to create a Rank or even a row number without using a VBA function.

Answer 3

When the recordsets are small and the requirements are continually evolving, then using the recordsets in VBA makes sense. I think that's particularly true when complex decisions or parsing must take place line-by-line and a form/report or two will be involved.

If the requirements are known, and the SQL works, then there's no real reason to convert.