VBA: Cannot open anymore tables

[Glenn Davy]

Q. How can I 'see' the number of table id's access is using for tracking
recordsets?

Details:
I have some ado code that itterates through a recordset and calls a
procedure on each record. This procedure calls a hierachy of other
subs/functions etc which _do_ instantiate several custom objects (class
modules), recordsets, both ado and dao at different times. Eventually the
code breaks with the error number -2147467259, (though has also been
broken with error number 3048 - I assume whether it breaks during an ADO
or DAO operation) and the message "Cannot open any more tables"

I believe (I've checked several times and am about to check again) that
every object (custom or access) is closed and set = nothing, so the
number of table id's at any given time shouldn't be extraordinary.

In theory, if everything is being closed and all garbage being collected,
I should be able to iterate through an infinite number of records and
execute this procedure, knowing that when the procedure finishes, things
are as they were when it started, but clearly they're not, and after about
250 records I get the error.

IF I can peek at some attibute of a recordset, or of the connection or of
something that gives me a hook into whats happening 'under the bonnet',
I can see when in the process the 'count' of table id's starts to climb,
I could then identify the problem.

any ideas?

Thx
Glenn

[Pieter Linden]

Glenn Davy <gS***********@ tpg.com.au> wrote in message news:<pa******* *************** ******@tpg.com. au>...

Q. How can I 'see' the number of table id's access is using for tracking
recordsets? <SNIP>
IF I can peek at some attibute of a recordset, or of the connection or of
something that gives me a hook into whats happening 'under the bonnet',
I can see when in the process the 'count' of table id's starts to climb,
I could then identify the problem.

any ideas?

Thx
Glenn

How about adding some code in your modules that does something like
giving an object's state. I'm pretty sure I've seen Steve Jorgensen
post this before. If you wanted, you could create a global integer of
open objects and a custom collection so you could add/remove items
from it. Whenever there was a change, you could output all the open
items/newly closed items to either a text file or to the debug window.
Something like

Variable_In_Use Calling_Module InstantiateDate Time
DestroyDateTime

Otherwise, you'll probably have to set watches and breakpoints
everywhere.

[Glenn Davy]

On Fri, 22 Oct 2004 04:47:15 -0700, Pieter Linden wrote:

Glenn Davy <gS***********@ tpg.com.au> wrote in message news:<pa******* *************** ******@tpg.com. au>...

Q. How can I 'see' the number of table id's access is using for tracking
recordsets? <SNIP>

IF I can peek at some attibute of a recordset, or of the connection or of
something that gives me a hook into whats happening 'under the bonnet',
I can see when in the process the 'count' of table id's starts to climb,
I could then identify the problem.

any ideas?

Thx
Glenn

thx for replying Pieter
I like the idea of the global collection of custom objects because I could
add code in the initialize and terminate methods to maintain the
collection - I'm not sure how I'd use this for ado/access objects though,
without adding code each time an object is opened,closed or set =nothing.

Glenn

How about adding some code in your modules that does something like
giving an object's state. I'm pretty sure I've seen Steve Jorgensen
post this before. If you wanted, you could create a global integer of
open objects and a custom collection so you could add/remove items from
it. Whenever there was a change, you could output all the open
items/newly closed items to either a text file or to the debug window.
Something like

Variable_In_Use Calling_Module InstantiateDate Time
DestroyDateTime

Otherwise, you'll probably have to set watches and breakpoints
everywhere.

[Michael \(michka\) Kaplan [MS]]

"Glenn Davy" <gS***********@ tpg.com.au> wrote in message
news:pa******** *************** *****@tpg.com.a u...

Q. How can I 'see' the number of table id's access is using for tracking
recordsets?

There is no way to get the internsl count of TABLEIDs that have been opened.
You can get estimates by looking at how many objects you have opened but on
the whole its better to just work hard to open fewer objects....
--
MichKa [MS]
NLS Collation/Locale/Keyboard Technical Lead
Globalization Infrastructure and Font Technologies
Windows International Division

This posting is provided "AS IS" with
no warranties, and confers no rights.