VBA to Count number of instances of Access are running (1 Viewer)

[CBrighton]

Don't think much explaination is needed here.

I have a database which opens other databases via command line and uses the /x modifier to run a macro upon opening (which ends with the Quit command).

This database loops through a recordset on a timer and every 10 mins it checks the modified date of a file (my data sources) and if it has been modified in the last 12 hours it opens the database & runs the macro, then exits the loop.

This means if the file has not been modified it will continue through the loop until it finds one which has been, thus it will launch a database roughly every 10 mins.

What I want to do is limit the number it has open at once to ensure I don't get issues with my poor desktops memory / CPU. Given that the databases are launched via command line rather than being opeend as an access object, can I count the number of "msaccess.exe" processes which are currently running?

Cheers.

 

[jdraw]

Here is some code that may be adapted to your needs. I got original from a list I'm on.
I'm using Access 2003 on XP SP3.

'---------------------------------------------------------------------------------------
' Procedure : IsExeRunning
' Author : James Barash(AccessD)
' Created : 3/17/2009
' Purpose : To determine if a specific program is currently running.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: The name of the program
' Dependency: N/A
'------------------------------------------------------------------------------
'
Public Function IsExeRunning(strExeName As String) As Boolean
Dim objProcesses As Object, objProcess As Object
Dim i As Integer
IsExeRunning = False
Set objProcesses = GetObject("winmgmts://" & Environ$("ComputerName") _
& "/root/cimv2").ExecQuery("select * from Win32_Process")
If Not objProcesses Is Nothing Then
For Each objProcess In objProcesses
If objProcess.name = strExeName Then
'****
'added next 2 lines to show number of occurrences for CBrighton AWF Sept 21/2011
i = i + 1
Debug.Print i, strExeName
'****
IsExeRunning = True
'Exit For
End If
Next
Set objProcess = Nothing
Set objProcesses = Nothing
End If
End Function

'---------------------------------------------------------------------------------------
' Procedure : jtestit
' Author : Jack
' Created : 3/17/2009
' Purpose : Test routine to ensure IsEXERunning function is working
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub jtestit()
Dim smyEXE As String

smyEXE = "msaccess.exe"
Debug.Print "Is " & smyEXE & " running: " & IsExeRunning(smyEXE)
End Sub

 

[CBrighton]

Thanks for that, I'll give it a try tomorrow.

 

[vbaInet]

I completely forgot about windows management. I used Shell to get the same result but that required a file because you can't get standard output from Shell.

I amended the code above to suit your needs:

Public Function CountOfAccessExe(strExeName As String) As Long
    Dim objProcesses As Object

    Set objProcesses = GetObject("winmgmts://" & Environ$("ComputerName") & _
                                 "/root/cimv2").ExecQuery("select * from Win32_Process where name='MSACCESS.EXE'")

    If Not objProcesses Is Nothing Then
        CountOfAccessExe = objProcesses.Count
    End If

    Set objProcesses = Nothing

End Function

 

[CBrighton]

Thanks guys.

The work which this will automate is currently being run by me alone (the other person who does this work is on leave) so I only have about 30 mins left at the end of the day for work on automating them.

Hopefully I can get this tested and added before I finish today ready for tomorrows batch!

 

[CBrighton]

I know this was a while ago, but in case anyone else is looking for a way to do this the above worked perfectly.