SQL - VBA once again

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

 

[Jeanne]

Forget it, I was doing something really dumb. thanks.

 

[Allen Browne]

RunSQL expects an action query, e.g. UPDATE or DELETE or INSERT, not SELECT.

A RunSQL example might be:
sSql = "DELETE FROM Table1 WHERE ID = 99;"
DoCmd.RunSQL sSQL
(None of the rest is needed.)

If you do need to open a recordset using ADO, here's an example:
http://allenbrowne.com/func-ADO.html#AdoRecordsetExample

The native library in Access is DAO, so I would personally prefer to use
that approach for native Access tables. Example:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

 

[Tom van Stiphout]

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

 

[David W. Fenton]

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.

The Access term "Action Query" is equivalent to "DML" in generic SQL
terminology.

 

[John Spencer]

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

 

[Douglas J. Steele]

Data Manipulation Language (as opposed to DDL - Data Definition Language or
DCL - Data Control Language)

Wikipedia is actually reasonable on this:
http://en.wikipedia.org/wiki/Data_Manipulation_Language

 

[David W. Fenton]

Wikipedia is actually reasonable on this:
http://en.wikipedia.org/wiki/Data_Manipulation_Language

Actually, to me, that article is wrong. SELECT INTO might be
considered DML, but I can't see how plain SELECT can be.

In any event, defining SELECT INTO as DML would also be consistent
with Access terminology.

 

[Douglas J. Steele]

Actually, to me, that article is wrong. SELECT INTO might be
considered DML, but I can't see how plain SELECT can be.

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.

SELECT... INTO certainly strikes me as an Action query: it's a Make-Table
query.

 

[David W. Fenton]

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

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.

I just hate it when everybody agrees with everyone else -- it just
ain't no fun at all.

 

[Banana]

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.

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.