Results 1 to 7 of 7
  1. #1
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    62

    One module not running when executing db via Windows Task Scheduler

    I'm not sure if this question goes here, but hopefully it's a start. I've got a database that I'm trying to schedule to run each morning via Windows Task Scheduler. This database runs great when you manually execute it. The overall aim is to grab text files off of a network share, process them as needed, and create a finished report. This finished report also needs a summary tab with pivot tables. It's that specific module that creates the pivot tables which won't fire, however the rest of the database up until that point does. It just kind of hangs, no errors, but no execution.
    The reason, as best I can tell, is that it won't execute if the user is not logged on. It does seem to run fine when, in Task Scheduler, I have checked: "Run only when user is logged on", and the user is logged on, however since I can't guarantee that the user will remain logged on overnight when this needs to run, I need to use: "Run whether user is logged on or not", which is what works up until the point that it calls the module to create the pivots on the Excel report.

    I also want to be clear that the db runs plenty of VBA up until that point. There's VBA to import and export the files. All of it works fine up until the specific module that grabs the finished Excel report to create the pivot tables.

    Can anyone tell me what prevents the VBA from running in this way, and if there's a way around it?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not sure myself, but there are similar threads at the bottom of the page that might be helpful.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Possibly there is something lacking in a code variable or parameter when the user isn't logged on that you're overlooking. Or an external file/macro/code is not running/is held up because of the same reason? Or the app isn't getting a valid token from Windows because they're not logged on? Maybe post the module code for the failing operation?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    62
    Quote Originally Posted by Micron View Post
    Possibly there is something lacking in a code variable or parameter when the user isn't logged on that you're overlooking. Or an external file/macro/code is not running/is held up because of the same reason? Or the app isn't getting a valid token from Windows because they're not logged on? Maybe post the module code for the failing operation?

    Could be? The thing that is lacking here is what I'm trying to determine, if it can be corrected so that this code can run when the user isn't logged on. This computer is on a corporate domain and group policy sets when it logs out for security reasons.
    Here is the code in question. Maybe I'm going about this the wrong way (I'm no pro at this), so if there's another way to go about looping through the sheets and creating pivots on a workbook, I'm all ears!

    Code:
    Option Explicit
    Private objExcel As Excel.Application
    Private wb As Excel.Workbook
    Private ws As Excel.Worksheet
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    
    
    Function create_pt()
    Dim strFile As String
    Dim LastRow As Long, LastRange As Long, x As Long
    Dim SourceRange As Range
    Dim i As Integer
    
    
    strFile = "\\server\path\file.xlsb"
    
    
    Set objExcel = Excel.Application
    
    
    objExcel.Visible = False
    
    
    Set wb = objExcel.Workbooks.Open(strFile)
    
    
            i = Round(((wb.Worksheets.Count + 1) / 2), 0)
    
    
    For Each ws In wb.Worksheets
    
    
        If ws.Index > 1 And ws.Index <= i Then
        
            With ws
            
                LastRange = .Range("A" & Rows.Count).End(xlUp).Row
                Set SourceRange = .Range("A1:N" & LastRange)
                
                LastRow = Range("A" & Rows.Count).End(xlUp).Row
                Range("A" & LastRow + 3).Formula = ws.Name
                
                wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceRange).CreatePivotTable _
                TableDestination:=Worksheets("Summary").Range("A" & LastRow + 4), _
                TableName:="PivotTable" & ws.Index
                
                Sheets("Summary").Select
                
                With ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Facility")
                    .Orientation = xlRowField
                    .Position = 1
                End With
                ActiveSheet.PivotTables("PivotTable" & ws.Index).AddDataField ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Charges"), "Count of Charges", xlCount
                ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Count of Charges").NumberFormat = "#,##0"
                ActiveSheet.PivotTables("PivotTable" & ws.Index).AddDataField ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Charges"), "Sum of Charges", xlSum
                ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Sum of Charges").NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
            
            End With
            
        ElseIf ws.Index > i Then
        
            With ws
            
                LastRange = .Range("A" & Rows.Count).End(xlUp).Row
                Set SourceRange = .Range("A1:N" & LastRange)
                
                LastRow = Range("E" & Rows.Count).End(xlUp).Row
                Range("E" & LastRow + 3).Formula = ws.Name
                
                wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceRange).CreatePivotTable _
                TableDestination:=Worksheets("Summary").Range("E" & LastRow + 4), _
                TableName:="PivotTable" & ws.Index
                
                Sheets("Summary").Select
                
                With ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Facility")
                    .Orientation = xlRowField
                    .Position = 1
                End With
                ActiveSheet.PivotTables("PivotTable" & ws.Index).AddDataField ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Charges"), "Count of Charges", xlCount
                ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Count of Charges").NumberFormat = "#,##0"
                ActiveSheet.PivotTables("PivotTable" & ws.Index).AddDataField ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Charges"), "Sum of Charges", xlSum
                ActiveSheet.PivotTables("PivotTable" & ws.Index).PivotFields("Sum of Charges").NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
            
            End With
        
        Else:
        End If
    
    
    Next ws
        
        For x = 1 To Worksheets.Count
            With Sheets(x)
                If .Name <> "Summary" Then
                    .Columns("T:AQ").Columns.Group
                    .Outline.ShowLevels rowlevels:=0, columnlevels:=1
                    .Select
                    .Range("A1").Select
                End If
            End With
        Next x
        
    Sheets("Summary").Select
    Range("A1").Select
    
    
    wb.Close savechanges:=True
    objExcel.Quit
    Set objExcel = Nothing
    End Function

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, looks impressive to one who doesn't really play well with Excel pivot tables, but it's not the code I was referring to. I meant the related stuff that happens up to the call to your posted procedure. Upon reviewing your initial post it seems you're saying the db opens but "hangs". Maybe when not logged on, there a missing variable value (such as a user credential) that is causing your code to get stuck in an endless loop. If that is the case, TS ought to be using a shortcut to open the db, and that shortcut should have a cmd switch. This cmd gets passed to the db whereupon it becomes the db Command property value. You use that to know if a person or machine is opening the db - as long as users don't use that same shortcut.

    When you get this sorted, you might want to investigate the use of dynamic ranges in Excel for future projects. That way, you don't have to worry about how many columns & rows have data when you need to do operations such as deleting it. IIRC, I couldn't get that to work if the range was defined at the workbook level, but only at the sheet level.

  6. #6
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    62
    Quote Originally Posted by Micron View Post
    Well, looks impressive to one who doesn't really play well with Excel pivot tables, but it's not the code I was referring to. I meant the related stuff that happens up to the call to your posted procedure. Upon reviewing your initial post it seems you're saying the db opens but "hangs". Maybe when not logged on, there a missing variable value (such as a user credential) that is causing your code to get stuck in an endless loop. If that is the case, TS ought to be using a shortcut to open the db, and that shortcut should have a cmd switch. This cmd gets passed to the db whereupon it becomes the db Command property value. You use that to know if a person or machine is opening the db - as long as users don't use that same shortcut.

    When you get this sorted, you might want to investigate the use of dynamic ranges in Excel for future projects. That way, you don't have to worry about how many columns & rows have data when you need to do operations such as deleting it. IIRC, I couldn't get that to work if the range was defined at the workbook level, but only at the sheet level.
    The scope of what the whole database does is fairly complex, so I was trying to keep my explanation focused specifically on the issue.
    The long and the short of the AutoExec macro are:
    1. Check if some files exist, and if yes
    2. Import them
    3. Queries process them
    4. Export the finished tables to a new Excel file
    5. Run the above create_pt code
    6. Some housekeeping and Exit

    I have other databases that run almost identically on Task Scheduler, minus the create pivot table code, and it runs just fine with the user logged out.

    When I say that it hangs, I'm just saying that everything up to the point that the macro calls the create_pt code seems to run just fine (I know this because it'll create the file it's supposed to work with, which is the step right before the pivot step), but when it reaches that code it just... pauses(?) I'm not sure what it does, because when I log back into the machine there's no error, nothing displayed on the screen, however the database file is locked open (I can see the temp file as if the db is open), and when I check Task Manager I see MSACCESS.EXE is still running, so I have to End Task. So I'm at a loss as to how to debug it, because it's not giving me any clues as to which line causes the issue. And again, this is code that runs perfectly when the user is logged in. It'll even run with Task Scheduler so long as the user is logged in. It's only when the user is logged out that it hangs up.

    As far as setting up TS, I do have the password entered for the user (and like I said other very similar processes execute just fine with the same settings.) So it seems to have something to do with the need for the database to actually open and manipulate the Excel file.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have other databases that run almost identically on Task Scheduler,
    Seems to me the problem isn't Access unless (repeating myself here) there is an issue regarding Trusted Locations, Windows Token (the 'file' that gets passed from one Windows application to another to provide characteristics such as permissions) or any other user profile related characteristic. If it works when user is logged in, to me it certainly indicates the issue is on the Windows or M$ application (such as Excel) side of things. My knowledge pertaining to tokens is limited to knowing they exist.

    To get any closer to knowing how far the code gets, I'd suggest inserting several lines that output to a log file "I got to point C"; "I got to point F" etc. and you review the file after a failed run. To avoid rapid successive writes to this file, I'd add about a 3 second pause before the write line. Probably won't help with any permissions problem unless knowing exactly where it fails provides a clue, but I thought it worth mentioning as a trouble shooting technique I've used before. For a brief moment, I wondered about using an xlsb file but that's probably not related, unless it contains any embedded macros that are getting in the way.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Task Scheduler Issue
    By LonghronJ in forum Modules
    Replies: 14
    Last Post: 04-13-2018, 09:03 AM
  2. Windows Task Scheduler .Bat File Macro
    By mrmmickle1 in forum Access
    Replies: 26
    Last Post: 09-08-2015, 04:21 PM
  3. Tricky Relationships in a Task Scheduler
    By igglebop in forum Database Design
    Replies: 2
    Last Post: 05-03-2012, 10:27 AM
  4. Windows 7 scheduler
    By faeren in forum Misc
    Replies: 1
    Last Post: 01-27-2012, 10:26 AM
  5. Task Scheduler using Service Account
    By ExpertNovice in forum Access
    Replies: 2
    Last Post: 06-11-2010, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums