Code randomly stopping when automating Excel from Access (1 Viewer)

Hi all

I wonder if anyone's experienced this before...

I've got a reasonably large database (circa 150 tables, several of which have over 10 million records). This is held in SQL Server 2012, hosted in the cloud (but not SQL Azure).

The tables are linked to an Access front end, using SQL Server Native Client. Access version is 2007, it's an MDB file, and Excel is also 2007.

Each month my client has a big reporting task, which essentially involves:
1. Using various stored procs to move data from live relational tables to temp reports tables with a flatter structure
2. Looping through each customer that my client has (approx. 400), and for each one,
3. Creating an Excel file with around 10 sheets - one for sales, one for internet sales, one for stock movements etc, plus an income summary sheet, and expenses summary sheet and a statement.
4. This Excel file is saved twice - once in a folder "By month" and once "By customer" to make searching through these thousands of files easier.
5. If the reporting process is marked as "Final and confirmed", these Excel files are then emailed to the customers as part of the process, using Outlook automation.

The Excel files can be anything from 30k to 100mb (although there are only a couple of 10mb + files, and they get sent manually via YouSendIt.

As the code loops through the labels, on-screen feedback is provided along the lines of "Processing ABC Industries..." so that my client can see how far this has got, as the total process (including the emailing) can take a couple of hours. More detailed feedback regarding which sheet is being created is logged into the Immediate Window so that I can keep an eye on it if needs be.

All ran well until the past 3 or 4 months. but I'm now finding that the code seems to randomly stop. I'll be watching the Immediate window, and notice that it'll be saying "Creating Statement sheet for ABC Industries" for example for 5 minutes - and this is not a sheet with any real data (and nothing at all coming from a SQL table), just some basic Excel calculations and headings. So, I'll hit CTRL-Break, and step into the code, and it will run absolutely fine, swiftly moving to the next customer's data and continuing at full speed. Then, 20 minutes later, maybe, it'll stop again.

It's really frustrating, because I can see no problems with the code, and the next time I run it, it'll stop on completely different random points.

I've done the usual compact & repair, decompile / recompile and even copying all the objects into a new database file.... but no good.

Any ideas? I'd be really grateful!

Thanks
Andrew

andrewmrichards said:

... I'll hit CTRL-Break, and step into the code, and it will run absolutely fine, swiftly moving to the next customer's data and continuing at full speed. Then, 20 minutes later, maybe, it'll stop again. ..

Click to expand...

It some times help to put in a DoEvents in your code, one just before the code line where it stops and one just behind the code line.

Yeah can very well be a display only issue that a doevents will fix.

Dont add to many though, doevents can slow down your processing considerably, particularly if done in a loop

Hi

Thanks for the suggestion.

The problem I have is that it's never done it on the same line twice. For example, I've just tried now to run this reporting tool, and it's stopped 3 times in total, in a process that lasts about 2 hours. Each time it's been on a different code line - twice in one procedure (different lines) and once in a completely different procedure.

The only thing that these lines have in common is their simplicity! They say things along the lines of "intRows=intRows+1" or "xlRng.value='Total'"

So where do I put the DoEvents? It's also not a display only issue - when I hit CTRL -Break, the code is halted on the iteration which was last reported in the immediate window (ie it hasn't gone on 4 more customers, but not updated the immediate window).

Thanks for your help though, I really appreciate it.

Andrew

doevents just put it at any line, but at large enough intervals

If it is a visual thingy, that is to be solved by DoEvents, it should (when you hit CTRL Break) have continued on from the point where is was and done more work even updated the immediate window.

Do your stamps to the immediate window include Now() or some time stamp, just to make sure it isnt just a trick of the brain?

Okey-dokey - I've sprinkled a few DoEventses and I'll also log the times with Now() - that's a good thought. I don't think it's just a brain fart, but you never know.


About to run another test, so I'll keep you updated!

Thanks
Andrew

It could also help us getting ideas when we could see the Procedures/Sub code, where it stops, (not only one code line, but the whole procedure code)!

Thanks to all who posted replies to this one - and sorry it's been a while! I've finally been able to do some more testing at that client, and the addition of a few DoEvents statements does seem to have made it a lot more stable!

Many thanks again.

Andrew

Good you got it solved.