SQL - VBA once again

  • Thread starter Thread starter Jeanne
  • Start date Start date
J

Jeanne

Preface: I use RunSQL successfully with SQL Select statements in many
projects. So, it’s doable.
I don’t do this for a living, am Access db for Dummies user. Where I copied
the code, and up till now it worked.
Dan, if you’re reading this, tried your link, it didn’t work.
Also, you may know more elegant solutions but I’d appreciate if I could get
feedback on the below.

Receive RunTime error ‘2342’
A RunSQL action requires an argument consisting on an SQL statement.

My “References†in Tools include “Microsoft ActiveX DataObjects 2.1 Library.

I have this same code running in other applications – no problem.
In this same application I have an SQL Select statement running using this
same Recordset definition -- no problem

Dim cnn2 As ADODB.Connection
Set cnn2 = CurrentProject.Connection
Dim RcdSet2 As New ADODB.Recordset
RcdSet2.ActiveConnection = cnn2

Dim sSQL As String

sSQL = "SELECT COA_Checking.* FROM COA_Checking"
DoCmd.RunSQL sSQL

I’m thinking there is something up with my Project set up.
 
On Fri, 16 Oct 2009 20:25:01 -0700, Jeanne

From the help file on RunSQL:
You can use the RunSQL action to run a Microsoft Access action query
by using the corresponding SQL statement . You can also run a
data-definition query .

This means you cannot run SELECT queries using RunSQL.

-Tom.
Microsoft Access MVP
 
From the help file on RunSQL:
You can use the RunSQL action to run a Microsoft Access action
query by using the corresponding SQL statement . You can also run
a data-definition query .

This means you cannot run SELECT queries using RunSQL.
Click to expand...

The Access term "Action Query" is equivalent to "DML" in generic SQL
terminology.
 
For those of us not familiar with DML, would you mind expanding the acronym in
the context of SQL

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
David W. Fenton said:
Actually, to me, that article is wrong. SELECT INTO might be
considered DML, but I can't see how plain SELECT can be.
Click to expand...

The article seems to be agreeing with you, David.

"The purely read-only SELECT query statement is classed with the 'SQL-data'
statements and so is considered by the standard to be outside of DML."
In any event, defining SELECT INTO as DML would also be consistent
with Access terminology.
Click to expand...

SELECT... INTO certainly strikes me as an Action query: it's a Make-Table
query.
 
The article seems to be agreeing with you, David.

"The purely read-only SELECT query statement is classed with the
'SQL-data' statements and so is considered by the standard to be
outside of DML."
Click to expand...

But the article equivocates in other places, allowing as how not
everyone agrees. I think there are people who are right and there
are people who are wrong. The ones who say SELECT queries are DML
are just wrong, as it contradicts the meaning of the middle letter
of DML, i.e., MODIFICATION.
SELECT... INTO certainly strikes me as an Action query: it's a
Make-Table query.
Click to expand...

I just hate it when everybody agrees with everyone else -- it just
ain't no fun at all.
 
David said:
But the article equivocates in other places, allowing as how not
everyone agrees. I think there are people who are right and there
are people who are wrong. The ones who say SELECT queries are DML
are just wrong, as it contradicts the meaning of the middle letter
of DML, i.e., MODIFICATION.
Click to expand...

I'm not sure how that is a bad thing. I had read it as noting what was
actually in practice, even though it was incorrect. Sure, we could omit
the incorrect but widespread definition, but that would only mean the
users would get confused when they find documents stating to the
contrary. Best to tell them straight up that several people has the
definition incorrect, I'd think.
 
Back
Top