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.openrecordset(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**********@hottermail.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.openrecordset(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.com...

On Sat, 14 May 2005 12:45:23 +1200, "xtra"
<wi**********@hottermail.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.openrecordset(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**********@hottermail.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**********@hottermail.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.openrecordset(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, "Transversely 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.com...

On Sat, 14 May 2005 14:21:52 +1200, "xtra"
<wi**********@hottermail.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**********@hottermail.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.openrecordset(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

[Steve Jorgensen]

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

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.openrecordset(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

OK, you've read all the posts saying that you do have to close and release
your DAO objects at a minimum, and perhaps other COM objects as well, but
reference counting does work correctly for internally defined objects.

There's a technique I've finally arrived at that uses the 2 pieces of
information above to create a simple, bullet proof way to make sure everything
gets cleaned up.

1. Make sure every procedure is covered by error handling. It can have its
own error handler or it can be called by something else that has an error
handler. That's because, if the code is ever reset, nothing your code does
can force proper clean-up, and having error handlers mostly prevents that. A
good rule of thumb is, put error handlers in your application start-up routine
and in every event handler. All other error handling is optional (and to be
avoided when as possible to keep code simpler).

2. Encapsulate every COM object within an instance of an internally defined
wrapper class, and have the terminate handler of the class explicitly close
and release the encapsulated object. Note that this termination handler will
fire whenever the object goes out of scope, even if that happens because a
procedure errored out, and the error was captured farther up the call stack.

3. Make sure every wrapper class also holds a reference to the wrapper class
of any object the thing it wraps depends on. That means the "parent" wrapper
can't go out of scope before the child. For instance, the DAO.Database
wrapper can't go out of scope before the DAO.Recordset wrapper that depends on
it.

It takes a bit of work to set this up initially, but then all the rest of your
code becomes much simpler. You no longer have to analyze each procedure to
make sure it will do its clean-up properly, and you don't have to write
additional error handlers just to force that to happen.

More specifics on wrappers...

First, once you have wrappers working, don't store references to the unwrapped
objects. That leads right back to the kinds of problems we wrote the wrappers
to avoid in the first place.

You don't need to make the wrappers wrap every method of the wrapped object
(that's excessive duplication), just provide an accessor to the wrapped
object. The wrappers do make great places to add helper functions though,
such as a way to get the names of missing query parameters to report a
meaningful error instead of ("2 parameters expected ...").

Since you'll be using the wrapper and the accessor a lot, use a short naming
convention for each.

When objects go out of scope, having dependent objects hold reference to their
dependencies forces the correct clean-up order, but if we allow actions that
can reset an object's state we need a way to force the dependent objects to
reset first. To do that, we use the VB/VBA Event mechanism. This doesn't
work in Access 97. You can make this safe in Access 97 by disabling actions
that can cause reset. Just check to see if the object is already set up, and
raise an error on any attempt to do anything that would reset the wrapped
object.

Here's a simplified, example (code is not tested, but does compile
successfully):

== clsDbsWrapper ==

Option Compare Database
Option Explicit

Private mdbs As DAO.Database
Private mblnCloseOnTerminate As Boolean

Public Event RequestReset()

Public Sub UseCurrentDb()
Reset
Set mdbs = CurrentDb
mblnCloseOnTerminate = False
End Sub

Public Sub OpenDb( _
Name As String, _
Optional Options As Variant, _
Optional ReadOnly As Variant, _
Optional Connect As Variant _
)
Reset
Set mdbs = DBEngine.OpenDatabase(Name, Options, ReadOnly, Connect)
mblnCloseOnTerminate = True
End Sub

Public Property Get dbs() As DAO.Database
Set dbs = mdbs
End Property

Public Function NewRstWrapper() As clsRstWrapper
Dim objRstWrapper As New clsRstWrapper
objRstWrapper.Setup Me
Set NewRstWrapper = objRstWrapper
End Function

Public Sub Reset()
' Tell dependent objects to reset first
RaiseEvent RequestReset

If mdbs Is Nothing Then Exit Sub

If mblnCloseOnTerminate Then
mdbs.Close
End If

Set mdbs = Nothing
End Sub

Private Sub Class_Terminate()
Reset
End Sub
== clsRstWrapper ==

Option Compare Database
Option Explicit

Private WithEvents mobjDbsWrapper As clsDbsWrapper
Private mrst As DAO.Recordset

Public Event RequestReset()

Public Sub Setup(objDbsWrapper As clsDbsWrapper)
Reset
Set mobjDbsWrapper = objDbsWrapper
End Sub

Public Sub OpenRst( _
Name As String, _
Optional RstType As Variant, _
Optional Options As Variant, _
Optional LockEdit As Variant _
)
Reset
Set mrst = mobjDbsWrapper.dbs.OpenRecordset( _
Name, RstType, Options, LockEdit)
End Sub

Public Property Get rst() As DAO.Recordset
Set rst = mrst
End Property

Public Sub Reset()
' Tell dependent objects to reset first
RaiseEvent RequestReset

If mrst Is Nothing Then Exit Sub

mrst.Close
Set mrst = Nothing

End Sub

Private Sub mobjDbsWrapper_RequestReset()
Reset
End Sub

Private Sub Class_Terminate()
Reset
End Sub

[Mike MacSween]

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:q0*****************@read1.cgocable.net...

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

What's the problem with that?

Is it meant to be something like:

rst = CurrentDB.openrecordset

?

[Trevor Best]

Mike MacSween wrote:

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:q0*****************@read1.cgocable.net...

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

What's the problem with that?

Is it meant to be something like:

rst = CurrentDB.openrecordset

That very quickly goes out of scope, the next line using rst will fail.

--
[Oo=w=oO]

[Trevor Best]

Tom van Stiphout wrote:

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.

Ah great, another language platform to encourage sloppy coding.

Whilst it's nice to not have to set a variable to nothing (we don't set
our ints to 0 and strings to "" at the end) I think a programmer should
at least close a recordset he has opened.

Sooner or later, a programmer may get another job and have to write a
program in another language that may be a lot stricter, they will then
fall foul of it.

--
[Oo=w=oO]

[Trevor Best]

xtra wrote:

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

It may close the recordsets (not tested) but will definately not release
the object variables as all you're doing there is creating new ones to
point at the ones that weren't cleaned up (assuming they were able to
point at them).

--
[Oo=w=oO]

[Lyle Fairfield]

Mike MacSween wrote:

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:q0*****************@read1.cgocable.net...

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

What's the problem with that?

Is it meant to be something like:

rst = CurrentDB.openrecordset

hmmm ... It was late; I was recommending converting everything to ADO.
Therefore DAO references would be redundant.
--
--
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

[Mike MacSween]

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:d7****************@read1.cgocable.net...

hmmm ... It was late; I was recommending converting everything to ADO.
Therefore DAO references would be redundant.

Got ya

[Steve Jorgensen]

On Sat, 14 May 2005 10:34:06 +0100, Trevor Best <no****@besty.org.uk> wrote:

Tom van Stiphout wrote:

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.

Ah great, another language platform to encourage sloppy coding.

Whilst it's nice to not have to set a variable to nothing (we don't set
our ints to 0 and strings to "" at the end) I think a programmer should
at least close a recordset he has opened.

Sooner or later, a programmer may get another job and have to write a
program in another language that may be a lot stricter, they will then
fall foul of it.

To me, that's like saying we should make programmers deal with pointers
correctly because they may eventually have to code in C. To me, any way a
language can get junk out of the way, so a program has, as much as possible,
just logic about the application and not about the system, that's a good
thing.

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:dm********************************@4ax.com:

A
good rule of thumb is, put error handlers in your application
start-up routine and in every event handler. All other error
handling is optional (and to be avoided when as possible to keep
code simpler).

Er, I don't think you typed what you meant to type.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Lyle Fairfield <ly******@yahoo.ca> wrote in
news:wt****************@read1.cgocable.net:

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

Well, from where I sit, technologies like ADO are so advanced and
up-to-date and sleekly designed that they guess what I mean to do
and end up getting results that are not what I would have intended.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Mike MacSween" <mi***************************@btinternet.com> wrote
in news:42***********************@news.aaisp.net.uk:

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:q0*****************@read1.cgocable.net...

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

What's the problem with that?

Is it meant to be something like:

rst = CurrentDB.openrecordset

My guess is that what Lyle means is that if you're constantly
resetting a db variable to CurrentDB() you should either use
DBEngine(0)(0) directly, or use a persistent structure, either a
global variable storing a pointer to CurrentDB() or a function that
returns a reference to CurrentDB() drawn from a persistent variable.

I use the function posted after my signature. It has vastly
simplified my coding, as I never have to set a db variable just to
perform something using CurrentDB().

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Public Function dbLocal(Optional ysnInitialize As Boolean = True) _
As DAO.Database
' 2003/02/08 DWF added comments to explain it to myself!
' 2005/03/18 DWF changed to use Static variable instead
' of module Private variable for dbCurrent
' uses GoTos instead of If/Then because:
' error of dbCurrent not being Nothing but dbCurrent being closed
' would (3420)would then be jumping back into the middle of an
' If/Then statement
On Error GoTo errHandler
Static dbCurrent As DAO.Database
Dim strTest As String

If Not ysnInitialize Then GoTo closeDB

retryDB:
If dbCurrent Is Nothing Then
Set dbCurrent = CurrentDb()
End If
' now that we know the db variable is not Nothing,
' test if it's Open
strTest = dbCurrent.Name

exitRoutine:
Set dbLocal = dbCurrent
Exit Function

closeDB:
If Not (dbCurrent Is Nothing) Then
'dbCurrent.close
Set dbCurrent = Nothing
End If
GoTo exitRoutine

errHandler:
Select Case Err.Number
Case 3420 ' Object invalid or no longer set.
Set dbCurrent = Nothing
If ysnInitialize Then
Resume retryDB
Else
Resume closeDB
End If
Case Else
MsgBox Err.Number & ": " & Err.Description, _
vbExclamation, "dbLocal()"
Resume exitRoutine
End Select
End Function

[Steve Jorgensen]

On Sat, 14 May 2005 17:47:38 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Lyle Fairfield <ly******@yahoo.ca> wrote in
news:wt****************@read1.cgocable.net:

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

Well, from where I sit, technologies like ADO are so advanced and
up-to-date and sleekly designed that they guess what I mean to do
and end up getting results that are not what I would have intended.

That's somewhat true of ADO, but it's much more true of ADPs that happen to be
using ADO. ADO alone is not so bad, it's just that DAO is unavoidable anyway
in an Access application, so ADO is not a solution to resource problems in DAO
in Access.

[David W. Fenton]

Lyle Fairfield <ly******@yahoo.ca> wrote in
news:d7****************@read1.cgocable.net:

Mike MacSween wrote:

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:q0*****************@read1.cgocable.net...

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

What's the problem with that?

Is it meant to be something like:

rst = CurrentDB.openrecordset

hmmm ... It was late; I was recommending converting everything to
ADO. Therefore DAO references would be redundant.

A phenomentally idiotic recommendation if the back end is Jet.

Migrating to an inferior data access method (performance, features)
just to make coding slightly easier would be bloody stupid.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Sat, 14 May 2005 17:41:50 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:dm********************************@4ax.com :

A
good rule of thumb is, put error handlers in your application
start-up routine and in every event handler. All other error
handling is optional (and to be avoided when as possible to keep
code simpler).

Er, I don't think you typed what you meant to type.

No, I meant to type "... and to be avoided when possible..." <g>.

I don't mean, of course, that you should just leave out handling where it's
needed to ensure graceful erorr handling. What I mean is that we should look
for ways of making sure errors will be handled gracefully with a minimum
number of error handling points, so the code consists, for the most part of
application logic, not cluttered by vast amounts of error handling baggage.

The minimum error handling required to make sure the user won't ever get an
error message directly from VB (which crashes Access run-time apps, by the
way) is what I said above. The start-up routine and every event handler needs
error handling, or it needs to contain a trivial call (no non-costant
parameter values) to a procedure that has error handling.

[Steve Jorgensen]

On Sat, 14 May 2005 17:53:10 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"Mike MacSween" <mi***************************@btinternet.com> wrote
in news:42***********************@news.aaisp.net.uk:

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:q0*****************@read1.cgocable.net...

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

What's the problem with that?

Is it meant to be something like:

rst = CurrentDB.openrecordset

My guess is that what Lyle means is that if you're constantly
resetting a db variable to CurrentDB() you should either use
DBEngine(0)(0) directly, or use a persistent structure, either a
global variable storing a pointer to CurrentDB() or a function that
returns a reference to CurrentDB() drawn from a persistent variable.

I use the function posted after my signature. It has vastly
simplified my coding, as I never have to set a db variable just to
perform something using CurrentDB().

I call that a lazy initializer and I use them a lot, too. Another thing I
like to use them for is creating structures that hold application settings
read from a table or .ini file.

[rkc]

Trevor Best wrote:

Mike MacSween wrote:

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:q0*****************@read1.cgocable.net...

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

What's the problem with that?

Is it meant to be something like:

rst = CurrentDB.openrecordset

That very quickly goes out of scope, the next line using rst will fail.

What goes out of scope on the next line?
The recordset goes out of scope when the method or object it was created
in goes out of scope, but surely not just the next line.

[Steve Jorgensen]

On Sat, 14 May 2005 18:38:27 GMT, rkc <rk*@rochester.yabba.dabba.do.rr.bomb>
wrote:

Trevor Best wrote:

Mike MacSween wrote:

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:q0*****************@read1.cgocable.net...
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

What's the problem with that?

Is it meant to be something like:

rst = CurrentDB.openrecordset

That very quickly goes out of scope, the next line using rst will fail.

What goes out of scope on the next line?
The recordset goes out of scope when the method or object it was created
in goes out of scope, but surely not just the next line.

CurrentDb returns a new instance of DAO.Database. Since there's no variable
holding a reference to that instance, it goes out of scope immediately after
the statement completes.

[Trevor Best]

rkc wrote:

Trevor Best wrote:

Mike MacSween wrote:

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:q0*****************@read1.cgocable.net...
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


What's the problem with that?

Is it meant to be something like:

rst = CurrentDB.openrecordset

That very quickly goes out of scope, the next line using rst will fail.

What goes out of scope on the next line?
The recordset goes out of scope when the method or object it was created
in goes out of scope, but surely not just the next line.

Try it, a recordset needs a database to hang off of, as Steve points out
CurrentDb goes out of scope so now your rst variable points at nothing
and you can no longer use it.

--
[Oo=w=oO]

[Trevor Best]

Steve Jorgensen wrote:

To me, that's like saying we should make programmers deal with pointers
correctly because they may eventually have to code in C.
That's a ridicularse statement. There's a difference between having to
use something specific to another language and not being sloppy.
To me, any way a
language can get junk out of the way, so a program has, as much as possible,
just logic about the application and not about the system, that's a good
thing.

Would you ever use:

open "filename" for output as #1

then never close it?

--
[Oo=w=oO]

[rkc]

Trevor Best wrote:

rkc wrote:

Trevor Best wrote:

Mike MacSween wrote:

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:q0*****************@read1.cgocable.net...
> 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

What's the problem with that?

Is it meant to be something like:

rst = CurrentDB.openrecordset


That very quickly goes out of scope, the next line using rst will fail.

What goes out of scope on the next line?
The recordset goes out of scope when the method or object it was
created in goes out of scope, but surely not just the next line.

Try it, a recordset needs a database to hang off of, as Steve points out
CurrentDb goes out of scope so now your rst variable points at nothing
and you can no longer use it.

I'm baffled.

This works.

Sub testCurrentDB()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("employees")

showRS rs

Set rs = Nothing
End Sub

Sub showRS(rs As DAO.Recordset)
With rs
If Not .EOF Then
Debug.Print .Fields(0)
Debug.Print .Fields(1)
Debug.Print .Fields(2)
End If
End With
End Sub

[RoyVidar]

But do try something like this:

dim tdf as dao.tabledef
dim idx as dao.index
dim l as long

set tdf = currentdb.tabledefs("employees")
for each idx in tdf.indexes
for l = 0 to idx.fields.count-1
debug.print idx.fields(l).name
next l
debug.print idx.name, idx.foreign, idx.unique, idx.primary,
idx.required
next idx

vs

dim db as dao.database
set db=currentdb
set tdf = db.tabledefs("employees")
....
[snip]

I'm baffled.

This works.

Sub testCurrentDB()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("employees")

showRS rs

Set rs = Nothing
End Sub

Sub showRS(rs As DAO.Recordset)
With rs
If Not .EOF Then
Debug.Print .Fields(0)
Debug.Print .Fields(1)
Debug.Print .Fields(2)
End If
End With
End Sub

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:19********************************@4ax.com:

On Sat, 14 May 2005 17:41:50 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:dm********************************@4ax.co m:

A
good rule of thumb is, put error handlers in your application
start-up routine and in every event handler. All other error
handling is optional (and to be avoided when as possible to keep
code simpler).
Er, I don't think you typed what you meant to type.

No, I meant to type "... and to be avoided when possible..." <g>.

I don't mean, of course, that you should just leave out handling
where it's needed to ensure graceful erorr handling. What I mean
is that we should look for ways of making sure errors will be
handled gracefully with a minimum number of error handling points,
so the code consists, for the most part of application logic, not
cluttered by vast amounts of error handling baggage.

Well, I'm not sure what you mean, then, by "event handler."
The minimum error handling required to make sure the user won't
ever get an error message directly from VB (which crashes Access
run-time apps, by the way) is what I said above. The start-up
routine and every event handler needs error handling, or it needs
to contain a trivial call (no non-costant parameter values) to a
procedure that has error handling.

I agree that one should structure code to have the fewest error
handlers necessary. But I think most people put them everywhere,
even in code in subroutines that are so trivial and have no outside
dependencies that they cannot throw an error.

The problem is that it's often hard to decide whether or not to put
error handlers in some subroutines that are called from multiple
locations, because in some cases, you want the subroutine to handle
the error, and in others, you want the calling subroutine to handle
it.

But most code that I write is not that complicated, so I've only
occasionally encountered that conundrum.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:1m********************************@4ax.com:

On Sat, 14 May 2005 17:53:10 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"Mike MacSween" <mi***************************@btinternet.com>
wrote in news:42***********************@news.aaisp.net.uk:

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:q0*****************@read1.cgocable.net...

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

What's the problem with that?

Is it meant to be something like:

rst = CurrentDB.openrecordset

My guess is that what Lyle means is that if you're constantly
resetting a db variable to CurrentDB() you should either use
DBEngine(0)(0) directly, or use a persistent structure, either a
global variable storing a pointer to CurrentDB() or a function
that returns a reference to CurrentDB() drawn from a persistent
variable.

I use the function posted after my signature. It has vastly
simplified my coding, as I never have to set a db variable just to
perform something using CurrentDB().

I call that a lazy initializer and I use them a lot, too. Another
thing I like to use them for is creating structures that hold
application settings read from a table or .ini file.

I just refer to those as self-initializing and self-healing.

Anything more complex than this, with groups of items that need to
be initialized, I'd likely wrap in a class.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Sat, 14 May 2005 21:51:14 GMT, rkc <rk*@rochester.yabba.dabba.do.rr.bomb>
wrote:

....

I'm baffled.

This works.

Sub testCurrentDB()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("employees")

showRS rs

Set rs = Nothing
End Sub

Sub showRS(rs As DAO.Recordset)
With rs
If Not .EOF Then
Debug.Print .Fields(0)
Debug.Print .Fields(1)
Debug.Print .Fields(2)
End If
End With
End Sub

That's because it's unpredictable when the Database instance is actually
cleaned up in the background. Sometimes it works after it's out of scope, and
sometimes it doesn't.

[xtra]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:dm********************************@4ax.com...

[snip]....[snip]....[snip]....[snip]....[snip]....[snip]....[snip]....[snip]
.....
Wow, thanks Steve, that was a rather indepth reply, in fact, there were so
many useful notes.

I think I want to use your class. That looks awesome (I am still trying to
understand how it all works....) Can you tell me how I should call it?

Thank you so much

Nicolaas

[rkc]

Steve Jorgensen wrote:

On Sat, 14 May 2005 21:51:14 GMT, rkc <rk*@rochester.yabba.dabba.do.rr.bomb>
wrote:

...

I'm baffled.

This works.

Sub testCurrentDB()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("employees")

showRS rs

Set rs = Nothing
End Sub

Sub showRS(rs As DAO.Recordset)
With rs
If Not .EOF Then
Debug.Print .Fields(0)
Debug.Print .Fields(1)
Debug.Print .Fields(2)
End If
End With
End Sub

That's because it's unpredictable when the Database instance is actually
cleaned up in the background. Sometimes it works after it's out of scope, and
sometimes it doesn't.

So maybe all or some of the recordset, depending on how trivial it is,
is left in memory? Trying to do an update doesn't work, but it doesn't
throw an error either.

So what's lame here, DAO or VBA? (Me, is beside the point.)

[rkc]

RoyVidar wrote:

But do try something like this:

dim tdf as dao.tabledef
dim idx as dao.index
dim l as long

set tdf = currentdb.tabledefs("employees")
for each idx in tdf.indexes
for l = 0 to idx.fields.count-1
debug.print idx.fields(l).name
next l
debug.print idx.name, idx.foreign, idx.unique, idx.primary,
idx.required
next idx

vs

dim db as dao.database
set db=currentdb
set tdf = db.tabledefs("employees")
...

O.K. No maybe it works maybe it doesn't with that.

[xtra]

"xtra" <wi**********@hottermail.com> wrote in message
news:uT******************@news.xtra.co.nz...

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.openrecordset(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?

As another idea, would it not be practical just to create a global variable
Dbs, set it to currentdb and leave it open "forever"?

As I said, there are about 800 procedures in my database that use dbs =
currentdb so why not keep it open at all times, or would that cause chaos if
more than one procedure would use it at the same time?

Cheers

Nicolaas

[Steve Jorgensen]

On Sun, 15 May 2005 11:25:18 +1200, "xtra" <wi**********@hottermail.com>
wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:dm********************************@4ax.com.. .

[snip]....[snip]....[snip]....[snip]....[snip]....[snip]....[snip]....[snip]
....
Wow, thanks Steve, that was a rather indepth reply, in fact, there were so
many useful notes.

I think I want to use your class. That looks awesome (I am still trying to
understand how it all works....) Can you tell me how I should call it?

Thank you so much

Nicolaas

Well, you'll need to flesh out that code some more before it'll get you very
far. For instance, you'll want to have a querydef wrapper, and extend the
recordset wrapper to hold either a database wrapper or a querydef wrapper as
its parent. It's not that hard if you understand what's there now - just
saying you'll need to add some.

Anyway, here's an example of how you might use the classes as presented...

Public Sub TestDaoWrappers
Dim dbw As New clsDbsWrapper
bdw.UseCurrentDb

Dim rsw As clsRstWrapper
set rsw = dbw.NewRstWrapper
rsw.OpenRecordset("SELECT * FROM tblFoo")

if rsw.rst.Recordcount = 0 then Exit sub
Do Until rsw.rst.EOF
Debug.Print rsw.rst!Foo
rsw.rst.MoveNext
Loop

End Sub

Note how the wrappers close the DAO objects and release them in the proper
order, so this procedure doesn't have to do explicitly do that. If the
calling procedure has an error handler, the cleanup will happen right after
the error is raised, and before control passes to the error handler in the
calling procedure.

The possibilites for improvement are boundless. For instance, you could add
something like a LoopNext() method that returns True if there are more records
to read, False if not, and increments to the next record each time it's called
except the first time. That would allow the code above to become something
like this...

Public Sub TestDaoWrappers
Dim dbw As New clsDbsWrapper
bdw.UseCurrentDb

Dim rsw As clsRstWrapper
set rsw = dbw.NewRstWrapper
rsw.OpenRecordset("SELECT * FROM tblFoo")

Do While rsw.LoopNext
Debug.Print rsw.rst!Foo
Loop

End Sub

[Steve Jorgensen]

On Sat, 14 May 2005 22:46:28 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:19********************************@4ax.com :

On Sat, 14 May 2005 17:41:50 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:dm********************************@4ax.com :

A
good rule of thumb is, put error handlers in your application
start-up routine and in every event handler. All other error
handling is optional (and to be avoided when as possible to keep
code simpler).

Er, I don't think you typed what you meant to type.
No, I meant to type "... and to be avoided when possible..." <g>.

I don't mean, of course, that you should just leave out handling
where it's needed to ensure graceful erorr handling. What I mean
is that we should look for ways of making sure errors will be
handled gracefully with a minimum number of error handling points,
so the code consists, for the most part of application logic, not
cluttered by vast amounts of error handling baggage.

Well, I'm not sure what you mean, then, by "event handler."

Oh, OnClick, BeforeUpdate, etc. As I know you know (but for the benefit of
lurkers) these are all things the environment can cause to run that were not
called directly by other code, so there can be no higher-level error handler
to trap errors that happen inside them.

My policy on these is that each one needs its own error handler or it needs to
only call code defined within the same class module or a standard module in
the VB project that does have an error handler, and it may only pass constants
or trivial local variables as parameters

The minimum error handling required to make sure the user won't
ever get an error message directly from VB (which crashes Access
run-time apps, by the way) is what I said above. The start-up
routine and every event handler needs error handling, or it needs
to contain a trivial call (no non-costant parameter values) to a
procedure that has error handling.

I agree that one should structure code to have the fewest error
handlers necessary. But I think most people put them everywhere,
even in code in subroutines that are so trivial and have no outside
dependencies that they cannot throw an error.

The problem is that it's often hard to decide whether or not to put
error handlers in some subroutines that are called from multiple
locations, because in some cases, you want the subroutine to handle
the error, and in others, you want the calling subroutine to handle
it.

I try to document any procedure that I expect may raise errors, so when I call
it, I know I have to either handle the error or be called by something that
will.
But most code that I write is not that complicated, so I've only
occasionally encountered that conundrum.

[xtra]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:o6********************************@4ax.com...

On Sun, 15 May 2005 11:25:18 +1200, "xtra" <wi**********@hottermail.com>
wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:dm********************************@4ax.com.. .

[snip]....[snip]....[snip]....[snip]....[snip]....[snip]....[snip]....[snip

]

....
Wow, thanks Steve, that was a rather indepth reply, in fact, there were somany useful notes.

I think I want to use your class. That looks awesome (I am still trying tounderstand how it all works....) Can you tell me how I should call it?

Thank you so much

Nicolaas

Well, you'll need to flesh out that code some more before it'll get you

very far. For instance, you'll want to have a querydef wrapper, and extend the
recordset wrapper to hold either a database wrapper or a querydef wrapper as its parent. It's not that hard if you understand what's there now - just
saying you'll need to add some.

Anyway, here's an example of how you might use the classes as presented...

Public Sub TestDaoWrappers
Dim dbw As New clsDbsWrapper
bdw.UseCurrentDb

Dim rsw As clsRstWrapper
set rsw = dbw.NewRstWrapper
rsw.OpenRecordset("SELECT * FROM tblFoo")

if rsw.rst.Recordcount = 0 then Exit sub
Do Until rsw.rst.EOF
Debug.Print rsw.rst!Foo
rsw.rst.MoveNext
Loop

End Sub

Note how the wrappers close the DAO objects and release them in the proper
order, so this procedure doesn't have to do explicitly do that. If the
calling procedure has an error handler, the cleanup will happen right after the error is raised, and before control passes to the error handler in the
calling procedure.

The possibilites for improvement are boundless. For instance, you could add something like a LoopNext() method that returns True if there are more rec ords to read, False if not, and increments to the next record each time it's called except the first time. That would allow the code above to become something like this...

Public Sub TestDaoWrappers
Dim dbw As New clsDbsWrapper
bdw.UseCurrentDb

Dim rsw As clsRstWrapper
set rsw = dbw.NewRstWrapper
rsw.OpenRecordset("SELECT * FROM tblFoo")

Do While rsw.LoopNext
Debug.Print rsw.rst!Foo
Loop

End Sub

LOVING IT

Here is the looper function that I created:

Public Function LoopNext() As Boolean
If Me.RST.RecordCount = 0 Then
LoopNext = False
Else
If NotFirstLoop = True Then
Me.RST.MoveNext
Else
NotFirstLoop = True
End If
If Not Me.RST.EOF Then
LoopNext = True
Else
LoopNext = False
End If
End If
End Function

Although I still dont really understand it all, it seems to be working as a
train on kerosine

[Trevor Best]

rkc wrote:

So what's lame here, DAO or VBA? (Me, is beside the point.)

Possibly neither or both. Maybe an interaction between the two.

--
[Oo=w=oO]

[Chuck Grimsby]

Rather then creating and destroying a Database and/or Recordset object
in each function or sub that needs one, create them in whatever calls
the fucntion or sub and pass it to the function or sub.

Then you can set them to Nothing in the calling routine without all the
nasty overhead of trying to remember to do that in each routine.

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:4p********************************@4ax.com:

On Sat, 14 May 2005 22:46:28 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:19********************************@4ax.co m:

On Sat, 14 May 2005 17:41:50 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:dm********************************@4ax.co m:

> A
> good rule of thumb is, put error handlers in your application
> start-up routine and in every event handler. All other error
> handling is optional (and to be avoided when as possible to
> keep code simpler).

Er, I don't think you typed what you meant to type.

No, I meant to type "... and to be avoided when possible..."
<g>.

I don't mean, of course, that you should just leave out handling
where it's needed to ensure graceful erorr handling. What I
mean is that we should look for ways of making sure errors will
be handled gracefully with a minimum number of error handling
points, so the code consists, for the most part of application
logic, not cluttered by vast amounts of error handling baggage.
Well, I'm not sure what you mean, then, by "event handler."

Oh, OnClick, BeforeUpdate, etc. As I know you know (but for the
benefit of lurkers) these are all things the environment can cause
to run that were not called directly by other code, so there can
be no higher-level error handler to trap errors that happen inside
them.

Well, I disagree with that. Whether or not you need an error handler
depends entirely on what the code in the event handler does.
My policy on these is that each one needs its own error handler or
it needs to only call code defined within the same class module or
a standard module in the VB project that does have an error
handler, and it may only pass constants or trivial local variables
as parameters

I think it's going overboard to have such a hard-and-fast rule, but
that's vintage Steve Jorgensen! :)

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"xtra" <wi**********@hottermail.com> wrote in
news:2E***************@news.xtra.co.nz:

As another idea, would it not be practical just to create a global
variable Dbs, set it to currentdb and leave it open "forever"?

As I said, there are about 800 procedures in my database that use
dbs = currentdb so why not keep it open at all times, or would
that cause chaos if more than one procedure would use it at the
same time?

I used to do that, but the problem is that if there's any
possibility of a code reset, your global variable could be Nothing.
Likewise, you have to initialize it somewhere.

I posted this elsewhere in the thread, but what I use for this is a
function that uses a Static db variable and sets it to CurrentDB()
if it's not already set, and just returns the value otherwise. The
code is posted after my signature.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Public Function dbLocal(Optional ysnInitialize As Boolean = True) _
As DAO.Database
' 2003/02/08 DWF added comments to explain it to myself!
' 2005/03/18 DWF changed to use Static variable instead
' of module Private variable for dbCurrent
' uses GoTos instead of If/Then because:
' error of dbCurrent not being Nothing but dbCurrent being closed
' would (3420)would then be jumping back into the middle of an
' If/Then statement
On Error GoTo errHandler
Static dbCurrent As DAO.Database
Dim strTest As String

If Not ysnInitialize Then GoTo closeDB

retryDB:
If dbCurrent Is Nothing Then
Set dbCurrent = CurrentDb()
End If
' now that we know the db variable is not Nothing,
' test if it's Open
strTest = dbCurrent.Name

exitRoutine:
Set dbLocal = dbCurrent
Exit Function

closeDB:
If Not (dbCurrent Is Nothing) Then
'dbCurrent.close
Set dbCurrent = Nothing
End If
GoTo exitRoutine

errHandler:
Select Case Err.Number
Case 3420 ' Object invalid or no longer set.
Set dbCurrent = Nothing
If ysnInitialize Then
Resume retryDB
Else
Resume closeDB
End If
Case Else
MsgBox Err.Number & ": " & Err.Description, _
vbExclamation, "dbLocal()"
Resume exitRoutine
End Select
End Function

[Steve Jorgensen]

On 15 May 2005 04:13:30 -0700, "Chuck Grimsby" <c.*******@worldnet.att.net>
wrote:

Rather then creating and destroying a Database and/or Recordset object
in each function or sub that needs one, create them in whatever calls
the fucntion or sub and pass it to the function or sub.

Then you can set them to Nothing in the calling routine without all the
nasty overhead of trying to remember to do that in each routine.

That can be more harm than good. If I have one procedure that returns a
collection of data items pulled form a table, and I call it from 10 places,
you'r now saying those 10 places each need to create and destroy database
objects. Now, if I ever want to change the operation of the procedure so it
does something other than read from the database, I also have to track down
all those 10 places.

[Steve Jorgensen]

On Sun, 15 May 2005 18:01:39 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

....

Well, I'm not sure what you mean, then, by "event handler."

Oh, OnClick, BeforeUpdate, etc. As I know you know (but for the
benefit of lurkers) these are all things the environment can cause
to run that were not called directly by other code, so there can
be no higher-level error handler to trap errors that happen inside
them.

Well, I disagree with that. Whether or not you need an error handler
depends entirely on what the code in the event handler does.

My policy on these is that each one needs its own error handler or
it needs to only call code defined within the same class module or
a standard module in the VB project that does have an error
handler, and it may only pass constants or trivial local variables
as parameters

I think it's going overboard to have such a hard-and-fast rule, but
that's vintage Steve Jorgensen! :)

Are you sure you disagree with that? How many places where an event handler
has code behind it don't need error handling?

How about ...

Private Sub Form_Current()
Debug.Print Me!txtName
End Sub

Of course, there are many ways that could fail. Pretty much any event handler
is going to deal with data on the object that called it, and attempts to do
that have failure modes.

[Steve Jorgensen]

On Sun, 15 May 2005 18:04:38 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"xtra" <wi**********@hottermail.com> wrote in
news:2E***************@news.xtra.co.nz:

As another idea, would it not be practical just to create a global
variable Dbs, set it to currentdb and leave it open "forever"?

As I said, there are about 800 procedures in my database that use
dbs = currentdb so why not keep it open at all times, or would
that cause chaos if more than one procedure would use it at the
same time?

I used to do that, but the problem is that if there's any
possibility of a code reset, your global variable could be Nothing.
Likewise, you have to initialize it somewhere.

I posted this elsewhere in the thread, but what I use for this is a
function that uses a Static db variable and sets it to CurrentDB()
if it's not already set, and just returns the value otherwise. The
code is posted after my signature.

Just a warning - if you extend this idea to recordsets or some such, you need
a way to explicitly clean them up before the application closes. I usually
open a hidden form when the application starts and use its Unload event
handler to start a clean-up routine.

[xtra]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ag********************************@4ax.com...

On 15 May 2005 04:13:30 -0700, "Chuck Grimsby" <c.*******@worldnet.att.net> wrote:

Rather then creating and destroying a Database and/or Recordset object
in each function or sub that needs one, create them in whatever calls
the fucntion or sub and pass it to the function or sub.

Then you can set them to Nothing in the calling routine without all the
nasty overhead of trying to remember to do that in each routine.
That can be more harm than good. If I have one procedure that returns a
collection of data items pulled form a table, and I call it from 10

places, you'r now saying those 10 places each need to create and destroy database
objects. Now, if I ever want to change the operation of the procedure so it does something other than read from the database, I also have to track down all those 10 places.

How about leaving the Dbs = currentdb open "forever". It is used all the
time and so it would be helpfull just to sit there open and ready for action
at any time...

[Steve Jorgensen]

On Mon, 16 May 2005 11:05:27 +1200, "xtra" <wi**********@hottermail.com>
wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ag********************************@4ax.com.. .

On 15 May 2005 04:13:30 -0700, "Chuck Grimsby"

<c.*******@worldnet.att.net>

wrote:

>Rather then creating and destroying a Database and/or Recordset object
>in each function or sub that needs one, create them in whatever calls
>the fucntion or sub and pass it to the function or sub.
>
>Then you can set them to Nothing in the calling routine without all the
>nasty overhead of trying to remember to do that in each routine.

That can be more harm than good. If I have one procedure that returns a
collection of data items pulled form a table, and I call it from 10

places,

you'r now saying those 10 places each need to create and destroy database
objects. Now, if I ever want to change the operation of the procedure so

it

does something other than read from the database, I also have to track

down

all those 10 places.

How about leaving the Dbs = currentdb open "forever". It is used all the
time and so it would be helpfull just to sit there open and ready for action
at any time...

If you do that, you have to decide where to initialize it and make sure it
gets reinitialized if it gets lost when you reset your code during debugging.

The procedure David proposed in this thread (twice) is the best one I know of.
Have a function that, when called, assigns CurrentDB to a private variable if
it is currently Nothing, then returns it to the caller.