• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1491
  • Last Modified:

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?
0
will_scarlet7
Asked:
will_scarlet7
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now