do i need to set objects to nothing

[xtra]

Hi Folk

I have about 1000 procedures in my project. Many, many of them are along
the lines of

function myfuntion () as boolean
on error goto er
'-
Dim Dbs as dao.database
Dim Rst as dao.recordset
Dim SqlS as string
'-
sqls = "..."
set dbs = currentdb
set rst = dbs.openrecords et(sqls)
do while not rst.eof
.....
rst.movenext
loop
'-
xt:
exit function
er:
msgbox error$
resume xt
end function
Should set dbs and rst to nothing or is that not necessary. If so, does
anyone know of an easy way to update all my procedures at once?

TIA

- Nicolaas

[Tom van Stiphout]

On Sat, 14 May 2005 12:45:23 +1200, "xtra"
<wi**********@h ottermail.com> wrote:

I would, and I would DEFINITELY close the recordset object
(rst.Close).

You can write code to update your code. Check out the properties and
methods of the Module object. Not a lot of fun though. Better do it
right the first time.

-Tom.

Hi Folk

I have about 1000 procedures in my project. Many, many of them are along
the lines of

function myfuntion () as boolean
on error goto er
'-
Dim Dbs as dao.database
Dim Rst as dao.recordset
Dim SqlS as string
'-
sqls = "..."
set dbs = currentdb
set rst = dbs.openrecords et(sqls)
do while not rst.eof
.....
rst.movenext
loop
'-
xt:
exit function
er:
msgbox error$
resume xt
end function
Should set dbs and rst to nothing or is that not necessary. If so, does
anyone know of an easy way to update all my procedures at once?

TIA

- Nicolaas

[xtra]

"Tom van Stiphout" <no************ *@cox.net> wrote in message
news:q4******** *************** *********@4ax.c om...

On Sat, 14 May 2005 12:45:23 +1200, "xtra"
<wi**********@h ottermail.com> wrote:

I would, and I would DEFINITELY close the recordset object
(rst.Close).

You can write code to update your code. Check out the properties and
methods of the Module object. Not a lot of fun though. Better do it
right the first time.

-Tom.

Hi Folk

I have about 1000 procedures in my project. Many, many of them are along
the lines of

function myfuntion () as boolean
on error goto er
'-
Dim Dbs as dao.database
Dim Rst as dao.recordset
Dim SqlS as string
'-
sqls = "..."
set dbs = currentdb
set rst = dbs.openrecords et(sqls)
do while not rst.eof
.....
rst.movenext
loop
'-
xt:
exit function
er:
msgbox error$
resume xt
end function
Should set dbs and rst to nothing or is that not necessary. If so, does
anyone know of an easy way to update all my procedures at once?

Hoi Tom

Thank you for your reply.

Oh NO!

It looks like this requires some strategic thinking. In the help it says:

"Variables declared in a procedure are visible only within the procedure and
lose their value between calls unless they are declared Static."

But obviously that does not apply to recordsets then?

I can write a function to add the
set rst = nothing
all the way through, but as you say, it will be some work to do so.

Could I not change the declaration so that the rst and dbs automatically
loose their value, etc... when the procedure is finished?

Thanks again

- Nicolaas

[Tom van Stiphout]

On Sat, 14 May 2005 14:21:52 +1200, "xtra"
<wi**********@h ottermail.com> wrote:

The reason that doesn't work with DAO and other object models (but
does work with strings and integers) is that little pesky thing called
OLE. It uses refence counting to keep track of who has what objects
instantiated, meaning that the well behaved application must decrement
a reference count after it no longer needs it. For some reason
unbeknownst to me VBA doesn't or can't do that when an OLE variable
goes out of scope.

BTW, this is one of the main reasons .NET does NOT use reference
counting. Everyone (including MSFT) now agrees that it was a bad idea,
requiring too much dilligence on the part of the developer. The smart
marketeers now sell the garbage collector in .NET as the best thing
since sliced bread. It performs the same function as
rst.Close
set rst = Nothing
without you ever having to worry about it.

Regards,

-Tom.

"Tom van Stiphout" <no************ *@cox.net> wrote in message
news:q4******* *************** **********@4ax. com...

