asked on

docmd.openquery, CurrentDb.Execute- which to use?

Inside a module I have the following code run:

docmd.set warnings false
docmd.open query "qryapp_tele"
docmd.set warnings true

I know that isn't the best way to do it. I have seen something similar to:
CurrentDb.Execute "INSERT INTO TableName....etc..

Can someone give me some advice on how to do this the best way? Here is the SQL behind my qryapp_tele query. You will see that it does have some form references.

INSERT INTO tbldates_req ( requestphase_id, dated_event_id, event_date, comments_text, event_date_user, event_date_created )
SELECT Val([Forms]![frmrequest]![sfrmrequestphase].[Form]![txtrequestphase_id]) AS requestphase_id, 99 AS dated_event_id, Date() AS event_date, [Forms]![frmrequest_orders]![sfrmorder_req].[Form]![Order subform].[Form]![FFName] & "-" & [Forms]![frmrequest_orders]![sfrmorder_req].[Form]![Order subform].[Form]![FFEmail] AS comments_text, DLookUp("UserID","tblcurrentuser") AS event_date_user, Date() AS event_date_created
FROM tbldates_req
GROUP BY Val([Forms]![frmrequest]![sfrmrequestphase].[Form]![txtrequestphase_id]), 99, Date(), [Forms]![frmrequest_orders]![sfrmorder_req].[Form]![Order subform].[Form]![FFName] & "-" & [Forms]![frmrequest_orders]![sfrmorder_req].[Form]![Order subform].[Form]![FFEmail], DLookUp("UserID","tblcurrentuser"), Date();

THANKS!

tbsgadi

Hi snyperj,

My advice...If it works leave it.
Unless you have time to spare.
Some people prefer to write all their SQL Code in modules, which is better code practice.
If you are a relative "Newbie" life is a lot easier if you leave it in a query.

Hope this helps,
Gary

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

Leigh Purvis

membership

This solution is only available to members.

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

Start Free Trial

snyperj

ASKER

I would like to try that. What is the proper syntax?
If I currently have

Private Sub objOLMail_Send(Cancel As Boolean)

blnSentFlag = True
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryapp_tele"
DoCmd.SetWarnings True

End Sub

Would it just be changed to:

Private Sub objOLMail_Send(Cancel As Boolean)

blnSentFlag = True
INSERT INTO tbldates_req ( requestphase_id, dated_event_id, event_date, comments_text, event_date_user, event_date_created )
Values (Eval("Val([Forms]![frmrequest]![sfrmrequestphase].[Form]![txtrequestphase_id])"), 99, Date(), Eval("[Forms]![frmrequest_orders]![sfrmorder_req].[Form]![Order subform].[Form]![FFName] & ""-"" & [Forms]![frmrequest_orders]![sfrmorder_req].[Form]![Order subform].[Form]![FFEmail]"), DLookUp("UserID","tblcurrentuser") , Date())

End Sub

something tells me I need some semi colons or something somewhere!!!

Arthur_Wood

Thanks angelIII. Every time I tried to submit a comment, I got an error page saying Page Not Found - it would seem that the submission had been recorded, and the error was on the post of the refreshed page back to my browser window - go figure. But thanks for the housecleaning.

AW

SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

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

Start Free Trial

Leigh Purvis

Oh Jet can optimize the code SQL statements.
Indeed it has to before it executes it. (Jet will decide upon the optimum order of Joins etc - if it can use an index etc etc).
That's the problem.
The execution plan to do so isn't stored. *That's* where saved queries pick up their advantage. The saved plan.

However it isn't necessarily an advantage.
The saved query plans remain with the query until they're recompiled.
If your database changes over time (all do - we're talking sheer numbers of records here) then what made a good plan on day one could become less efficient for a much larger number of records.
There is an argument made by some that on the fly SQL statements have the advantage for this very reason!
I'm not necessarily a proponent of that - but acknowledge that they're not the end of the world either.

Using CurrentDb.Execute (or the ADO equivalent CurrentProject.Connection.Execute) is commonly heralded as preferable because there's no need to worry about SetWarnings.
They should also be slightly more efficient.
If you do use it - include setting it back on in your procedure's Exit label section (so it gets fired after an error too!).

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Leigh:

"The saved query plans remain with the query until they're recompiled."
This happens everytime the mdb is Compacted & Repaired.

mx

Leigh Purvis

Yes I'm aware of that.