Execute SQL SELECT statement with VBA code

[Bill Morgan]

Access provides a simple way to run a SQL Action Query
using VBA code. For example:

DoCmd.RunSQL "DELETE Customers.* FROM Customers".

QUESTION: Using VBA code and a SQL statement sting only
(i.e., without referring to a stored query) is there an
equally simple way to execute and view a SELECT query in
table format?

I am trying to avoid complex code (i.e., without creating
a New ADODB.Command object).

Thanks for any help you can provide.

Bill Morgan

 

[Ken Snell]

Did you try
DoCmd.OpenQuery

 

[Randy Wayne]

Below is a method with minimal code


Dim strSQL as String
Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection
strSQL = "DELETE Customers.* FROM Customers"

Execute.sql


cnn = Nothing

 

[Ken Snell]

Oops...sorry. This won't work.

What you could do is to create a QueryDef and then run it using the
DoCmd.OpenQuery action. Note that this will add the new query to your
queries in the database; if you don't want it when you're done, delete it
from the QueryDefs collection.


Dim strSQL As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDefs

strSQL = "Select * From TableName"
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("NewQueryName", strSQL)

DoCmd.OpenQuery qdf.Name

dbs.Close
Set qdf = Nothing
Set dbs = Nothing

 

[Guest]

Ken,

Thanks for your input. I think your DAO solution is
probably the simplest way to go. I have no problem with
deleting the query with code, once it's created. Thanks
again. Bill

 

[Guest]

Randy,

If I change your SQL statement to a SELECT statement, and
I change your line #5 to:

cnn.Execute strSQL

and I change your last line to:

Set cnn = nothing

I do not get an error, but there is no display of the
recordset. What I am looking for is a result similar to
DoCmd.OpenQuery "Customers" (assuming "Customers" is a
stored, SELECT query). The difference is, I want to
reference a SELECT SQL statement in code, not a stored
query.

If you discover something that works (Access 2000, 2002,
or 2003), please write back.

Thanks for your input.

 

[Sid]

Ken
So I can understand this, the results of using something like:
DoCmd.RunSQL "SELECT Customers.* FROM Customers".
Can't Be viewed like a normal table or Query result? Or the recordset can't
be called like a normal query result?

I've been looking for advanced methods of SQL & DAO selection I can
implement in code, using variables/form control references, to apply the
resulting data to a report or form, without success.
I'm trying to use a form with four selectors(check box for no
preference(all) & combo box for choose specific) for four different data
fields. But I can't determine the correct SQL syntax to handle it. My main
problem is, if my form's checkbox control is blank(false) use combo box
value instead, then inversely, ignore a blank combo box and do a Like "*" if
my check box = True.
Is QueryDef the best way for me to go too?
Any suggestions appreciated.
Thanks
Sid

 

[Sid]

Ken
I'm also having a problem & trying to use CreateQueryDef, But I'm very
close.
I tried your example, and it Does create the query, but keep receiving a
"Type mismatch, Run-time error '13'" on the line I have indicated. Mine
looks like ths:

Dim strSQL As String
Dim dbs As Database ' Was Dim dbs As DAO.Database - Didn't matter
Dim qdf As QueryDef 'Was Dim qdf As DAO.QueryDef - Didn't matter

strSQL = "Select * From tblEventLog"
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("qryTempRptQry", strSQL) '<----TYPE MISMATCH
DoCmd.OpenQuery "qryTempRptQry", , acReadOnly 'it didn't like the
qdf.Name...
Set qdf = Nothing
Set dbs = Nothing
End Sub

Any suggestions appreciated.
Sid

 

[Sid]

Ken I found my problem. After I Placed () behind the CurrentDB like
Set dbs = CurrentDB() , all worked perfect.
I happened to find an example at
http://www.mvps.org/access/queries/qry0003.htm
Thanks

 

[Ken Snell]

