Access 2007 - Overflow + error 2950 when running a macro

[Donberti]

Hi everyone,

I am not a programmer or an expert of Access.
My OS is MS xp professional sp3 and my access was installed with the Office
package 2007.
I am seeing a strange behaviour on my DB when running a macro (arguments:
_createContractTable; Datasheet; Edit).

I extract data from Siebel Agreements (.csv file) and I run access to change
and filter some fields. Until last Thursday I had no problem, but now the
macro written by a colleague is not able to create an output (.xlsx) anymore.
Microsoft tells me that I should check and change the security options in
Access (bulletin http://support.microsoft.com/kb/931407/en-us) - no help.

The point is that the macro works with the data up to January 15. I think it
has to do with Access constraints related to macros (i.e. if the files are
too big or something similar). I tried to run the macro step by step - but no
further indication on the matter. I see only at a certain point a window with
the macro name, the condition (true), the action name (OpenQuery) the related
arguments and the error n. (2950).

Is there a way to export the steps or to see more of the eventual problem in
Access? I cannot program in VBA though.

Thank you for any indication,

Best regards

 

[Clifford Bass]

Hi Donatella,

What happens if you open the query directly from the Navigation panel?
Do you get the overflow error. If so, go into design view of the query and
look for any place where there might be division by zero. If you find it it
will need to be fixed to deal with the condition. So if somwhere there is
something like this:

[SomeField]/[AnotherField] you might need go change it to:

IIf([AnotherField] = 0, "Division by Zero!", [SomeField]/[AnotherField])

You can replace the "Division by Zero!" part by something else,
depending on what fits your situation; such as maybe 0 or Null.

Hope that helps,

Clifford Bass

 

[AccessVandal via AccessMonster.com]

Is your DB in a Trusted Locations? Macros does not work if it is not in
trusted location.

 

[Donberti]

Hello,

yes - moreover if the data of jan 15 show no problem and the data of jan 16
cannot be accessed (I mentioned this I think) it cannot be a problem of
trusting a location or security. I read already the KB documents related and
set up a new trusted path, no difference at all.

Thank you, best regards,

 

[Donberti]

Hello Clifford,

thank you. Through your tip (to use Navigator) I could restrict the query
that is not working (and the macro calls up a table which is created by that
query).

What I did so far is to compare the sql statements when I run access on both
..csv files (the one working and the one with problems). No sign of errors,
which is quite odd. I will check with the guy who wrote these statements, not
diving in these waters.. the statements are quite long (and difficult to me).

It would be helpful though to know a debugging tool for access. It would be
great if you know about any. I tried a button in access ( step by step in
designer view I guess) but it does not show what is causing the problem, I
think this can be done with VBA but it would be for me like writing in
Japanese.

Best regards and have a nice day,
Donatella

Thank you anyway. Let's see what happens,
Best regards
Donatella
--
Donatella

Hi Donatella,

What happens if you open the query directly from the Navigation panel?
Do you get the overflow error. If so, go into design view of the query and
look for any place where there might be division by zero. If you find it it
will need to be fixed to deal with the condition. So if somwhere there is
something like this:

[SomeField]/[AnotherField] you might need go change it to:

IIf([AnotherField] = 0, "Division by Zero!", [SomeField]/[AnotherField])

You can replace the "Division by Zero!" part by something else,
depending on what fits your situation; such as maybe 0 or Null.

Hope that helps,

Clifford Bass

Hi everyone,

I am not a programmer or an expert of Access.
My OS is MS xp professional sp3 and my access was installed with the Office
package 2007.
I am seeing a strange behaviour on my DB when running a macro (arguments:
_createContractTable; Datasheet; Edit).

I extract data from Siebel Agreements (.csv file) and I run access to change
and filter some fields. Until last Thursday I had no problem, but now the
macro written by a colleague is not able to create an output (.xlsx) anymore.
Microsoft tells me that I should check and change the security options in
Access (bulletin http://support.microsoft.com/kb/931407/en-us) - no help.

The point is that the macro works with the data up to January 15. I think it
has to do with Access constraints related to macros (i.e. if the files are
too big or something similar). I tried to run the macro step by step - but no
further indication on the matter. I see only at a certain point a window with
the macro name, the condition (true), the action name (OpenQuery) the related
arguments and the error n. (2950).

Is there a way to export the steps or to see more of the eventual problem in
Access? I cannot program in VBA though.

Thank you for any indication,

Best regards

 

[Clifford Bass]

Hi Donatella,

Is the error a stack overflow or a numeric overflow? Usually when a
query has trouble it is due either to the data with which it is trying to
deal or it is too complicated. Is the data that does not work larger in
quantity? Unfortunately, to my knowledge, there is no debugging tool for SQL
statements. You usually have to look at the data that is causing trouble
and/or start breaking the statement into smaller parts to try to isolate
where the trouble is. My guess is that the data may be the issue.
Especially if succeeding day's data works again. Have you had the chance to
test that? You could try breaking the data into half and then quarters; see
if that helps.

Good Luck,

Clifford Bass

 

[AccessVandal via AccessMonster.com]

It just to be sure as you didn't indicate that.

You mentioned a csv file for importing, is it possible that this file not in
trusted location? Than again it's just my guess. It's impossible for us to
see what's wrong in that file like comma delimited, etc. You'll have to look
into the file itself to see what's truncating the data.

 

[Donberti]

Hi,

I am posting the solution to Clifford Bass. And - sorry to say - it has
absolute nothing to do with trusts or security ..

Thank you and have a good day,
Donatella

 

[Donberti]

Hi Clifford,

The error was caused by a single field in the extract I load in Access
(which is quite big). In this field there is the combination of
number+E+number. In one of the table there is a val function which should run
and read the field as letter BUT "E" is also a math function (did not
really know) - the val function has a problem with this "E". This was causing
the overflow. So you were right.

I am checking in the next day what is happening. Of course I would have
never found it. I got help on this. I think I'll go for a brandy now.

Sorry if I did not answer your previous post. Was kind of stressed.
Best regards and have a good day,
Donatella

 

[Clifford Bass]

Hi Donatella,

Yes, the number with the E and another number is a number in scientific
notation--apparently a rather large number in your case.

Glad you were able to figure it out with help! No problem on the
non-answer. Good Luck!

Clifford Bass

 

[AccessVandal via AccessMonster.com]

Good to hear, thanks for posting back.

Hi,

I am posting the solution to Clifford Bass. And - sorry to say - it has
absolute nothing to do with trusts or security ..

Thank you and have a good day,