Using Dynamic Query

General Computing Coding and Design
1

How do I create a recordset, from a query, hold it in memory, and then run another query using the one held in memory?

confused? I’ll try to explain.

I have a database, in the database is a bit of code that builds a query

Set db = CurrentDb
sqlstr = "SELECT * FROM [user query] WHERE [shift] IN(" & Shift_Criteria & ");"
Set Q = db.QueryDefs("Query_Results")
Q.SQL = sqlstr
Q.Close

that query is then used to form the basis for another number of queries. it works great,

but (you knew there was one comming)

inbetween me running the code to generate the first query and running any of the subsequent queries someone else runs the code for the first query again generating a diferent query (Shift_Criteria changes) and a diferent set of information. All my subsiquent queries run against the new data, not my data.

How can I stop this from happening?

Someone must be able to help me.

2

Blatant Google … But I think its helpful to you…

hopefully the following code you’ll pick out the bit that populates the array and you can twiddle to suit your needs…


Dim rsR As DAO.Recordset
Dim Results As Variant

Set rsR = CurrentDB.OpenRecordset "SELECT * FROM MyTable"

'Get accurate record count
rsR.MoveLast
rsR.MoveFirst

'Maybe check here whether there is an unreasonably large number of rows

'Populate the variant with a 2-dimensional array containing the results
Results = rsR.GetRows(rsR.RecordCount)

rsR.Close