Who keeps Access opened 2 ? (1 Viewer)

[amorosik]

Suppose we have a server database and many workstations each with its own copy of the Access procedures
In this situation it is not possible to use the laccdb file because each workstation will have its own
How to detect (from any workstation) which workstations have the Access program running in memory?

 

[Edgar_]

A websocket running from a web browser control could be raising events from any workstation to all workstations. That would require zero timers and it could be monitored without Access, even from a phone. It would also allow you to issue instructions like close all workstations, or open a form at once. Since all apps would be in sync, this would happen in real time.

 

[gemma-the-husky]

That doesn't matter. (Individual PCs). Check the open sessions to the data/laccdb on the shared server.

 

[arnelgp]

you can use Allen Browne's ShowUserRosterMultipleUsers() function found:
Microsoft Access tips: ADO Programming Code Examples (allenbrowne.com)

 

[sonic8]

Suppose we have a server database ...

Is it an .accdb file on a file server? Then there will be a shared .laccdb file in the server directory.
Is it a server DBMS like SQL Server? Then the database server will provide means to query active sessions.

 

[ebs17]

Creating a laccdb is a method of making an access file backend-capable. So when a backend is accessed (linking), the laccdeb is created in the same directory or supplemented with the relevant information. So if the backend is accessible and readable, the laccdb (simple text file) should be too.

You should ask yourself where your backends are. In more complex applications, you also have user logins and thus automatic knowledge of active logins.

 

[KitaYama]

How to detect (from any workstation) which workstations have the Access program running in memory?

When you talk about processes in memory, it sound to be a windows question, but your question is posted in Access sub forum.
If it's a Windows question see the following.
If it's an Microsoft Access question, forget about the following

You can check the processes of all PCs on a network.
Start Windows Management Instrumentation service.
Download WMI Explorer from GitHub
Run WMI Explorer
Connect to a PC ( use PC name, user name and password from file menu - Connect)
Double click CIMV2
Dobule Click Win32_Process
Check the list of processes

You can also use the query tab or Script tab to do a search

 

[Pat Hartman]

How to detect (from any workstation) which workstations have the Access program running in memory?

If the FE is run from the user's local PC as it should be, then there is only one user and so only one db for the .laccdb to manage.

However, the BE is shared and when the BE is opened, a lock file is created in the directory of the BE and that controls all the data sharing. If the BE is Jet or ACE, there is a "Who is connected" tool that will tell you which workstations are connected to the BE which is what is important.

If the BE is SQL server and everyone has his own log on credentials, the DBA has tools that can tell who is connected. Off hand, I don't remember the command because I am never the DBA so I just design the database, I don't manage it once it is installed in "production"

 

[KitaYama]

If the BE is SQL server and everyone has his own log on credentials, the DBA has tools that can tell who is connected.

You don't need to be DBA or use external tools to know who is connected to sql server.
You can check it for yourself:

Sub WhoIsConnected()
    Dim con As Object
    Dim rs As Object
    Dim strCon As String
    Dim sql As String
    Const Server As String = "Server"
    Const db As String = "DatabaseName"

    strCon = "Provider=SQLOLEDB;Data Source=" & Server & ";Initial Catalog=" & db & ";Integrated Security=SSPI;"
    sql = "SELECT ses.session_id, ses.host_name, ses.login_name, ses.program_name, con.client_net_address "
    sql = sql & "FROM sys.dm_exec_sessions ses "
    sql = sql & "JOIN sys.dm_exec_connections con ON ses.session_id = con.session_id "
    sql = sql & "WHERE ses.is_user_process = 1;"

    Set con = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    con.Open strCon
    rs.Open sql, con

    Do While Not rs.EOF
        Debug.Print "Session ID: " & rs.Fields("session_id").Value
        Debug.Print "Host Name:  " & rs.Fields("host_name").Value
        Debug.Print "Login Name:  " & rs.Fields("login_name").Value
        Debug.Print "Client IP Address:  " & rs.Fields("client_net_address").Value
        Debug.Print "Program Name:  " & rs.Fields("program_name").Value
        Debug.Print "----------------------------------"
        rs.MoveNext
    Loop

    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
End Sub

You will receive this ;

.....
Session ID: 53
Host Name: KONISHI
Login Name: ******\K_Konishi
Client IP Address: 192.168.100.17
Program Name: Microsoft Office 2010
----------------------------------
Session ID: 54
Host Name: KONISHI
Login Name: *****\K_Konishi
Client IP Address: 192.168.100.17
Program Name: Microsoft Office
----------------------------------
Session ID: 55
Host Name: Ando
Login Name: *****\K_Ando
Client IP Address: 192.168.100.23
Program Name: Microsoft Office
----------------------------------
Session ID: 56
Host Name: Ando
Login Name: *****\K_Ando
Client IP Address: 192.168.100.23
Program Name: Microsoft Office
----------------------------------
.....

 

[amorosik]

A websocket running from a web browser control could be raising events from any workstation to all workstations. That would require zero timers and it could be monitored without Access, even from a phone. It would also allow you to issue instructions like close all workstations, or open a form at once. Since all apps would be in sync, this would happen in real time.

"..A websocket running from a web browser control.."

I don't understand exactly what you mean
Can you give me some links/examples/documentation to study and follow?

 

[amorosik]

That doesn't matter. (Individual PCs). Check the open sessions to the data/laccdb on the shared server.

