Solved

Server 2008 Scheduled Task - Access Macro suddenly time out

Posted on 2013-12-03
4
645 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 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