Complex calculations and out of stack error

Hi all,

I'm developing a rather complex financial analysis & forecasting software in
MS Access 2000. In fact the app is already done, and I'm redesigning it for
better ease of use and more flexibility. The whole thing is built like a
giant spreadsheet with the important difference that circular references are
allowed. For example, assets growth can be specified either as absolute
increase or percentage, and user can change either - I'm checking for
endless recursion in code.

Anyway, I've got to the point where Out of Stack error message appears.
Apparently this is due to complex nature of calculations and formulas, I've
even added stack depth checking thru my own functions and it turns out that
at depth of 90 it hits the wall. Note that I'm specifically checking for
endless recursion in the code, and I even tracked whole call stack to find
out all calls are perfectly legit.

Now, from what I've found there's no control over stack size in A2K - can
anyone confirm or deny this? What about A2K2? Alternatively I'm considering
rewrite of my calculations in VB6 and compile it as ActiveX control for
later use from Access - will VB allow more stack depth?

Any help appreciated!

Peter

Possibly u could reduce some of processing power needed by
these calculations by storing values in table...that way
ot as much of strain will b placed on your machines
memory...
>-----Original Message-----
>Hi all,
>
>I'm developing a rather complex financial analysis &
forecasting software in
>MS Access 2000. In fact the app is already done, and I'm
redesigning it for
>better ease of use and more flexibility. The whole thing
is built like a
>giant spreadsheet with the important difference that
circular references are
>allowed. For example, assets growth can be specified
either as absolute
>increase or percentage, and user can change either - I'm
checking for
>endless recursion in code.
>
>Anyway, I've got to the point where Out of Stack error
message appears.
>Apparently this is due to complex nature of calculations
and formulas, I've
>even added stack depth checking thru my own functions and
it turns out that
>at depth of 90 it hits the wall. Note that I'm
specifically checking for
>endless recursion in the code, and I even tracked whole
call stack to find
>out all calls are perfectly legit.
>
>Now, from what I've found there's no control over stack
size in A2K - can
>anyone confirm or deny this? What about A2K2?
Alternatively I'm considering
>rewrite of my calculations in VB6 and compile it as
ActiveX control for
>later use from Access - will VB allow more stack depth?
>
>Any help appreciated!
>
>Peter
>
>
>.
>

Peter wrote:

>Hi all,
>
>I'm developing a rather complex financial analysis & forecasting software in
>MS Access 2000. In fact the app is already done, and I'm redesigning it for
>better ease of use and more flexibility. The whole thing is built like a
>giant spreadsheet with the important difference that circular references are
>allowed. For example, assets growth can be specified either as absolute
>increase or percentage, and user can change either - I'm checking for
>endless recursion in code.
>
>Anyway, I've got to the point where Out of Stack error message appears.
>Apparently this is due to complex nature of calculations and formulas, I've
>even added stack depth checking thru my own functions and it turns out that
>at depth of 90 it hits the wall. Note that I'm specifically checking for
>endless recursion in the code, and I even tracked whole call stack to find
>out all calls are perfectly legit.
>
>Now, from what I've found there's no control over stack size in A2K - can
>anyone confirm or deny this? What about A2K2? Alternatively I'm considering
>rewrite of my calculations in VB6 and compile it as ActiveX control for
>later use from Access - will VB allow more stack depth?


I don't believe there is any way to control the amount of
stack space, but I've never dug into deeply enough to know
for sure.

OTOH, I think that hitting the limit at 90 levels is caused
by the number and size of the arguments and local variables
in your procedures. You must have a ton of those things to
hit the wall so quickly.

With a single Integer argument, I don't hit the limit until
it gets over 3,800 levels deep. With 10 Variant arguments,
it gets about 1,800 levels. Using a bunch of local
variables will consume even more stack space.

I don't know if that helps you, but it should at least
provide some food for thought.
--
Marsh
MVP [MS Access]

> OTOH, I think that hitting the limit at 90 levels is caused
> by the number and size of the arguments and local variables
> in your procedures. You must have a ton of those things to
> hit the wall so quickly.

Yep. I've got ~400 class instances where each is calling another. However,
these instances are pre-allocated and initialized before any calls are made!

> With a single Integer argument, I don't hit the limit until
> it gets over 3,800 levels deep. With 10 Variant arguments,
> it gets about 1,800 levels. Using a bunch of local
> variables will consume even more stack space.
>
> I don't know if that helps you, but it should at least
> provide some food for thought.

Thanks for the insight... I also thought 90 levels is suspiciously low!

Peter

test only

"Peter" <peter@greatnowhere.com> wrote in message
news:bvlpj2$tvnus$1@ID-97500.news.uni-berlin.de...
> > OTOH, I think that hitting the limit at 90 levels is caused
> > by the number and size of the arguments and local variables
> > in your procedures. You must have a ton of those things to
> > hit the wall so quickly.
>
> Yep. I've got ~400 class instances where each is calling another. However,
> these instances are pre-allocated and initialized before any calls are
made!
>
> > With a single Integer argument, I don't hit the limit until
> > it gets over 3,800 levels deep. With 10 Variant arguments,
> > it gets about 1,800 levels. Using a bunch of local
> > variables will consume even more stack space.
> >
> > I don't know if that helps you, but it should at least
> > provide some food for thought.
>
> Thanks for the insight... I also thought 90 levels is suspiciously low!
>
> Peter
>
>