Do a RunSQL in memory ... without table ?

[ksor]

I have made an SQL-string from a query and I have to change the criterias for different fields from user input in a form.

I can't just - in the query - reference the criterias directly in the form because the way they are given there ... I have to manipulate them into a string and incorporate that "criteria string" in the SQL-string I got from the query ... I think.

The resulting "recordset" from running the "total" SQL string I have to run through and generate some shapes in Excel ... I know how to do that !

My problem is how to "hold" the dataset ... sort of "in memory" while I'm running through the recordset !

... do I really NEED to have a table to hold the dataset ?

 

[The_Doc_Man]

... do I really NEED to have a table to hold the dataset ?

If you are using SQL, then yes. The reason is that Access is really an interfacing tool that (among other things) talks to a database engine. Older versions use JET; newer versions use ACE. Whichever one you have, it is part of the Access bundle but you don't usually see it. The problem with your question is that JET or ACE exists in its own separate memory environment and things in the memory of the MSACCESS.EXE task cannot be seen by JET/ACE randomly reading the memory of the interfacing tool. The actual data flow is that Access feeds the engines and they feed back information to your tables. Because, you see, that is the only point of common contact.

Typically, if I read the question correctly, what you do is create a multi-part SQL string in your code. Then based on user selections, you might change the WHERE clause to isolate what you wanted to see. You "re-assemble" the query from its parts, where typically the SELECT... and FROM... segments are constant but the WHERE... segment is dynamic.

Once you have the rebuilt SQL string, you can execute it.

If my answer does not seem directly responsive, then you need to describe your end goal better, because I answered the question as I saw it. But your description is not totally clear and I had to make an inference or two.

 

[ksor]

I have it to work now ... but I think the table is not needed ... but I'm wrong :banghead:

I'll have to live with that, I think

 

[ksor]

If you save the querydef after you have built it, Excel can run the querydef so you don't have to write the resultset to a table.

OK, I'll look into that - thx !