asked on

Why is CurrentDB.Execute slower than DoCmd.RunSQL?

This is not really a need but something that is puzzling me and so I thought to as you Experts for help in understanding...

I've built a function that needs to import records from a SQL database to a local table in Access (it is for outputting field values to RTF which I have experienced instability issues when exporting straight from the linked SQL table). It is still in the development stage so I am just implementing some aspects, but when I put in the import query I used "CurrentDB.Execute" as I thought that was more proper than turning off warnings and "DoCmd.RunSQL". However....

This command:
CurrentDB.Execute "INSERT INTO tbl_CSData SELECT qry_CSData2.* FROM qry_CSData2;"

takes nearly 45 seconds to run as opposed to approximatly 3 or 4 seconds for:
DoCmd.RunSQL "INSERT INTO tbl_CSData SELECT qry_CSData2.* FROM qry_CSData2;"

Is that a built in issue with the CurrentDB command?

peter57r

Hi will_scarlet7,
I would say that is bizarre result.
I thought it was a fundamental truth that DoCmd.RunSQL is slower than CurrentDB.Execute.

I might have a play......

Pete

will_scarlet7

ASKER

ThanX Pete!

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peter57r

The numbers are times in seconds.

will_scarlet7

ASKER

I suppose then that it must be something to do with the calculated values in my "qry_CSData2" query that it is using as the record source to append from. I've had some trouble with certain criteria making the query run slow, so perhaps there is something about the calculations that affects CurrentDB but not RunSQL...

ThanX for the input!

Sam