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?
LVL 15
will_scarlet7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
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
0
will_scarlet7Author Commented:
ThanX Pete!
0
peter57rCommented:
will_scarlet7,
I have run the following several times.

Sub docmdrunsql()

Debug.Print Timer
    CurrentDb.Execute "INSERT INTO tbl1 SELECT * FROM qryoldtbl1", dbFailOnError

DoCmd.SetWarnings False
Debug.Print Timer
   
    DoCmd.RunSQL "INSERT INTO tbl2 SELECT * FROM qryoldtbl1"
   
Debug.Print Timer
DoCmd.SetWarnings True

End Sub

Each append query adds about 200,000 records from the source to the destination.

In every test, Currentdb.execute has been quicker.  But both are still quick.  You can see a clear trend for RunSQL.
Currentdb.execute       docmd.runsql
1.55                            2.37
1.39                            2.83
1.61                            3.17
1.34                            3.52
1.55                            3.63
5 more runs then
1.34                            5.00

Pete
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peter57rCommented:
The numbers are times in seconds.
0
will_scarlet7Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.