Where is it safe to delete the querydef?

[MLH]

Consider the following...

With MyDB
Set qdfA = .CreateQueryDef("NewQry", "Select * From tblData Where_
[Field]=True;")
Set rstA = .OpenRecordset(qdfA, dbOpenSnapshot)
With rstA
.MoveFirst
Do Until rstA.EOF
...
Loop
.Close
End With
..QueryDefs.Delete qdfA.Name
..Close
End With

Should the deletion of the NewQry querydef object occur before or
after the last .Close?

__________________________________________________ ___
notes: Look for .QueryDefs.Delete qdfMarkedAddnlOwnerRecs.Name
in Sub cmdSaveVehicleRec_Click()
on frmVehicleEntryForm
__________________________________________________ ___

[kaniest]

MLH schreef:

Consider the following...

With MyDB
Set qdfA = .CreateQueryDef("NewQry", "Select * From tblData Where_
[Field]=True;")
Set rstA = .OpenRecordset(qdfA, dbOpenSnapshot)
With rstA
.MoveFirst
Do Until rstA.EOF
...
Loop
.Close
End With
.QueryDefs.Delete qdfA.Name
.Close
End With

Should the deletion of the NewQry querydef object occur before or
after the last .Close?

Before. But why not use a temporary query if you're going
to delete it anyway?

--
Paul

[MLH]

On Fri, 24 Mar 2006 20:34:12 +0100, "kaniest"
<ka*****@invalid.invalid> wrote:

Before. But why not use a temporary query if you're going
to delete it anyway?

I tried that, but I kept getting an error when running
OpenRecordset - See new thread entitled
"I'm doing something wrong with OpenRecordset???"

[Albert D. Kallal]

I would certainly delete it after I am done...just as you have. however,
creating and deleing query us going to cause file bloat, and their is
little, if any reason to create a query...

Why not use

dim strSql as string
dim rstA as dao.recordset

set strSql = "Select * From tblData Where Field = True"

set rstA = currentdb.openRecordset(strSql)

do until rstA.EOF
' do whatever with the one roecrd
rst.MoveNext
loop
rstA.close
set rstA = nothing

you can see how clean, and how little the amount of above code is...and, we
don't even have to create a query.. You don't really even have to create a
database object either.....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Tim Marshall]

MLH wrote:

Consider the following...

Why on EARTH are you using a query? Just use the openrecordset method
on a currentdb object. Your code is simply adding a lot of uselessness
if you're not saving the query for something else... rather like going
camping and building a whole house each time instead of just erecting a
tent! 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[MLH]

Why on EARTH are you using a query? Just use the openrecordset method
on a currentdb object. Your code is simply adding a lot of uselessness
if you're not saving the query for something else... rather like going
camping and building a whole house each time instead of just erecting a
tent! 8)

I see your point. Had I created the query using the QBE grid, saved it
as a permanent query in the query tab of the database window, I could
have eliminated having to create it on the fly. Would that be a better
approach? I was hoping something I could do from within DAO would
eliminate the need to have a permanently saved query object.

[Rick Brandt]

"MLH" <CR**@NorthState.net> wrote in message
news:22********************************@4ax.com...

Why on EARTH are you using a query? Just use the openrecordset method
on a currentdb object. Your code is simply adding a lot of uselessness
if you're not saving the query for something else... rather like going
camping and building a whole house each time instead of just erecting a
tent! 8)

I see your point. Had I created the query using the QBE grid, saved it
as a permanent query in the query tab of the database window, I could
have eliminated having to create it on the fly. Would that be a better
approach? I was hoping something I could do from within DAO would
eliminate the need to have a permanently saved query object.

What was meant is that you can Open a Recordset just with the SQL string and the
database object. You don't need a QueryDef at all. Then there is no need to
delete it.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[MLH]

What was meant is that you can Open a Recordset just with the SQL string and the
database object. You don't need a QueryDef at all. Then there is no need to
delete it.

I finally go that through my thick head. I made the modification and
you're absolutely right. Well, that's what I get for doing cut 'n
paste code snippets from A97 HELP w/o scrutinizing them.
Quite often they do say "... now delete the saved query since
this is just an example..." or something to that effect. I just
haven't been paying attention closely enough when snatching
the snippets.