By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Instance of Access left open when using recordsetclone.

P: n/a
Hey guys,

I have the following code:
'************************************************* *****
If Not Me.NewRecord Then

Dim rs As DAO.Recordset
Dim strBookmark As String
Set rs = Forms("frmMaster").RecordsetClone 'set an instance of form
master recordset clone
rs.FindFirst "CustomerID='" & Me.CustomerID & "'" 'find a particular
record in the recordset
If Not rs.NoMatch Then
strBookmark = rs.Bookmark 'grab the bookmark
Forms("frmMaster").Bookmark = strBookmark 'set the FORM equal to
the rs bookmark to move to that record.
End If

End If
'************************************************* *******
This code works fine.
I am using this code to move to a record on my main form. I cannot use
another technique like filtering for reasons I will not explain.

The exit handler is running the following code:
'************************************************* *******
rs.Close
If Not rs Is Nothing Then
Set rs = Nothing
End If
'************************************************* *******
This code is triggered by the Current event of a SUBFORM of the Main form.
(in other words every time the application opens).

Here is my problem:

When the application exits the locking files for both the front end and back
ends are not automatically deleted. A look at Task Manager (Win XP Pro)
shows that an instance of Access is open. And if you go back into the
application and try to open the form in design view you get the "You do not
have exclusive access..." error message. I have never seen this behavior
before. Has anyone else. Can you clue me in on what to do here to rid
myself of this problem.

Seth B Spearman
Nov 12 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Oh - don't close a recordset clone, just set its reference to Nothing. The
general rule is - If you .Open it, then .Close it. If you don't .Open it,
don't .Close it.

On Mon, 1 Mar 2004 22:34:41 -0500, "Seth Spearman" <se**********@hotmail.com>
wrote:
Hey guys,

I have the following code:
'************************************************ ******
If Not Me.NewRecord Then

Dim rs As DAO.Recordset
Dim strBookmark As String
Set rs = Forms("frmMaster").RecordsetClone 'set an instance of form
master recordset clone
rs.FindFirst "CustomerID='" & Me.CustomerID & "'" 'find a particular
record in the recordset
If Not rs.NoMatch Then
strBookmark = rs.Bookmark 'grab the bookmark
Forms("frmMaster").Bookmark = strBookmark 'set the FORM equal to
the rs bookmark to move to that record.
End If

End If
'************************************************ ********
This code works fine.
I am using this code to move to a record on my main form. I cannot use
another technique like filtering for reasons I will not explain.

The exit handler is running the following code:
'************************************************ ********
rs.Close
If Not rs Is Nothing Then
Set rs = Nothing
End If
'************************************************ ********
This code is triggered by the Current event of a SUBFORM of the Main form.
(in other words every time the application opens).

Here is my problem:

When the application exits the locking files for both the front end and back
ends are not automatically deleted. A look at Task Manager (Win XP Pro)
shows that an instance of Access is open. And if you go back into the
application and try to open the form in design view you get the "You do not
have exclusive access..." error message. I have never seen this behavior
before. Has anyone else. Can you clue me in on what to do here to rid
myself of this problem.

Seth B Spearman


Nov 12 '05 #2

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:79********************************@4ax.com:
Oh - don't close a recordset clone, just set its reference to
Nothing. The general rule is - If you .Open it, then .Close it.
If you don't .Open it, don't .Close it.


Better yet, don't use a recordset variable, but a WITH structure:

With Forms!frmMaster.RecordsetClone
[do your thing]
End With

Then you don't have to worry about whether you need to set to
Nothing or Close what you've used.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #3

P: n/a
On Tue, 02 Mar 2004 22:54:18 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:79********************************@4ax.com :
Oh - don't close a recordset clone, just set its reference to
Nothing. The general rule is - If you .Open it, then .Close it.
If you don't .Open it, don't .Close it.


Better yet, don't use a recordset variable, but a WITH structure:

With Forms!frmMaster.RecordsetClone
[do your thing]
End With

Then you don't have to worry about whether you need to set to
Nothing or Close what you've used.


Well, you still need to worry about whether to .Close it - don't :) If .Close
was required, it would need to go before the End With. I second the motion
that With blocks are good for stuff like this, just avoid long block or
nesting because it gets really hard to read. Procedure calls are a good
alternative to nested With blocks...

....
DoYourThing Forms!frmMaster.RecordsetClone
....

