how to open a Select query using SQL string?

[Jerry Qu]

Hi ALL,

Is there any way to run a SQL "select statement" to display the result in a
new datasheet like

DoCmd.opentable and DoCmd.openquery doing. the docmd.runsql only work for
action sql statement


TIA

Jerry

 

[George Nicholson]

AFAIK, OpenQuery is the only way to display a query result (other than
of assigning a query name or SQL string to the Recordsource of an existing
form/report). Since OpenQuery only accepts existing query names as
arguments, you need to create a named query from your SQL string. Something
like:

strSQL = "SELECT * FROM YadaYada"
strQueryName = "MyTempQuery"

Set db = CurrentDb
Set qdf = db.CreateQueryDef(strQueryName, strSQL)
DoCmd.OpenQuery strQueryName, acViewNormal, acReadOnly

Do Until IsLoaded(strQueryName, acQuery) Eqv False
DoEvents
Loop

db.QueryDefs.Delete strQueryName
Set qdf = Nothing
Set db = Nothing

IsLoaded is a pretty standard function, a version of which can be found in
Northwind.mdb
--
HTH,

George Nicholson


(Please post responses to newsgroup but remove "Junk" from return address if
used)

 

[Mike Labosh]

Is there any way to run a SQL "select statement" to display the result in
a

new datasheet like

DoCmd.opentable and DoCmd.openquery doing. the docmd.runsql only work for
action sql statement

This should work, but it will leave the query in the database window.

[Air Code:]

Dim db As DAO.Database
Dim qd As New DAO.QueryDef

Set db = CurrentDB

With qd
.Name = "qselSomeName"
.SQL = "select statement"
End With

db.QueryDefs.Append qd

DoCmd.OpenQuery "qselSomeName"

Set qd = Nothing
Set db = Nothing

--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~