Queries suddenly failing

[Guest]

Users run a macro that contains approx. 13 queries that run consecutively
until complete. The only criteria used is from 2 fields that are on a form
that remains open while the macro is running.
We have a front-end application using Access 2000,2002, 2003 (depending on
workstation) and the back-end is still Access 2.0 (size of db is approx
68,000KB). We have been doing this same process for close to 7 years.
On 3/17/06 it suddenly started to fail, because a counter that copied into
another table/field hit the max integer length the field was set for. I
changed the responsible field type to long integer, and things are still not
working correctly. The results are sometimes sucessful, sometimes partially
successful, and I can't pinpoint what is happening. I did compact/repair the
database after the change.
The only difference I can see other than the counter/field issue is that we
have increaser our user base for this application from 5 users to 10. We have
another database application that mirrors the problematic one (it is used for
a single territory) that is running fine. It is much smaller in size, and
only has 2 users. I have tried running each query seperately to see where it
is failing, but it's dfferent each time. Can it possibly be a user overload
type problem - or is my backend database too large?

 

[Guest]

2.0 has an MDB file size limitation of 1GB so that shouldn't be it.

I'd wonder if the code that increments the field is Dim'ed as an Integer
also. That could cause problems.

I'd recommend stepping through the macro, not just each query, in turn until
you find which one is causing the problem.

One weakness of macros is a lack of error handling. Convert the macro to a
function in a module and you might find the problem then.

I'd also wonder about any data that you get from an external source starting
on that date. Maybe they are doing something like putting text in a number
field or something else strange.

 

[Guest]

This afternoon another query went kaput on a user's workstation. Where a list
of calls typically appear on a form in chronological order, today they are
appearing in a random order when that contact's form is brought up. Hitting
the refresh button brings them into proper order. Going out and coming back
in produces the same result, until you refresh. I copied a previous copy of
the front-end database app onto this person's workstation, and it appears to
have taken care of the problem (for now, anyways). I feel like there's a lot
more going on here that I know. Could it be something more major than just
the macro thing?
Thank you for your help.