On Sat, 14 May 2005 12:45:23 +1200, "xtra"
<wi**********@h ottermail.com> wrote:

I would, and I would DEFINITELY close the recordset object
(rst.Close).

You can write code to update your code. Check out the properties and
methods of the Module object. Not a lot of fun though. Better do it
right the first time.

-Tom.

>Hi Folk
>
>I have about 1000 procedures in my project. Many, many of them are along
>the lines of
>
>function myfuntion () as boolean
> on error goto er
>'-
> Dim Dbs as dao.database
> Dim Rst as dao.recordset
> Dim SqlS as string
>'-
> sqls = "..."
> set dbs = currentdb
> set rst = dbs.openrecords et(sqls)
> do while not rst.eof
> .....
> rst.movenext
> loop
>'-
>xt:
> exit function
>er:
> msgbox error$
> resume xt
>end function
>
>
>Should set dbs and rst to nothing or is that not necessary. If so, does
>anyone know of an easy way to update all my procedures at once?

Hoi Tom

Thank you for your reply.

Oh NO!

It looks like this requires some strategic thinking. In the help it says:

"Variables declared in a procedure are visible only within the procedure and
lose their value between calls unless they are declared Static."

But obviously that does not apply to recordsets then?

I can write a function to add the
set rst = nothing
all the way through, but as you say, it will be some work to do so.

Could I not change the declaration so that the rst and dbs automatically
loose their value, etc... when the procedure is finished?

Thanks again

- Nicolaas

[Lyle Fairfield]

Tom van Stiphout wrote:

The reason that doesn't work with DAO and other object models (but
does work with strings and integers) is that little pesky thing called
OLE. It uses refence counting to keep track of who has what objects
instantiated, meaning that the well behaved application must decrement
a reference count after it no longer needs it. For some reason
unbeknownst to me VBA doesn't or can't do that when an OLE variable
goes out of scope.

BTW, this is one of the main reasons .NET does NOT use reference
counting. Everyone (including MSFT) now agrees that it was a bad idea,
requiring too much dilligence on the part of the developer. The smart
marketeers now sell the garbage collector in .NET as the best thing
since sliced bread. It performs the same function as
rst.Close
set rst = Nothing
without you ever having to worry about it.

TTBOMK, there are two only "ghost" pointers/objects which should be
released, viz, the DAO Recordset and the DAO Database. I routinely and
purposefully never release ADO objects. And I seldom release other
objects, only doing so when habit of years past guides my fingers.

If I am right then it is DAO, and not VBA nor OLE, which is the culprit
here.

But your post encourages me. If garbage collection is a major selling
point of .Net then perhaps I am right in looking for another technology.
I am reminded of a Pontiac commercial in which two sophisticated young
men open the hood and look at each other. One whistles and the other
whispers sotto voce, "Transverse ly mounted V6!". Way to go boys, you're
twenty years behind the times and you want us to be impressed!

IMO, while Access is a fine GUI, DAO and VBA are archaic, clumsy and
inefficient.

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[Larry Linson]

"Tom van Stiphout" wrote

You can write code to update your
code. Check out the properties and
methods of the Module object. Not
a lot of fun though. Better do it
right the first time.

Work on a copy, and save your code-modification-code. Then, if you didn't
get it right the first time, make another copy, paste in your code-mod-code,
fix it, save it, and try again.

Larry Linson
Microsoft Access MVP

[Larry Linson]

"Lyle Fairfield" wrote

IMO, while Access is a fine GUI, DAO
and VBA are archaic, clumsy and
inefficient.

Maybe that's why some of us are so empathetic toward DAO and VBA -- because
we can relate to "archaic, clumsy, and inefficient". <GRIN>

[xtra]

"Tom van Stiphout" <no************ *@cox.net> wrote in message
news:m3******** *************** *********@4ax.c om...

On Sat, 14 May 2005 14:21:52 +1200, "xtra"
<wi**********@h ottermail.com> wrote:

The reason that doesn't work with DAO and other object models (but
does work with strings and integers) is that little pesky thing called
OLE. It uses refence counting to keep track of who has what objects
instantiated, meaning that the well behaved application must decrement
a reference count after it no longer needs it. For some reason
unbeknownst to me VBA doesn't or can't do that when an OLE variable
goes out of scope.