I don't understand what you mean
The 'shared server' is a Firebird db and can also be accessed by other programs besides the one I would like to monitor
And so it's not a feature that I can 'see' on the db server

 

[amorosik]

you can use Allen Browne's ShowUserRosterMultipleUsers() function found:
Microsoft Access tips: ADO Programming Code Examples (allenbrowne.com)

Thanks, I'm seeing Allen Browne's example now
But an accdb file with the data is used
Are you saying that the same system is usable even if the db server is accessed via odbc?

 

[amorosik]

Is it an .accdb file on a file server? Then there will be a shared .laccdb file in the server directory.
Is it a server DBMS like SQL Server? Then the database server will provide means to query active sessions.

Pc1, is a db server with Firebird 3.0
Pc2 is workstation 1, connected to db server via odbc
Pc3 is workstation 2, connected to db server via odbc
Pc4 is workstation 3, connected to db server via odbc

Every workstation has own Access procedure, on local disk
It is important to note that the individual workstations also have other programs that access the same db server
And therefore the use of the database server's own functions to detect connected users is of no help in my case
Because I would like to understand if the 'pippo' Access procedure is active on the individual workstations, not if user1/pc1 is active on db server

 

[amorosik]

Creating a laccdb is a method of making an access file backend-capable. So when a backend is accessed (linking), the laccdeb is created in the same directory or supplemented with the relevant information. So if the backend is accessible and readable, the laccdb (simple text file) should be too.

You should ask yourself where your backends are. In more complex applications, you also have user logins and thus automatic knowledge of active logins.

My 'backend' is a Firebird db server
And 'active logins' on db server is of no help in my case

 

[amorosik]

When you talk about processes in memory, it sound to be a windows question, but your question is posted in Access sub forum.
If it's a Windows question see the following.
If it's an Microsoft Access question, forget about the following

You can check the processes of all PCs on a network.
Start Windows Management Instrumentation service.
Download WMI Explorer from GitHub
Run WMI Explorer
Connect to a PC ( use PC name, user name and password from file menu - Connect)
Double click CIMV2
Dobule Click Win32_Process
Check the list of processes

View attachment 114686


You can also use the query tab or Script tab to do a search



View attachment 114687

Yes, I think this is a valid possibility
Now I'll see if I can understand more about it.

 

[arnelgp]

Thanks, I'm seeing Allen Browne's example now
But an accdb file with the data is used
Are you saying that the same system is usable even if the db server is accessed via odbc?

No, only if the backend is Access also.

 

[gemma-the-husky]

I don't understand what you mean
The 'shared server' is a Firebird db and can also be accessed by other programs besides the one I would like to monitor
And so it's not a feature that I can 'see' on the db server

Maybe I'm wrong, and I'm not familiar with the database you are discussing.

However the backend database that holds the data that all the front ends are connected to, would be the one you need. There will be an accdb file associated with that database.

Whatever machine that database is running on is the one to look at. Try to delete the accdb file manually, and you will most likely see a "you can't. The file is in use" message. You need to use facilities on that server to see which users/machines have sessions to that file.

You started by saying you had shut down the machine that you thought had the database connection, but that didn't resolve the issue. I am trying to explain that you need to look at the machine that holds the backend.

 

[amorosik]

Maybe I'm wrong, and I'm not familiar with the database you are discussing.

However the backend database that holds the data that all the front ends are connected to, would be the one you need. There will be an accdb file associated with that database.

Whatever machine that database is running on is the one to look at. Try to delete the accdb file manually, and you will most likely see a "you can't. The file is in use" message. You need to use facilities on that server to see which users/machines have sessions to that file.

You started by saying you had shut down the machine that you thought had the database connection, but that didn't resolve the issue. I am trying to explain that you need to look at the machine that holds the backend.

The data is in the Firebird server db, there is no accdb file with the data
And using the db server functions to check the connected users doesn't help me, because the individual workstations could have other programs that access the same data on db server
I remember the problem I would like to solve: I would like to understand from the VBA code included within the test1.accde procedure running on PC1, how to know if the test1.accde procedure on PC2/PC3/PC4 is running or not

 

[sonic8]

It is important to note that the individual workstations also have other programs that access the same db server
And therefore the use of the database server's own functions to detect connected users is of no help in my case

Look at the MON$ATTACHMENTS system table. It contains the client process name. If this isn't of help, because it is just MsAccess and there are multiple Access applications, then pass in your 'pippo' application name in the connection string and use that to identify the relevant sessions.

(Disclaimer: I haven't used Firebird in more than 10 years, I might mis-rememeber things.)

 

[amorosik]

Look at the MON$ATTACHMENTS system table. It contains the client process name. If this isn't of help, because it is just MsAccess and there are multiple Access applications, then pass in your 'pippo' application name in the connection string and use that to identify the relevant sessions.

(Disclaimer: I haven't used Firebird in more than 10 years, I might mis-rememeber things.)

Yes, on Remote_Process field there is:
- C:\Program Files (x86)\HK-Software\IBExpert\IBExpert.exe
- C:\Program Files\Microsoft Office 15\Root\Office15\MSACCESS.EXE
- C:\Program Files\Microsoft Office 15\Root\Office15\MSACCESS.EXE

but, in the second and third rows the Process Name is the same, for different program

What exactly you mean with "..then pass in your 'pippo' application name in the connection string and use that to identify the relevant sessions..." ?