Access macro intermittantly won't run

  • Thread starter Thread starter Jeff Vandervoort
  • Start date Start date
J

Jeff Vandervoort

Access "guru" at one of my clients has an Access 2000 MDB file with a fair
number of macros in it. One of them works fine when it works, other times,
you click the button, see a wait cursor for a few seconds, then nothing. No
error message.

We've tried this on lots of machines under several user accounts and can't
find any sort of pattern. On some machines no user can run it, on some
machines only certain users (different on each machine) can run it, on some
machines any user can run it. The network is an NT Domain with roaming
profiles, so the user account and profile is identical on all machines
despite different results on different machines at different times.

System is a mix of Access 2000 and Access 2002 machines on Win 2000 SP4 and
Win XP SP1. There is no pattern with OS and Access versions. The application
is developed on an Access 2002/Win XP SP1 box.

On one recent test, we found it would not run several times, but then it
just started running in the same Windows session as it had not been running
in seconds before. Generally we've not seen that.

To me, this last event in particular points to a corruption in the MDB file.
Compact and repair halved the size of the file, but did not fix the problem.
Any idea how to fix it?
 
WIthout knowing what the macro is doing, how you call it, what else might be
running at the time it's called, etc., I tentatively agree with your idea
about corruption.

You might try creating a new macro with same steps as current one, delete
the old one, and rename the new one the same as the old one to see if that
works.

You also could try importing all the objects into a new database.


Also see Tony Toews' web page for more info about corrupted databases:

http://www.granite.ab.ca/access/corruptmdbs.htm
 
Thanks, Ken. I will forward this info to the developer.

I'll also ask if it's OK to post the macro. I have only very limited
knowledge of Access and what her app is doing, but I will tell you what I do
know: It's launched from a button on an Access form. It prompts for user
input, then runs a query against a SQL database. The results are exported to
a CSV. An Excel template is called that opens the CSV and formats it using a
VBA module in the Excel template.

When it runs, it runs perfectly. When it fails, it doesn't even post the
user input boxes. You just see a wait cursor for a few seconds and that's
all you get.
 
Back
Top