DoCmd.OpenQuery or DoCmd.RunSQL

Redesigning parts of my employers database I come across subroutines wrote by who ever created it originally that go something like this:

DoCmd.OpenQuery "qry1"
DoCmd.OpenQuery "qry2"
DoCmd.OpenQuery "qry3"
DoCmd.OpenQuery "qry4"
DoCmd.OpenQuery "qry5"
DoCmd.OpenQuery "qry6"
DoCmd.OpenQuery "qry7"
etc...
etc.....
etc.......

I have always used DoCmd.RunSQL for running queries behind events etc, mainly because the SQL is then all in front of me in one place within the VBA editor.

Currently some procedures have up to 50 OpenQuery dealing with tables with 250000+ records. Having a brief look through the queries it seems that many of them can be combined as they do similar things with the same data sets, it appears to me whoever wrote them didn't have a good understanding of SQL.

My question is which method is better in terms of performance etc. DoCmd.OpenQuery or DoCmd.RunSQL

1 Answer

If these are action queries (UPDATE or DELETE or INSERT, rather than just SELECT), it really doesn't matter. (There might be some minuscule overhead where Access is trying to collect output records to display in a grid.)

If they are not action queries, then DoCmd.RunSQL wouldn't do anything anyhow.

Also, consider using DoCmd.Execute rather than DoCmd.RunSQL, since Execute can take a saved query name instead of just raw SQL.