Question about "Quotes (') and Double-Quotes (") - Where and When to use them"

[thelonelyghost]

DoCmd.RunSQL strSQL

I thought there were some restrictions on what types of SQL you could run with this command. Source

** Admin Edit **
This thread pertains to the article Quotes (') and Double-Quotes (") - Where and When to use them.

[NeoPa]

That seems to be saying you cannot use it to execute a QueryDef. QueryDefs are not types of SQL. They are database objects that contain SQL.

I'm not sure I've even understood your point to be fair, so excuse me if my reply makes little sense. Perhaps you could clarify your point.

[thelonelyghost]

Apologies, allow me to clarify. First paragraph under RunSQL:

"RunSQL is a method of the DoCmd object in Microsoft Access. It is designed for DML SQL, such as UPDATE, INSERT and DELETE statements. You cannot "execute" a SELECT query so the RunSQL method will fail if you attempt to pass a select statement to it."

-- Lesandrini, Danny. "Executing SQL Statements in VBA Code." Published 20 May 2005. DatabaseJournal.com

Is this just blatantly wrong or was there a mistake/misunderstanding along the way?

[NeoPa]

No mistake. DoCmd.RunSQL can only be used for action queries. I'm wondering where you feel the article implied otherwise. The last code snippet is an example of using SQL only. It is not intended to imply DoCmd.RunSQL specifically need be used, simply that you should do your thing with the SQL after the SQL string itself has been displayed to the Immediate Pane. Is that what the question was about? The last line?

By the way, as this is a question relative to the article rather than additions to the article itself, I'll split it into a separate Question thread (Question about "Quotes (') and Double-Quotes (") - Where and When to use them").

[thelonelyghost]

I guess I felt that the article gave a faulty example. As a reminder you posted these two, which the DoCmd.RunSQL command could reference either of them.

strSQL = "SELECT * FROM [TableName] WHERE ([AccountName]='Hieronymous')" 

strSQL = "SELECT *" & VbCrLf & _ 
         "FROM [TableName]" & VbCrLf & _ 
         "WHERE ([AccountName]='" & Me.cboAccount & "')" 

My original comment was mostly to point out that DoCmd.RunSQL has the restriction of not being able to use SELECT statements. It seemed that you were using the above strings as examples to put into DoCmd.RunSQL and I guess I was politely asking if you knew its limitations (to action queries) when writing the article.

[NeoPa]

That's a good point. The reasoning behind it is that I use SELECT queries in the example SQL as these are probably the most recognisable and common SQL for most people. The least threatening if you like. It's not about the actual SQL used, but the concepts of how they are built with reference to string literals within them.

Unfortunately, SELECT queries are the least simple to execute. They are either used as the Record Source of some object or other, or saved into a QueryDef (another object of course). My intention was to avoid drawing the attention away from the concept and onto whatever is required for a particular object. Clearly in your case this has back-fired somewhat. I find such a level of attention to detail on your part quite heartening. Signs of a good approach.

I will have to see if I can find a way of ensuring no-one else considers this juxtaposition of ill-fitting examples to be a problem.

Having looked at it more deeply, I can see it runs through the article more fundamentally than I'd thought at first look. Check it out now. I hope that is fixed consistently. It was a good point to raise by the way. Reading through it again I wasn't happy with it after you pointed that out.

[thelonelyghost]

Much better now. All I really wanted was a small note like that or a link to more info on how to use DoCmd.RunSQL so as not to distract from the thesis of the article. Les changes sont parfait, merci!

P.S. If you can't tell I use french when I'm happy about something. I really hope this doesn't bend the typical English-only rule too far...

[NeoPa]

I don't think they're bent too far by your French, and I'm pleased that's resulted in a clearer, easier to understand, article. Thanks for your input :)