Hi B,
For now, the following may help
(you will need to set reference to DAO library):
C) REDEFINING SQL OF STORED QUERY
-------------------------------------------------
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strQryName As String
'name of your stored query
strQryName = "MyQuery"
'create new SQL for your stored query
strSQL = "SELECT * FROM MyTable;"
'redefine query
Set qdf = CurrentDb.QueryDefs(strQryName)
qdf.SQL = strSQL
'now open your stored query with new SQL
DoCmd.OpenQuery strQryName
qdf.Close
******************************************************************
or you may use the following function to redefine a query
******************************************************************
Public Function ChangeSQL(pstrQueryName As String, pstrSQL As String)
'Redefines the SQL for the query pstrQueryName to the passed pstrSQL.
'Code provided by Duane Hookum in ng.
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQueryName)
qd.SQL = pstrSQL
qd.Close
db.Close
Set qd = Nothing
Set db = Nothing
End Function
--------------------
save above function in a module.
use in code something like:
Dim strSQL As String
Dim strQryName As String
strQryName = "qryCustomers"
strSQL = "SELECT * FROM Customers"
'change SQL for stored query
ChangeSQL strQryName, strSQL
////////////////////////////////////////////////////////////////////////////
//////////////////////
D) NOT-PREVIOUSLY-STORED TEMPORARY OR NEW QUERY
-----------------------------------------------------------------------
Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim qdfNew As DAO.QueryDef
Set dbs = CurrentDb()
' Create temporary QueryDef by using "" as name
Set qdfTemp = dbs.CreateQueryDef("", _
"SELECT * FROM Employees")
' Create permanent QueryDef.
Set qdfNew = dbs.CreateQueryDef("NewQueryDef", _
"SELECT * FROM Categories")
'Can now run new query since now has valid name
DoCmd.OpenQuery "NewQueryDef"
'If want, can delete new QueryDef
'(no need to delete temporary QueryDef)
dbs.QueryDefs.Delete qdfNew.Name
dbs.Close
***********************************************
CREATEQUERYDEF method from Access Help:
Creates a new QueryDef object in a specified Connection or Database object.
Syntax
Set querydef = object.CreateQueryDef (name, sqltext)
The CreateQueryDef method syntax has these parts.
Part Description
querydef An object variable that represents the QueryDef object you
want to create.
Object An object variable that represents an open Connection or
Database object that will contain the new QueryDef.
Name Optional. A Variant (String subtype) that uniquely names
the new QueryDef.
Sqltext Optional. A Variant (String subtype) that is an SQL
statement defining the QueryDef. If you omit this argument,
you can define the QueryDef by setting its SQL
property before or after you append it to a collection.
Remarks
In a Microsoft Jet workspace, if you provide anything other than a
zero-length string for the name when you create a QueryDef, the resulting
QueryDef object is automatically appended to the QueryDefs collection. In an
ODBCDirect workspace, QueryDef objects are always temporary.
In an ODBCDirect workspace, the sqltext argument can specify an SQL
statement or a Microsoft SQL Server stored procedure and its parameters.
If the object specified by name is already a member of the QueryDefs
collection, a run-time error occurs. You can create a temporary QueryDef by
using a zero-length string for the name argument when you execute the
CreateQueryDef method. You can also accomplish this by setting the Name
property of a newly created QueryDef to a zero-length string (""). Temporary
QueryDef objects are useful if you want to repeatedly use dynamic SQL
statements without having to create any new permanent objects in the
QueryDefs collection. You can't append a temporary QueryDef to any
collection because a zero-length string isn't a valid name for a permanent
QueryDef object. You can always set the Name and SQL properties of the newly
created QueryDef object and subsequently append the QueryDef to the
QueryDefs collection.
To run the SQL statement in a QueryDef object, use the Execute or
OpenRecordset method.
Using a QueryDef object is the preferred way to perform SQL pass-through
queries with ODBC databases.
To remove a QueryDef object from a QueryDefs collection in a Microsoft Jet
database, use the Delete method on the collection. For an ODBCDirect
database, use the Close method on the QueryDef object.
