How to check if an Access Application (not Process) is open for a user on a 2008R2 remote desktop server in vbs

I have some code that i use to test if a "process" is running for a user on a 2008R2 Remote Desktop server:
Dim IsOpen As String
Dim Loopcnt As Integer
Dim MaxLoops As Integer
Dim shell
Dim oNetwork                'Network Access Object
Dim sUserName               'Login Name of User
Set oNetwork = CreateObject("Wscript.Network")
sUserName = oNetwork.UserName
IsOpen = "N"
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery _
("SELECT * FROM Win32_Process WHERE Name = 'VMMFGWIN.EXE'")
For Each objProcess In colProcessList
      If objProcess.GetOwner(tUser, Domain) = 0 Then
         If tUser = sUserName Then
            IsOpen = "Y"
         End If
      End If
If IsOpen = "Y" Then
   Set shell = CreateObject("WSCRIPT.SHELL")
   shell.Run """\\Ct01\root\Visual\Scripts\OpenWO.vbs"" """ & Me.Base_ID & """ " & Me.Lot_ID & " " & IsOpen, , False
   MsgBox "Manufacturing Window must be open before you can open the Work Order"
End If
Set shell = Nothing

Open in new window

I need to test to see if a user has a Microsoft Access application open.  The above code can only tell me if the user has MSACCESS *32 open.  I need to know if they have a particular Microsoft application called "QUA20  Data Books" open.
Remember the user is on a Remote Desktop Server.  20-30 users may have the QUA20  Data Books application open on the "Terminal" Server.  I need to know if the current user has it open.

Any help would be greatly appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
I'm not a terminal server expert, but if each user has a userid on terminal server, then you would simply check in the folder where the application is installed for that user to see if there is file with the same name as your application and a ".laccdb" file extension.

Each user should still have their own version of the application front-end.
BFanguyAuthor Commented:
thanks, but they all share the same accmdb file on the terminal server.  i.e. only one lock file for 20-30 users.
Dale FyeOwner, Developing Solutions LLCCommented:
They should not be sharing the same accdb front-end.  That is a recipe for file corruption.
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

BFanguyAuthor Commented:
Battled my way through the file corruption problems 6 months ago.  We have 4 2008R2 Remote desktop servers.  Had a file share for front end for all 4 remote desktop servers, tons of corruption problems (after moving to virtual servers and SAN - Note: Ran for 3 years with no corruption problems with physical remote desktop servers and 1 physical file server share.  

Removed all local tables to SQL Server and placed the front end on the local drives of the terminal servers - no corruption problems in 6 months. ~100 users  (20-30 per front end).
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I'm a little tied up at the moment, but the way you'd do this in VBA is to look at all the top level windows and grab the caption.

I'm sure you can do this in VB Scripting as well.

If you don't have another response by tomorrow, I'll see what I can dig up.

Dale FyeOwner, Developing Solutions LLCCommented:
Here is the code I use to see who has a file open.  It gives me the computer names of the users that are logged in.  I usually pass it the name of the back-end file, but it sounds like you have moved the backend to SQL Server.  It they are all using the same front end file, then try using that file name (not the laccdb file name).

Public Sub LDBViewer2010(Filename As String)
'Reference Microsoft ActiveX Data Objects for this code to work

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

     ' Open connection to Access backend
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Filename & ";Persist Security Info=False;"

     ' Open recordset
    Set rs = cn.OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

     'Output the field list
    Debug.Print rs.Fields(0).Name, "|", rs.Fields(1).Name, "|", rs.Fields(2).Name, "|", rs.Fields(3).Name

     'Loop through users recordset of users in database.
    While Not rs.EOF
         Debug.Print Trim(rs.Fields(0)), "|", Trim(rs.Fields(1)), "|", Trim(rs.Fields(2)), "|", Trim(rs.Fields(3))

     ' Close
    If rs.State <> adStateClosed Then rs.Close
     Set rs = Nothing
     Set cn = Nothing
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BFanguyAuthor Commented:
interesting.  will investigate.  thanks.
Dale FyeOwner, Developing Solutions LLCCommented:
This is very simplistic, but could easily be incorporated into an application which would allow you to store computer names with users and lookup user information based on the computer name.

But I'm concerned that all the users may end up with the same computer name if it is being run from a server.
BFanguyAuthor Commented:
I was hoping to be able to test to see if they have the access front end application open without having to open the access front end.  looking at appactivate.... something from vba or vbscript.  

Remember i need to be able to see if the current user has the application open (it may be opened by 20-30 other users) on the "Terminal Server", not that it is open by someone.
Dale FyeOwner, Developing Solutions LLCCommented:
Are you trying to prevent them from being able to open the application twice?

You might try something like an AppLauncher, which stores the names of the applications, the people that are authorized to use them, and make the user sign in with their WindowsID when the AppLauncher is launched.  You could then keep track of when they open the application and close it, and check to see whether they already have it open.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
What is your ultimate goal?

Preventing the user from opening an app twice, or finding out who's in the app so you can do maintenance?

BFanguyAuthor Commented:
I want to open a form in the access app (if it is open) from another access front end app.  i.e. in access app OPR10.accdb, i want to be able to open a form in access app QUA20.accdb, but i need to know if QUA20.accdb is already open for the user for security reasons.
Seth SimmonsSr. Systems AdministratorCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for fyed's comment #a40830875

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.