Starting applications from MsAccess VBA using shell, can take seconds !!

Question

My MsAccess program is starting task using the VBA Shell version. Starting a program using shell() should take a couple of milliseconds, and sometimes it works that way, but frequently it takes seconds (3.5) !! Not workable at all!

Some info:

• I am running Windows11 64 bit pro with msaccess fully patched latest version.
• My MsAccess main program start starts up to thousands of shells. A maximum of ten in parallel. Before starting the next shells it waits for earlier started shell processes to finish.
• After starting the main process, I observe the one of the following two situations:

1. shell tasks start within the expected couple of milliseconds .... up to a certain moment .... and then suddenly every next shell start takes seconds
2. shell starts do take seconds right from the beginning

• Compressing and restarting msaccess does sometimes solve the issue ... for a moment but not always
• Restarting windows also does not always solve the issue (for a moment).
• I took care that the msaccess database is about max 1 TB at max (so wel below the 2TB limit)

I did search the internet for causes and solutions, but I could not find any. So hopefully some one understands the problem and has a solution. :( :(

Below two relevant parts of my program:

• A peace of code starting a shell program
• A peace of code checking if the started shell program is ready (So that I can start another shell program)

I do not rule out that there is a bug in windows, powershell or msaccess ... but of course the behavoir is to vague to be sure.

Main program Idea:

• start application
• start que with max 10 active shell programs
• wait for shell programs to finish
• fetch next job to do
• start the next shell for that job
• if all done ready

Sub TestShellStart(ByRef PID As Double, ByRef StartDelay As Long)

Dim StartTask As Long

Dim TaskStarted As Long

    StartTask = GetTimeStamp()

    '>> example

    PID = Shell("powershell.exe & vbCrLf exit /s" & vbCrLf, vbHide) ', vbHide vbNormalFocus

    TaskStarted = GetTimeStamp()

    StartDelay = TimeDiffMs(StartTask, TaskStarted)

end sub

Function CheckProcessByPID(PID As Double) As Boolean

Dim objList As Object

Set objList = GetObject("winmgmts:") _

    .ExecQuery("select * from win32_process where ProcessID='" & PID & "'")

If objList.Count > 0 Then

    CheckProcessByPID = True

Else

    CheckProcessByPID = False

End If

Set objList = Nothing

End Function

Answer 1

It sounds like you’re experiencing significant delays when starting shell tasks in your MS Access program using VBA’s Shell function. Here are some potential causes and solutions to consider:

1. Resource Contention: Running multiple shell processes in parallel can lead to resource contention, especially if each process is resource intensive. Ensure that your system has sufficient CPU and memory resources available.

Process Management: Your program waits for earlier shell processes to finish before starting new ones. If these processes are not terminating correctly or are taking longer than expected, it could cause delays. Consider adding more robust error handling and logging to track the status and duration of each shell process.

Windows and MS Access Updates: Ensure that both Windows 11 and MS Access are fully updated. Sometimes, updates can resolve performance issues.

Database Size and Optimization: Although your database is below the 2TB limit, its size can still impact performance. Regularly compact and repair your database to optimize performance.

VBA Code Optimization: Review and optimize your VBA code. For example, the Shell function runs asynchronously by default, which means the next line of code executes immediately. Ensure that your code correctly handles asynchronous execution.

System Performance: Monitor your system’s performance using Task Manager or Resource Monitor to identify any bottlenecks or resource-intensive processes.

Here is a revised version of your code with some additional error handling and logging:

Sub TestShellStart(ByRef PID As Double, ByRef StartDelay As Long)
    Dim StartTask As Long
    Dim TaskStarted As Long

    StartTask = GetTimeStamp()

    On Error Resume Next
    PID = Shell("powershell.exe & vbCrLf exit /s" & vbCrLf, vbHide)
    If Err.Number <> 0 Then
        Debug.Print "Error starting shell: " & Err.Description
        Err.Clear
    End If
    On Error GoTo 0

    TaskStarted = GetTimeStamp()
    StartDelay = TimeDiffMs(StartTask, TaskStarted)
End Sub

Function CheckProcessByPID(PID As Double) As Boolean
    Dim objList As Object
    Set objList = GetObject("winmgmts:").ExecQuery("select * from win32_process where ProcessID='" & PID & "'")

    CheckProcessByPID = (objList.Count > 0)
    Set objList = Nothing
End Function

If the issue persists, consider reaching out to Microsoft Support or consulting with a professional who specializes in VBA and MS Access performance optimization. They might be able to provide more specific insights based on your environment and code.

Answer 2

I did copy some lines from the reaction and below each line a comment

*Resource Contention: Running multiple shell processes in parallel can lead to resource contention, especially if each process is resource intensive. Ensure that your system has sufficient CPU and memory resources available.*

The PC is an 8 core 16 threads CPU. 32GB RAM. I have no reason at all to assume there is lack of resources. Monitoring with the task manager only shows that CPU memory and disk are far from fully used.

Process Management: Your program waits for earlier shell processes to finish before starting new ones. If these processes are not terminating correctly or are taking longer than expected, it could cause delays. Consider adding more robust error handling and logging to track the status and duration of each shell process.

I have implement a scheduler which monitors all started processes, and do not start new one's before others have finished. Apart from that in a lot of cases the problem is already there when starting the first shell program.

If these processes are not terminating correctly or are taking longer than expected, it could cause delays.

If a program is not ready withing the expected time, the main program kills the program using ^kill^

Dim PScmd As String

PScmd = "Stop-Process -ID " & CStr(ProcessID) & " -Force"    

Call PS_Execute(PScmd)

I also check if the program is correctly started by verifying that the PID <> 0

the Shell function runs asynchronously by default, which means the next line of code executes immediately. Ensure that your code correctly handles asynchronous execution.

asynchronously is one of the two main reason to use this principle ! :) The other reason is that I need to use external programs. I have no reason to believe that those programs do not behave correctly. As described the behavoir starts correct .... or not ... and functions well for hundreds of times before lets say 15 ms changes suddenly into 3.5 seconds.

Here is a revised version of your code with some additional error handling and logging:

I understand your extra code. However I never met a situation which did need an error handler ...

In the actual situation in case of an error the code will just stop asking for my attention. Perhaps I will add that error handler but that will not the problem

Answer 3

Side note: I guess you mean GB, not TB.

Try running this in a Windows Server version (free to download and run for several months).

Desktop Windows versions are not optimised for tasks like this, neither are desktop applications like Access.

Answer 4

We discussed this issue in the Access MVP group I am a member of.

One observation is that the process you're describing seems well outside of normal business processes, which is where Access is usually situated.

A better way forward would be to use tools that are designed for such processes, such as:

#1: A .Net worker service to control all these Shell calls, optimized as it is for the task:

Worker Services - .NET | Microsoft Learn

• Processing CPU-intensive data.

• Queuing work items in the background.

• Performing a time-based operation on a schedule.

#2: A clean instance of a server-class operating system. Yes, it costs a little, but if this is a core part of your business, it is the wrong place to try to save money.