Private Sub DoYourThing(rst As DAO.Recordset)
...
With rst.Fields("Foo") ' Is typesafe in With ... as opposed to rst!Foo
If .Value > 500 Then .Value = 500
End With
...
End Sub
Nov 12 '05 #4

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:pu********************************@4ax.com:
On Tue, 02 Mar 2004 22:54:18 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:79********************************@4ax.co m:
Oh - don't close a recordset clone, just set its reference to
Nothing. The general rule is - If you .Open it, then .Close it.
If you don't .Open it, don't .Close it.
Better yet, don't use a recordset variable, but a WITH structure:

With Forms!frmMaster.RecordsetClone
[do your thing]
End With

Then you don't have to worry about whether you need to set to
Nothing or Close what you've used.


Well, you still need to worry about whether to .Close it. . .


Not at all.

You wouldn't close it if you didn't use it, so you don't need to
close if you use it, either.
. . . - don't
:) If .Close was required, it would need to go before the End
With. . . .
That makes no sense whatsoever.

Aside from that fact that you *can't* close it, if you *could*, it
would destroy the structure, as in:

With Forms!frmWhatever
[do your thing]
DoCmd.Close acForm, "frmWhatever"
End With

That will obviously cause an error, because it's bad thinking.

Of course, at some point you may decide to close the form, but
*that's because you opened it*.
. . . I second the motion that With blocks are good for stuff
like this, just avoid long block or nesting because it gets really
hard to read. . .
Well, I don't believe you should ever nest With blocks (can you even
do it?), but I've seen lots of people post code using a recordset
variable to work with a form's recordsetclone, and it has always
seemed to me to be a very dumb thing to do when you've got something
that works much better and is clearer.
. . . Procedure calls are a good alternative to nested
With blocks...

...
DoYourThing Forms!frmMaster.RecordsetClone
...

Private Sub DoYourThing(rst As DAO.Recordset)
...
With rst.Fields("Foo") ' Is typesafe in With ... as opposed
to rst!Foo
If .Value > 500 Then .Value = 500
End With
...
End Sub


You're implicitly passing the recordset ByRef (since that's the
default), so that's going to be fine.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #5

P: n/a
Not sure if this helps...

But in some NT environments you need to issue a

Set myRecordset = Nothing

else Access won't close properly.
--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.78...
Steve Jorgensen <no****@nospam.nospam> wrote in
news:pu********************************@4ax.com:
On Tue, 02 Mar 2004 22:54:18 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:79********************************@4ax.co m:

Oh - don't close a recordset clone, just set its reference to
Nothing. The general rule is - If you .Open it, then .Close it.
If you don't .Open it, don't .Close it.

Better yet, don't use a recordset variable, but a WITH structure:

With Forms!frmMaster.RecordsetClone
[do your thing]
End With

Then you don't have to worry about whether you need to set to
Nothing or Close what you've used.


Well, you still need to worry about whether to .Close it. . .


Not at all.

You wouldn't close it if you didn't use it, so you don't need to
close if you use it, either.
. . . - don't
:) If .Close was required, it would need to go before the End
With. . . .


That makes no sense whatsoever.

Aside from that fact that you *can't* close it, if you *could*, it
would destroy the structure, as in:

With Forms!frmWhatever
[do your thing]
DoCmd.Close acForm, "frmWhatever"
End With

That will obviously cause an error, because it's bad thinking.

Of course, at some point you may decide to close the form, but
*that's because you opened it*.
. . . I second the motion that With blocks are good for stuff
like this, just avoid long block or nesting because it gets really
hard to read. . .


Well, I don't believe you should ever nest With blocks (can you even
do it?), but I've seen lots of people post code using a recordset
variable to work with a form's recordsetclone, and it has always
seemed to me to be a very dumb thing to do when you've got something
that works much better and is clearer.
. . . Procedure calls are a good alternative to nested
With blocks...

...
DoYourThing Forms!frmMaster.RecordsetClone
...

Private Sub DoYourThing(rst As DAO.Recordset)
...
With rst.Fields("Foo") ' Is typesafe in With ... as opposed
to rst!Foo
If .Value > 500 Then .Value = 500
End With
...
End Sub


You're implicitly passing the recordset ByRef (since that's the
default), so that's going to be fine.

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

Nov 12 '05 #6

P: n/a
On Wed, 03 Mar 2004 01:54:44 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:pu********************************@4ax.com :
On Tue, 02 Mar 2004 22:54:18 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:79********************************@4ax.com :

Oh - don't close a recordset clone, just set its reference to
Nothing. The general rule is - If you .Open it, then .Close it.
If you don't .Open it, don't .Close it.

Better yet, don't use a recordset variable, but a WITH structure:

With Forms!frmMaster.RecordsetClone
[do your thing]
End With

