UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Effect Of Recordsetclone On "cannot Open Any More Databases" Error, Any Version    
 
   
ngins
post Jan 14 2020, 05:30 PM
Post#1



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


I was looking at Allen Browne's reply to someone having a problem with "Cannot open any more databases," and in his reply, one of the culprits that he notes that use connections is:

QUOTE
e) Referring to the RecordsetClone of forms (even where you do set the
objects to nothing.


I found this to be a bit confusing. Is he saying that anytime you refer to RecordsetClone, you use up a database connection? For example, if I do:

CODE
Dim rs as recordset
Set rs = Me.Recordsetclone
rs.FindFirst "X=1"
me.Bookmark = rs.Bookmark
Set rs = nothing


then he seems to be saying that that keeps a connection open, even though I set rs = nothing.

That seems to be a bit puzzling, as it would imply that we should never use the Recordsetclone object.

Here's the post here he wrote that: https://bytes.com/topic/access/answers/2086...-more-databases

Thoughts?
This post has been edited by ngins: Jan 14 2020, 05:31 PM

--------------------
Neil
Accessing since '96
Go to the top of the page
 
cheekybuddha
post Jan 14 2020, 05:42 PM
Post#2


UtterAccess Moderator
Posts: 12,112
Joined: 6-December 03
From: Telegraph Hill


I didn't see any reference to RecordsetClone in that link - did I miss something?

--------------------


Regards,

David Marten
Go to the top of the page
 
ngins
post Jan 14 2020, 05:58 PM
Post#3



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


Allen Browne's reply is the first reply to that post. It is his subpoint (e) in his reply that I'm referring to .

Thanks.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
cheekybuddha
post Jan 14 2020, 06:20 PM
Post#4


UtterAccess Moderator
Posts: 12,112
Joined: 6-December 03
From: Telegraph Hill


Thanks for the pointer!

I'd be more inclined to check for domain functions in queries as the root of your problem

--------------------


Regards,

David Marten
Go to the top of the page
 
ngins
post Jan 14 2020, 06:32 PM
Post#5



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


Right. I'm just saying that from a theoretical point of view, what he saying doesn't make a lot of sense to me. I'm just trying to understand it. Is he really saying that anytime you refer to the recordsetclone object that you use up a database connection? That seems to be what he saying, no?

--------------------
Neil
Accessing since '96
Go to the top of the page
 
cheekybuddha
post Jan 14 2020, 06:43 PM
Post#6


UtterAccess Moderator
Posts: 12,112
Joined: 6-December 03
From: Telegraph Hill


Yes, but I think this is different type of connection from a user connected to your db - an internal connection within the db.

I may be wrong, and I'm pretty sure it will be released once the form is closed.

--------------------


Regards,

David Marten
Go to the top of the page
 
ngins
post Jan 14 2020, 07:37 PM
Post#7



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


Yes, that's what we're discussing here -- internal database connections, the running out of which causes the "Could not open any more databases" error.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
moke123
post Jan 14 2020, 07:57 PM
Post#8



Posts: 1,377
Joined: 26-December 12
From: Berkshire Mtns.


Heres a tool to count the connections.
Just import the form into your DB.

Attached File  CountOfDBConnections.zip ( 25.4K )Number of downloads: 2
Go to the top of the page
 
ngins
post Jan 14 2020, 08:00 PM
Post#9



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


Thanks!

--------------------
Neil
Accessing since '96
Go to the top of the page
 
theDBguy
post Jan 14 2020, 09:20 PM
Post#10


UA Moderator
Posts: 77,336
Joined: 19-June 07
From: SunnySandyEggo


Hi Neil. I think any time you try to retrieve any information (data) from the BE (tables), you are opening a connection. So, if the information is coming from the BE, it needs/uses a connection to get it. I think what Allen may have meant is if you set a recordset/clone and don't "Close" it, you could be using up a connection, that didn't get released, even if you set the rs variable to Nothing.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ngins
post Jan 14 2020, 10:19 PM
Post#11



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


Well, the recordsetclone is a persistent object that the user doesn't create. So, since I don't open the recordsetclone, I wouldn't close it either. I just set a reference to it and then set that object variable to Nothing when I'm done. If you try to close a form's Recordsetclone object, Access just ignores the command.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
theDBguy
post Jan 14 2020, 10:56 PM
Post#12


UA Moderator
Posts: 77,336
Joined: 19-June 07
From: SunnySandyEggo


I'm no expert, but my guess is instantiating a recordsetclone is probably the same as opening it. But, if you try to explicitly close a recordsetclone and get an error, then I guess I don't get what Allen was saying either.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ngins
post Jan 15 2020, 05:27 AM
Post#13



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


I tested this with a table and form that had a button that used debug.print to write the recordcount of the recordsetclone object to the Immediate window.

First I had the code use the recordsetclone object directly -- getting the recordcount, then executing:

CODE
Me.RecordsetClone.Close


and then getting the recordcount again. The results were the same. Executing a Close command on the Recordsetclone object had no effect.

Then I assigned the recordsetclone object to an object variable, "rs." I then performed the same steps on "rs" -- printing the recordcount value, and then closing "rs," and then printing the recordcount value again.

And, again, the results were the same: in both cases the recordcount value was printed, even after I executed:

CODE
rs.Close


So closing a recordsetclone object has no effect, even when it is done through an object variable and you try to close the object variable. Access just ignores it.

Of course, setting rs = nothing broke the association, so that had an effect of course. But using Close on the recordset had no effect.


--------------------
Neil
Accessing since '96
Go to the top of the page
 
ngins
post Jan 15 2020, 05:35 AM
Post#14



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


OK, after writing that last reply, I had a brilliant idea: why not just test it? Moke123 above shared that utility for counting connections, so I put it to the test in the simple db I created for testing, noted in the last reply.

  • Initial state: 253 connections available
  • Open the form with table connected: 251 connections available (not sure why just opening a form bound to a local table uses up two connections, when local tables are supposed to use one connection).
  • Execute code to print count of recordsetclone object: 250 connections available
  • Execute code again to print count of recordsetclone object: 250 connections available
  • Close form: 253 connections available


So we can see that Allen Browne was right: once you reference the recordsetclone object of a form, it uses a database connection that wasn't previously used.

And referencing that object multiple times doesn't use any additional connections.

And that connection stays in use until the form is closed.

So that's good to know. Probably not a big deal, since the connection is recouped once the form is closed, and it never uses more than one connection per form. Still, that's good to know.

--------------------
Neil
Accessing since '96
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th January 2020 - 03:38 PM