BTW, this is one of the main reasons .NET does NOT use reference
counting. Everyone (including MSFT) now agrees that it was a bad idea,
requiring too much dilligence on the part of the developer. The smart
marketeers now sell the garbage collector in .NET as the best thing
since sliced bread. It performs the same function as
rst.Close
set rst = Nothing
without you ever having to worry about it.

Regards,

-Tom.

"Tom van Stiphout" <no************ *@cox.net> wrote in message
news:q4******* *************** **********@4ax. com...

On Sat, 14 May 2005 12:45:23 +1200, "xtra"
<wi**********@h ottermail.com> wrote:

I would, and I would DEFINITELY close the recordset object
(rst.Close).

You can write code to update your code. Check out the properties and
methods of the Module object. Not a lot of fun though. Better do it
right the first time.

-Tom.
>Hi Folk
>
>I have about 1000 procedures in my project. Many, many of them are along >the lines of
>
>function myfuntion () as boolean
> on error goto er
>'-
> Dim Dbs as dao.database
> Dim Rst as dao.recordset
> Dim SqlS as string
>'-
> sqls = "..."
> set dbs = currentdb
> set rst = dbs.openrecords et(sqls)
> do while not rst.eof
> .....
> rst.movenext
> loop
>'-
>xt:
> exit function
>er:
> msgbox error$
> resume xt
>end function
>
>
>Should set dbs and rst to nothing or is that not necessary. If so, does >anyone know of an easy way to update all my procedures at once?

Hoi Tom

Thank you for your reply.

Oh NO!

It looks like this requires some strategic thinking. In the help it says:
"Variables declared in a procedure are visible only within the procedure andlose their value between calls unless they are declared Static."

But obviously that does not apply to recordsets then?

I can write a function to add the
set rst = nothing
all the way through, but as you say, it will be some work to do so.

Could I not change the declaration so that the rst and dbs automatically
loose their value, etc... when the procedure is finished?

Thanks again

- Nicolaas

Hi Guys
Thank you for all your interesting and entertaining messages.

I was wondering, can I write a function along the lines of

Function GC ()
for each object
if object.name = rst
rst.close
set rst = nothing
end if
next object
end function
????

That would solve my problem as all my functions end in

xt:
exit function

then it would be a simple replace.

I have recently been doing a lot of PHP/MySql and I found it to be very
clean and simple. HOWEVER, there are some aspects of Access that still make
it very powerful. In terms of GUI, you can really cranck it without having
to work with clumsy HTML

- Nicolaas

[Lyle Fairfield]

xtra wrote:

I was wondering, can I write a function along the lines of

Function GC ()
for each object
if object.name = rst
rst.close
set rst = nothing
end if
next object
end function
????

You could write any function you want, but it may not work. I have my
doubts about this one, in fact I would give you a thousand to one that
it would not.

If I were doing the code writing I would examine my code carefully and
write code to change it by

1. removing redundant DAO references eg. SET db=CurrentDB
2. changing the other DAO lines to ADO

first of course I would practise with a mock up.

ADO objects do not require release.

Oh yes, it's unlikely I would go through the Module object thing. It's
too clumsy and makes a mess of ones screen. I would SaveToFile the
modules, open the files with low-level open commands, stroke the code
using the Replace function a lot, save the revisions with new names and
then LoadFromFile. The original SaveToOFile files would be my safeties.
Probably the whole thing could be done with less than 50 lines of code.
--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[Lyle Fairfield]

Larry Linson wrote:

"Lyle Fairfield" wrote

> IMO, while Access is a fine GUI, DAO
> and VBA are archaic, clumsy and
> inefficient.

Maybe that's why some of us are so empathetic toward DAO and VBA -- because
we can relate to "archaic, clumsy, and inefficient". <GRIN>

What, are there others who type their answers with one finger of their
left hand because the arthritis, calcium deposits and bone chips of the
right shoulder that used to power that high hard fastball (way too many
times) in our youth, have claimed their revenge?

--
--
Lyle