Easiest answer for me to have given all day < grin! >. Glad you found the
solution!

 

[Sid]

Thanks Ken
But one more question for the Guru.
I'm trying to set a string of text(for an SQL) equal to a variable, to
ultimately compose the SQL for my QueryDef. BUT, a string variable will not
accept a data in even a simple SQL line, including quotes, parentheses,
asterisks, single quotes, and all that you find in a SQL. My goal is to
create 4 different variables based on selections for from a form's check &
combo boxes, then I'll format and concatenate the string to create my
QueryDef lines.
A very appreciative Access student.
Sid

 

[Michel Walsh]

Hi,




Dim rst As ADODB.Recordset
Set rst=xnn.Execute("SELECT * FROM somwhere WHERE somethingOccur")




with xnn an open ADO connection. The recordset is read only, forward only,
with that simple syntax, but that should cover 80% of all practical cases.
For the other 20%, try




Dim uvw As ADODB.Recordset ' add With Events, if required
Set uvw=New ADODB.Recordset
uvw.Open "SELECT ...", xnn.Connection, adOpenKeyset, adLockOptimistic,
Options:= adCmdText




Hoping it may help,
Vanderghast, Access MVP

 

[Ken Snell]

Post example of what you're trying to do. It's definitely possible to do
what you want to do, but you may be running into problem of needing to embed
" characters inside the string? If that is it, be sure to double up the "
characters ("") in order to have a single " character be part of the text
string.

 

[Sid]

Ken
I have learned a bit since my posting yesterday. I was trying to display a
string variable (in a form text box for a test)with a string of SQL
characters, couldn't. But now I know the SQL syntax is super critical. I
managed to make the following work:
-----
Private Sub Command37_Click() ' SQL Creator Test
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
'DoCmd.DeleteObject acQuery, "qryTempRptQry"
strSQL = "SELECT * FROM qryReportLog1"
'Works by itself
'strSQL = strSQL & " WHERE Label = " & """" & Me!SelectPnt & """"
'Works by itself
'strSQL = strSQL & " WHERE Group = " & """" & Me!SelectGrp & """" 'WON'T
work
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("qryTempRptQry", strSQL)
DoCmd.OpenQuery "qryTempRptQry", , acReadOnly
Set qdf = Nothing
Set dbs = Nothing
------
The Me!SelectPnt & Me!SelectGrp are combo boxes that select choices like
"Room 103", and "Physical Plant" respectively -- I'm totally lost as to why
the SelectPnt one works and the second one won't. Now I'm trying to get
some code working that gives the prefix & suffix for a given input at:
http://www.mvps.org/access/forms/frm0001.htm The "Dim rst As
ADODB.Recordset" gives a 'User defined type, not defined' error when
compiled. The tangents never end... Thanks.

Sid

 

[Michael Wong]

Hi Sid,

I'm not quite sure, but you can try this:

strSQL = strSQL & " WHERE Label = " & """" & Me!SelectPnt & """"
strSQL = strSQL & " AND Group = " & """" & Me!SelectGrp & """"

In the WHERE statement, you can have a combination of different conditions,
all linked by eithere AND or OR. I put here AND for an example, but you can
also have a OR in place, depending of how you want to select the data. Use
AND when you want both conditions to be true at the same time, OR for either
one of them to be true.

For the "Dim rst As ADODB.Recordset" to work, you have to reference ADO in
you vba module.

Hope this might help

 

[Sid]

Michael
Would that have been the reference: Microsoft ADO Ext. 2.1 for DDL and
Security ?
I selected it, but I still get the compile error.

My SQL problem is running those strings separately, (in response to
selections on my project form), but my test was just trying each
individually. The Me!SelectPnt one worked, while the Me!SelectGrp has never
worked. This is awful, there must be a non-experimental syntax I can obtain
for SQL.
Thanks

 

[Sid]

