Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Server 2008 Scheduled Task - Access Macro suddenly time out

Posted on 2013-12-03
4
Medium Priority
?
651 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 2000 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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

886 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