Querydef contruction problem

[StarvinMarvin]

I've been trying to find ways to eliminate static queries from an application, attempting to use recordsets as dynamic queries to use as a table in another recordset but that doesn't work. So I found a few posts on querydefs but nothing seems to work for my purpose and I'm not getting the syntax down correctly:

Public Sub AvPool()
Dim db As DAO.Database, qdf As QueryDef
Set db = CurrentDb
Set qdf = dbcurrent.QueryDefs("AvPool")
qdf.SQL = "SELECT Emp_no FROM Requests LEFT JOIN Assignments ON Request.Emp_No = Assignments.Emp_No WHERE Assignments.Emp_No IS NULL;"
 
DoCmd.RunSQL qdf
End Sub

just trying to create a temp querydef that I use in a loop with a recordset that uses the querydef as it would a static query I've already defined.

When the subs done I need the querydef gone.

 

[SOS]

Perhaps this would be useful to you

But to sum it up, basically you want to change this line:

Set qdf = dbcurrent.QueryDefs("AvPool")

to this:

Set qdf = db.CreateQueryDef("", "SELECT Emp_no FROM Requests LEFT JOIN Assignments ON Request.Emp_No = Assignments.Emp_No WHERE Assignments.Emp_No IS NULL;")

and then you can just set it to nothing when you are done:

Set qdf = Nothing

 

[StarvinMarvin]

I did find that page while googling this but then I found something here on querydefs and got screwed up!

Changing querydefs to CreateQuerydef worked.... now I've got a static query created from the code. Thank you sir!

 

[pbaldy]

I'm curious as to your motivation here. Saved queries are compiled and will execute faster. If you're just trying to keep the db smaller, any gains are probably offset by performance losses.

 

[StarvinMarvin]

well besides just trying to learn more, it just looks cleaner to me to code this and then get rid of it. I know I mentioned before I'd like to keep this app readable by others who may have to mess with it after me, but in the event the next guy knows less than me I don't want to make it too easy Also, in the current incarnation of this thing I needed 2 queries that compare Emp_No's in two different assignment tables because I have to run the Assign() function twice, then I shove the records from the second run back into the first assignments table. So I had AvPool and AvPool2. I figure this way I could create one temp query, kill it, then start the second pass with the same querydef and get rid of it after I'm done.

Question 1 of 2 tho:

This appears to actually create a saved query that shows up after closing the database just like an actual saved query... does that mean that when I create it it's compiled like a saved query would be until I'm done using it?

#2, I need to delete this query when I'm done and tried DoCmd.DeleteObject acQuery, AvPool to drop it but that ain't workin'.

 

[SOS]

If you do exactly what I said in the first place, using the

""

instead of the name, it won't save a query to your database and then you can simply set the variable to NOTHING and it will go away. No deletion necessary.

 

[SOS]

To reiterate:

Set qdf = db.CreateQueryDef("", "SELECT Emp_no FROM Requests LEFT JOIN Assignments ON Request.Emp_No = Assignments.Emp_No WHERE Assignments.Emp_No IS NULL;")

and then you can just set it to nothing when you are done:

Set qdf = Nothing

 

[StarvinMarvin]

how do I refer to it in a recordset then? At the moment I refer to it as AvPool, can I make reference to it using qdf in the recordset?

Using this in a loop BTW.... at the time I didn't know that "" created a temp querydef vs giving it a name.... reread the msdn reference and saw that.... doesn't mention how to refer to the query tho...

 

[SOS]

Okay, I think we're running around and around in circles here.

If you are just using this to make a query to use for a recordset you just need to create a recordset which uses the SQL for this query as the basis of the recordset.

Sorry to do this to you but I'm starting to wonder if I'm not explaining myself well enough or vice-versa. What is it you are really trying to accomplish? You want a query to do what? You want a recordset to do what? You have two items and how do they supposedly relate?

 

[SOS]

By the way, you can delete your querydef if you use the saved type by using

db.QueryDefs.Delete("AvPool")

 

[StarvinMarvin]

ko... the query has to dynamically change every time I look at it after I've inserted records into a table that the query looks at. If I insert a person's empno into "assignments" table, they won't appear the next time I look at AvPool. So, I open a recordset based on the contents of AvPool and take the TOP 2 records and insert into "assignments". Next loop around AvPool won't include those TOP 2 I just inserted so the next TOP 2 are found and inserted into "assignments".

Originally I was just using a permanent query to do this and it worked find. I then considered getting rid of the permanent query and opening up a temp query and using it just like the permanent one while I'm looping, then kill it when I'm done.

I can post the entire code if that would give a bigger picture of what's going on.


lemme try your last reply... I think that's what I'm looking for!

 

[StarvinMarvin]

db.QueryDefs.Delete ("AvPool") give me this error:

Object variable or With block variable not set

using this - Set qdf = db.CreateQueryDef("AvPool", to create the query which works.



*edit..... well that error was my bad once again, I put it after I set db to nothing. Works like a champ now!

 

[SOS]

*edit..... well that error was my bad once again, I put it after I set db to nothing. Works like a champ now!

Glad you figured it out.

 

[Banana]

Just to drop my 2 bits in....

1) I almost never bother with CreateQueryDef, and especially not for temporary querydef. Creating them will cause the database to bloat and you would have to compact & repair more frequently.

2) It's mostly needless because you can use SQL directly to open a recordset:

Set rs = db.OpenRecordset("SELECT 1;", dbOpenSnapshot)

3) It's usually easier to manipulate SQL either by using parameter queries or a string. Unfortunately, parameter queries, though great in conception suffers from performance issues so string that's subsequently used in a OpenRecordset method tends to be preferred though it's relatively more messy compared to parameter queries. At least you can write functions to abstract away the ugly parsing/editing parts.

4) Just because you can, doesn't mean you should. Simpler is almost always better, IMHO.

 

[StarvinMarvin]

that's the first thing I tried to do, but then I found I can't open the recordset and refer to it like a table or query in a subsequent recordset... so I had to create a temp query so I could reference it.

I have 5 books on access, SQL and vba and not one of them mentions that you can't open a recordset and reference it like a table/query in another recordset.