Amazing!
I found my problem Ken &/or Michael.
The word "Group"(my field name) in my SQL caused the problem. I named it
Groups & it works!
Thanks

 

[Ken Snell]

The reason Group doesn't work is because it's a "reserved word" in
ACCESS....so ACCESS thinks that you're calling that special use of the word,
not using your field name. Surround Group with [ ] in the SQL statement and
it should work ok...better thing to do though is to not use it as a field
name.

See this MS KB article for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286335

 

[Sid]

Thanks Ken
I just changed the field name in the data table to Groups.
Everything is working amazingly well now.
Thanks for your time, that was a weird one. I need to learn all SQL &
other reserved words to avoid.
Sid

The reason Group doesn't work is because it's a "reserved word" in
ACCESS....so ACCESS thinks that you're calling that special use of the word,
not using your field name. Surround Group with [ ] in the SQL statement and
it should work ok...better thing to do though is to not use it as a field
name.

See this MS KB article for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286335

--
Ken Snell
<MS ACCESS MVP>

Amazing!
I found my problem Ken &/or Michael.
The word "Group"(my field name) in my SQL caused the problem. I named it
Groups & it works!
Thanks
data.
Use ADO choices
like as
to to
get possible
to needing CreateQueryDef,
But there
an query

 

[Sid]

Thanks Ken
I just changed the field name in the data table to Groups.
Everything is working amazingly well now.
Thanks for your time, that was a weird one. I need to learn all SQL &
other reserved words to avoid.
Sid

The reason Group doesn't work is because it's a "reserved word" in
ACCESS....so ACCESS thinks that you're calling that special use of the word,
not using your field name. Surround Group with [ ] in the SQL statement and
it should work ok...better thing to do though is to not use it as a field
name.

See this MS KB article for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286335

--
Ken Snell
<MS ACCESS MVP>

Amazing!
I found my problem Ken &/or Michael.
The word "Group"(my field name) in my SQL caused the problem. I named it
Groups & it works!
Thanks
Michael
Would that have been the reference: Microsoft ADO Ext. 2.1 for DDL and
Security ?
I selected it, but I still get the compile error.

My SQL problem is running those strings separately, (in response to
selections on my project form), but my test was just trying each
individually. The Me!SelectPnt one worked, while the Me!SelectGrp has
never
worked. This is awful, there must be a non-experimental syntax I can
obtain
for SQL.
Thanks

Hi Sid,

I'm not quite sure, but you can try this:

strSQL = strSQL & " WHERE Label = " & """" & Me!SelectPnt & """"
strSQL = strSQL & " AND Group = " & """" & Me!SelectGrp & """"

In the WHERE statement, you can have a combination of different
conditions,
all linked by eithere AND or OR. I put here AND for an example,

but
you

can
also have a OR in place, depending of how you want to select the data.
Use
AND when you want both conditions to be true at the same time, OR for
either
one of them to be true.

For the "Dim rst As ADODB.Recordset" to work, you have to

reference
ADO

in
you vba module.

Hope this might help

Ken
I have learned a bit since my posting yesterday. I was trying to
display
a
string variable (in a form text box for a test)with a string of SQL
characters, couldn't. But now I know the SQL syntax is super
critical.
I
managed to make the following work:
-----
Private Sub Command37_Click() ' SQL Creator Test
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
'DoCmd.DeleteObject acQuery, "qryTempRptQry"
strSQL = "SELECT * FROM qryReportLog1"
'Works by itself
'strSQL = strSQL & " WHERE Label = " & """" & Me!SelectPnt & """"
'Works by itself
'strSQL = strSQL & " WHERE Group = " & """" & Me!SelectGrp & """"
'WON'T
work
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("qryTempRptQry", strSQL)
DoCmd.OpenQuery "qryTempRptQry", , acReadOnly
Set qdf = Nothing
Set dbs = Nothing

lost

as

trying

to input
at: double
up of
the My
goal Glad
you but
keep run
it