Open select query in vba code

[KayC]

Hi
I am running Acess2000.
I am trying to replicate the docmd.openquery ("qrs_TEST") in sql
I have tried using docmd.runsql(strSQL) but I keep getting the Run-time
error message..
... "A RunSQL action requires an argument consisting of an SQL
statement"
I am assuming this is because the SQL I am using is a select statement
rather than an action.
Is there a way to open a query through SQL?
Regards
Kay

 

[Guest]

It is because the RunSql method required an Action query (Update, Append,
Make Table, Delete). It can be either a stored query name or a valid SQL
statement.

It is alway better to use the Execute method for Action queries; however,
because it is much faster. It is faster because it does not have to be
processed by the Access UI. It goes directly to Jet. The gottcha is you
have to use the dbFailOnError argument to trap errors or you will not know an
error occured. The syntax is:

CurrentDb.Execute(strSQL), dbFailOnError

The OpenQuery method; however, only works with a stored query name.

If you are having to create a select query on the fly and want it to open
the results of the query like you would with the OpenQuery, See VBA Help for
the CreateQueryDef method. The example shows a couple of ways to do that.

 

[Douglas J Steele]

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
Set qdfCurr = CurrentDb.CreateQueryDef("qryTemp", strSQL)
DoCmd.OpenQuery "qryTemp"
dbCurr.QueryDefs.Delete "qryTemp"

 

[OfficeDev18 via AccessMonster.com]

Dim RecSetObj As Recordset 'or DAO.Recordset if you need to

Set RecSetObj =CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

With RecSetObj
.Move.......

 

[KayC]

Thanks guys for steering me the right way!