Can't Open Temporary Querydef Object

[Dor100]

How do you open the results of a sql select statement without creating a permanent query object? Here's the basics of what I've been trying to do in a VBA module:

--------------------------------------
Dim Q as querydef

Set Q = CurrentDb.CreateQueryDef("", "select * from Table order by lastname, firstname")
--------------------------------------

I'm trying to get this sql statement to "open" as if one was opening a preexisting query. So far the most I've gotten is an error statement about being unable to "find" the temporary querydef. Thanks in advance to anyone who has the solution.

 

[RoyVidar]

You should be able to open a recordset based on it, but not as if it were a permanent querydef, try

[tt]set rs = Q.openrecordset()[/tt]

- but in stead of messing with this, why not just open a recordset based on the dynamic SQL?

- btw - one reason for error, could be that when using CurrentDB to create the temporary querydef, the querydef might loose the reference to the current database...

[tt]dim db as dao.database
dim rs as dao.recordset
set db=currentdb
set q = db.createquerydef.....
set rs = q.openrecordset()[/tt]

Roy-Vidar

 

[Dor100]

Thanks, Roy. How do I get the results of these items to open up on the screen and look as if one were opening a query or table, but without leaving a permanent object behind?

 

[Dor100]

Anyone know how to do this, or if it's not possible?

 

[Dor100]

Just when you thought I might have given up : after a weekend of thinking about it, is there anyone out there who knows how to simply display the results of a sql statement to users on the fly without having to create a permanent object such as a table or query, and without having to go through the process of creating such objects and then dropping them? Or can anyone say definitively that's it's simply not possible? It would be gravy on top if it were updatable as well. Thanks...

 

[RoyVidar]

Assign the sql string as rowsource for a listbox control - if the result can be a dynamic number of columns, you may have to do some programattic alterations on some properties of it...

Roy-Vidar

 

[Dor100]

Thanks. Interesting idea, but what I'm really chasing after is to have the data "open" automatically on the screen looking as if one had opened a table or a query. Doesn't it seem intuitive to you that this should be routinely possible; e.g., "if such and such happens, display the results of 'select table left join yada, yada, yada order by this, that, and the other thing'"?

 

[Dor100]

Well, for the time being I'm just going to live with having more queries than I would prefer and setting their sql on the fly. It's less time consuming than also recreating and dropping them over and over. I have a feeling what I'm looking for here may not be possible, at least with XP, and that only action queries get to have quick one-liners. It's good to have that.