Why is CurrentDB.Execute slower than DoCmd.RunSQL?

Posted on 2005-04-07
Last Modified: 2008-01-09
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?
Question by:will_scarlet7
    LVL 77

    Expert Comment

    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......

    LVL 15

    Author Comment

    ThanX Pete!
    LVL 77

    Accepted Solution

    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

    LVL 77

    Expert Comment

    The numbers are times in seconds.
    LVL 15

    Author Comment

    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!


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now