Solved

Server 2008 Scheduled Task - Access Macro suddenly time out

Posted on 2013-12-03
4
640 Views
Last Modified: 2013-12-30
Server 2008
Access 2007

I run 5 scheduled tasks consecutively each hour on my server 2008.  Each task calls a different Access 2007 DB and runs a series of macros to summarize data.  

Everything has worked fine until about 1.5 weeks ago when one task (the main one) started timing out due to reaching a 23 minute time limit that I imposed.  When I run the task manually "Run only when user is logged on" it runs perfectly in 9 minutes every time, but when it runs on schedule (or when I run it on command) using "Run whether user is logged on or not" it times out after 23 minutes and is terminated.

I thought it was obviously a permission thing, but after trying everything I can think of, I still cannot get it to run.  I have changed the user/group to other administrators, SYSTEM but it still terminates at 23 min.  Strange thing is the other 4 tasks use the same administrator (LVAPPS\administrator) and run fine, but his one will not.

I have also checked the permission of each access db and the folders they sit in ... all are identical and have full permissions for LVAPPS\administrator.

I have deleted and recreated the task several time ... no luck ... out of ideas ...

Anyone recognize this?

Max
0
Comment
Question by:MaxwellTurner
  • 2
  • 2
4 Comments
 
LVL 14

Accepted Solution

by:
luconsta earned 500 total points
ID: 39745937
Because all is happening without any user interface, you have to modify that macro (at least for a short time, for debugging purposes) so you could add some "checkpoints" that would be relevant for your problem.

For example I would create a new table in the DB for logging and somewhere in the loops (be careful not to use very tight loops) put code that would add, or modify a record in that table with a date/time stamp, number of records processed, whatever you need to get the picture.

So, refining your checkpoints you will finally get to the root cause.
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 39747386
That is a great suggestion!  I feel shame for not thinking of that.

In the end, I ended up re-creating the dbase simply by importing in all of the queries/macros/forms and relinking the tables to the backend.  Should have been simple, but I lost field names in most queries (Expr1: grrrrr) during the import.  No small job as there are almost 2000 queries.  Anyway, it worked ... something went corrupt in the original dbase I guess.

I still may try your suggestion as I still have the original dbase and am very curious what the problem was.

Thanks,

Max
0
 
LVL 14

Expert Comment

by:luconsta
ID: 39747653
Thank you for awarded points, but now I am feeling shame because I forgot to mention another thing you could try: Compact and Repair Database
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 39747657
Feel less shame ... lol ... I compact and repair everyday!

Max
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Running sum query 6 34
MS Access Tables Linking 6 43
access denied when running native SQL tool: SQLIOSIM 2 26
Access 2010 Query Syntax 5 25
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now