Then you don't have to worry about whether you need to set to
Nothing or Close what you've used.
Well, you still need to worry about whether to .Close it. . .


Not at all.

You wouldn't close it if you didn't use it, so you don't need to
close if you use it, either.
. . . - don't
:) If .Close was required, it would need to go before the End
With. . . .


That makes no sense whatsoever.

Aside from that fact that you *can't* close it, if you *could*, it
would destroy the structure, as in:

With Forms!frmWhatever
[do your thing]
DoCmd.Close acForm, "frmWhatever"
End With

That will obviously cause an error, because it's bad thinking.

Of course, at some point you may decide to close the form, but
*that's because you opened it*.


I'm confused why you're confused. There are object models such as ADO in
which one can create an object using New, open it after creating it, and close
it all while it still exists as an object. In our particular case, it is
important to understand not to close the object, but if was an object that
needed to be .Close-d to be cleaned up properly, that would have to happen
before it went out of scope - before End With. This is exactly what one would
have to do, for instance, in the case of With New DAODB.Recordset ... which is
a perfectly reasonable thing to do.
. . . I second the motion that With blocks are good for stuff
like this, just avoid long block or nesting because it gets really
hard to read. . .


Well, I don't believe you should ever nest With blocks (can you even
do it?), but I've seen lots of people post code using a recordset
variable to work with a form's recordsetclone, and it has always
seemed to me to be a very dumb thing to do when you've got something
that works much better and is clearer.


I presumed we were in agreement on that, just thought I'd mention it for
posterity.
. . . Procedure calls are a good alternative to nested
With blocks...

...
DoYourThing Forms!frmMaster.RecordsetClone
...

Private Sub DoYourThing(rst As DAO.Recordset)
...
With rst.Fields("Foo") ' Is typesafe in With ... as opposed
to rst!Foo
If .Value > 500 Then .Value = 500
End With
...
End Sub


You're implicitly passing the recordset ByRef (since that's the
default), so that's going to be fine.


I don't think an object can be passed by value in any case. When I'm passing
object references, I never bother with explicit ByRef. I do use ByRef
explicitly as documentation for primitive type variables if the procedure may
change the value for use by the calling procedure.
Nov 12 '05 #7

P: n/a
On Wed, 03 Mar 2004 03:15:00 GMT, "Bradley" <br*****@REMOVETHIScomcen.com.au>
wrote:
Not sure if this helps...

But in some NT environments you need to issue a

Set myRecordset = Nothing

else Access won't close properly.


We're aware of that, and it's not only NT environments, but the End With
performs the same operation as setting and explicit reference = Nothing. The
With block is somewhat preferable in this case, because you can't compile the
code without an End With, so you can't forget to release the variable. If you
use only With Blocks and passing new objects directly to function calls, then
it is also impossible to release the objects in the wrong order.
Nov 12 '05 #8

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:fh********************************@4ax.com:
but the End With
performs the same operation as setting
and [an] explicit reference = Nothing.


Perhaps, I am misunderstanding! Could you give a clear example of a situation
where "End With" releases an Object Variable and is equivalent to "Set
ObjectPointer = Nothing"?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #9

P: n/a
On 3 Mar 2004 07:35:09 GMT, Lyle Fairfield <Mi************@Invalid.Com> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:fh********************************@4ax.com :
but the End With
performs the same operation as setting
and an explicit reference = Nothing.


Perhaps, I am misunderstanding! Could you give a clear example of a situation
where "End With" releases an Object Variable and is equivalent to "Set
ObjectPointer = Nothing"?


In this particular discussion, something like...

...
With Me.RecordsetClone
.FindFirst "Foo='ABC'"
If Not .NoMatch Then Me.Bookmark = Bookmark
End With ' < Temp. ref. to recordset from Me.RecordsetClone is released.
...

Or

...
DoSomethingWithRst Me.RecordsetClone
' Temporary reference is released after procedure return.
...
Nov 12 '05 #10

P: n/a
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in
news:U4*****************@news-server.bigpond.net.au:
Not sure if this helps...

But in some NT environments you need to issue a

Set myRecordset = Nothing

else Access won't close properly.


That is not true for WITH structures, because you haven't SET a
recordset variable to point to a recordset.

This is precisely the point of confusion that I think using WITH
avoids -- it's not a recordset variable, so you don't have to worry
about whether or not you should close it or set it to Nothing.

RecordsetClone is an existing property of a form, one that is there
whether you use it or not, and that you can not close even if you
try.

And that's why a WITH structure is preferable to creating a
recordset variable, setting it to point to the RecordsetClone, then
setting the variable to Nothing.

You get all the benefits in terms of ease of reference to the object
with none of the overhead of having to deal with the problems of
recordset variables not being released properly when they go out of
scope.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #11

P: n/a
On Wed, 03 Mar 2004 20:42:51 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in
news:U4*****************@news-server.bigpond.net.au:
Not sure if this helps...

But in some NT environments you need to issue a

Set myRecordset = Nothing

else Access won't close properly.
That is not true for WITH structures, because you haven't SET a
recordset variable to point to a recordset.

This is precisely the point of confusion that I think using WITH
avoids -- it's not a recordset variable, so you don't have to worry
about whether or not you should close it or set it to Nothing.


Again, I only 1/2 agree with this. If an object needs its .Close method
called for clean-up before it is released, and does not always clean itself up
when it goes out of scope, then it needs its .Close method called before it is
released to clean it up before it goes out of scope. Using a With block does
not magically make this unnecessary.

If this were...

...
With dbs.OpenRecordset("SELECT * FROM tblFoo Where Bar=123")
<Do some stuff>
.Close ' < This is important!
End With
...

If you -do- .Open it, then you -do- need to .Close it when you're talking
about a DAO object. The only reason you would not .Close it is if it is a
clone since clones are not supposed to be .Close-d.
RecordsetClone is an existing property of a form, one that is there
whether you use it or not, and that you can not close even if you
try.

And that's why a WITH structure is preferable to creating a
recordset variable, setting it to point to the RecordsetClone, then
setting the variable to Nothing.

You get all the benefits in terms of ease of reference to the object
with none of the overhead of having to deal with the problems of
recordset variables not being released properly when they go out of
scope.


I agree it solves the problem having to remember to set it = Nothing, and
that's a very good thing.
Nov 12 '05 #12

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:uk********************************@4ax.com:
On Wed, 03 Mar 2004 20:42:51 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in
news:U4*****************@news-server.bigpond.net.au:
Not sure if this helps...

But in some NT environments you need to issue a

Set myRecordset = Nothing

else Access won't close properly.


That is not true for WITH structures, because you haven't SET a
recordset variable to point to a recordset.

This is precisely the point of confusion that I think using WITH
avoids -- it's not a recordset variable, so you don't have to
worry about whether or not you should close it or set it to
Nothing.


Again, I only 1/2 agree with this. If an object needs its .Close
method called for clean-up before it is released, and does not
always clean itself up when it goes out of scope, then it needs
its .Close method called before it is released to clean it up
before it goes out of scope. Using a With block does not
magically make this unnecessary.


We're talking past each other.

I'm referring to RecordsetClone, and only RecordsetClone.

I've repeatedly seen what is, in my opinion, really stupid code
posted here that looks like this:

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
[do whatever you want with the recordsetclone]
Set rst = Nothing

That's just dumb, in my opinion, and in that case, a WITH block is
obviously superior.

In cases where you are actually creating a new recordset, you're
dealing with a completely different set of issues, and that's not
what I thought we were talking about.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #13

P: n/a
On Wed, 03 Mar 2004 21:37:33 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:uk********************************@4ax.com :
On Wed, 03 Mar 2004 20:42:51 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in
news:U4*****************@news-server.bigpond.net.au:

Not sure if this helps...

But in some NT environments you need to issue a

Set myRecordset = Nothing

else Access won't close properly.

That is not true for WITH structures, because you haven't SET a
recordset variable to point to a recordset.

This is precisely the point of confusion that I think using WITH
avoids -- it's not a recordset variable, so you don't have to
worry about whether or not you should close it or set it to
Nothing.


Again, I only 1/2 agree with this. If an object needs its .Close
method called for clean-up before it is released, and does not
always clean itself up when it goes out of scope, then it needs
its .Close method called before it is released to clean it up
before it goes out of scope. Using a With block does not
magically make this unnecessary.


We're talking past each other.

I'm referring to RecordsetClone, and only RecordsetClone.

I've repeatedly seen what is, in my opinion, really stupid code
posted here that looks like this:

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
[do whatever you want with the recordsetclone]
Set rst = Nothing

That's just dumb, in my opinion, and in that case, a WITH block is
obviously superior.

In cases where you are actually creating a new recordset, you're
dealing with a completely different set of issues, and that's not
what I thought we were talking about.


Yes, we seem to have been talking past each other. It did seem to me, though
that you were saying that simply using a With block means you don't have to
worry about whether something needs .Close called, and it's good to make sure
people reading this know that only the programmer can deal with whether .Close
should or should not be called in a given case.
Nov 12 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.