Who keeps Access opened 2 ?

[Edgar_]

"..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?

I can give you an example with firebase, but you can build your own. First, go create some realtime database, then go to project settings and near the bottom there will be some config data:

Paste that in the VBA code:

Private Sub firebaseScript_onreadystatechange()
    If firebaseScript.ReadyState = "complete" Then
        doc.parentWindow.Document.parentWindow.execScript _
        "firebase.initializeApp({" & _
        "    apiKey: 'apiKey'," & _
        "    authDomain: 'authDomain'," & _
        "    databaseURL: 'databaseURL'," & _
        "    projectId: 'projectId'," & _
        "    storageBucket: 'storageBucket'," & _
        "    messagingSenderId: 'messagingSenderId'" & _
        "});" & _
        "firebase.database().ref('/YourNodeName').on('value', function(snapshot) {" & _
        "    var data = snapshot.val();" & _
        "    document.getElementById('listenerInput').value = JSON.stringify(data);" & _
        "    document.title = JSON.stringify(data);" & _
        "});"
    End If
End Sub

In your realtime database, add a node, in the js code above, the node is called YourNodeName, but you can name it yourself. Finally, make sure you enable IE11 emulation, its a registry key.

The rest is done by the app. You can see it in action by simply modifying the value of YourNodeName. The changes will be reflected in real time. The benefit of that is being able to capture those changes, I added two possibilities to this example:
1. Detect property change in listener input
2. Detect document title change

The most important part, these changes will reflect in any frontend where the web browser object is open.

 

[sonic8]

I can give you an example with firebase,

FYI: Firebase and Firebird are entirely different database systems.

 

[gemma-the-husky]

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

I see. Well I don't quite see what the issue is? Can't you just check the processes in task manger and see which instances of access are running?

 

[arnelgp]

FYI: Firebase and Firebird are entirely different database systems.

i think base is the nest of the bird?

 

[Edgar_]

FYI: Firebase and Firebird are entirely different database systems.

FYI: I am not talking about Firebird databases at any point, I am talking about Firebase websockets.

 

[sonic8]

Well I don't quite see what the issue is? Can't you just check the processes in task manger and see which instances of access are running?

How do you check the task manager on another computer, that might not necessarily be in the same network, in the same location, or on the same planet (just in case the application is run in a space station). ;-)

FYI: I am not talking about Firebird databases at any point, I am talking about Firebase websockets.

That's the point. @amorosik is using a Firebird database. So, your example of using a Firebase websocket feels somewhat far fetched.

 

[arnelgp]

FYI: I am not talking about Firebird databases at any point, I am talking about Firebase websockets.

better back-read, specially to post #13.

 

[amorosik]

No, on db the MON$ATTACHMENT table is like this:

On MON$REMOTE_PROCESS field you can see that ther is the same name, and are different procedure
But on db table appear only the name of runtime enviroment

 

[sonic8]

No, on db the MON$ATTACHMENT table is like this:

Sorry, it looks like my advice was based on inaccurate documentation. In some official documents MON$ATTACHMENTS.MON$ATTACHMENT_NAME is documented as "Connection String" while in reality it only contains the database name/path but no additional connection string info.

Maybe you could look whether the MON$CONTEXT_VARIABLES tables contains any helpful information, but it's a slim chance.

 

[Edgar_]

That's the point. @amorosik is using a Firebird database. So, your example of using a Firebase websocket feels somewhat far fetched.

What point? Do you have trouble with the code? did you enable IE11 emulation?

better back-read, specially to post #13.

I don't understand, sorry. You mean you re-read it again to see that the Firebase approach is indeed viable? I mean, this is post #13

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

Then you're correct. The Firebase approach presented relies only on having a form open with a websocket running, similar to how Access databases establish connection with their backends when a form is open, not before. This allows for real time messaging across all workstations, regardless of the specific database system in use. An Access frontend is sufficient for this functionality.

This methodology:
1. Works independently of the backend,
2. Facilitates communication across all workstations,
3. Operates on an event-driven basis,
4. Requires only a few lines of code

 

[The_Doc_Man]

I see. Well I don't quite see what the issue is? Can't you just check the processes in task manger and see which instances of access are running?

IF the connections are ODBC, is there even a process on the back-end? Unless there is a network broker task that is unique to Firebird's implementation, it is likely that at most, you would have a child process of Firebird itself (one per active client) for which a specific username would not be recognizable from the back-end. And the implementation doesn't have to even do that much if they use a pre-determined number of slots and just allocate a bunch of connection sockets internally. There is no guarantee that ODBC is not linearized internally on either a round-robin scheme or a first-come, first-served scheme.

You MIGHT get somewhere on the server using Windows Task Manager IF you could get there. Launch WTM. Select the "Performance" tab. At the bottom, click "Open Resource Monitor."

This next part isn't too hard, but requires you to maybe take notes. On Resource Monitor, select the Disk tab. Under the "Disk Activity" section (which you can expand by cursor-drag over the bottom edge), there is list of processes and files they have open. Find your database file. Clicking in the "File" column header changes the sort order to group the files together, which SHOULD make things easier to find. Each section scrolls independently so it won't be a terrible experience. Make a list of process IDs using your database.

Now switch to the Networks tab. Click in the "PID" column header to sort that list in PID order. You should be able to see the IP addresses of the sessions connected to your DB. You can ignore any that use the IPv4 loopback IP; those are internal connections that don't directly lead to anyone in particular and are usually part of the particular task's implementation. But you should be able to visit each PC to determine what IP address it is using, and that should match up with one of the connections from that list.

 

[gemma-the-husky]

IF the connections are ODBC, is there even a process on the back-end? Unless there is a network broker task that is unique to Firebird's implementation, it is likely that at most, you would have a child process of Firebird itself (one per active client) for which a specific username would not be recognizable from the back-end. And the implementation doesn't have to even do that much if they use a pre-determined number of slots and just allocate a bunch of connection sockets internally. There is no guarantee that ODBC is not linearized internally on either a round-robin scheme or a first-come, first-served scheme.

You MIGHT get somewhere on the server using Windows Task Manager IF you could get there. Launch WTM. Select the "Performance" tab. At the bottom, click "Open Resource Monitor."

This next part isn't too hard, but requires you to maybe take notes. On Resource Monitor, select the Disk tab. Under the "Disk Activity" section (which you can expand by cursor-drag over the bottom edge), there is list of processes and files they have open. Find your database file. Clicking in the "File" column header changes the sort order to group the files together, which SHOULD make things easier to find. Each section scrolls independently so it won't be a terrible experience. Make a list of process IDs using your database.

Now switch to the Networks tab. Click in the "PID" column header to sort that list in PID order. You should be able to see the IP addresses of the sessions connected to your DB. You can ignore any that use the IPv4 loopback IP; those are internal connections that don't directly lead to anyone in particular and are usually part of the particular task's implementation. But you should be able to visit each PC to determine what IP address it is using, and that should match up with one of the connections from that list.

What I was wondering was why it was as an issue. Is there still the same problem that you can't perhaps redesign the back end while it is showing as in use?

 

[gemma-the-husky]

How do you check the task manager on another computer, that might not necessarily be in the same network, in the same location, or on the same planet (just in case the application is run in a space station). ;-)

Well I assumed the server was on the LAN, and somebody must be able to connect to it.

Even if it's Firebird, does Firebird not offer management tools to ascertain who is connected, and stop the connection if necessary?

I assume we have a professional corporate system here.

 

[arnelgp]

I don't understand, sorry. You mean you re-read it again to see that the Firebase approach is indeed viable? I mean, this is post #13

post #13 talks about what his back end is, Firebird not Firebase.

 

[The_Doc_Man]

What I was wondering was why it was as an issue. Is there still the same problem that you can't perhaps redesign the back end while it is showing as in use?

That depends on the app, but if there is a "keep open" type of connection then something might be locked - in which case Windows File Locking would also still be engaged in some way. IN THEORY you can design a new table in Access even if someone is using the back end - but there can be no one actually trying to use the table under construction. Even then, I wouldn't bet 100% on that working correctly.

 

[Edgar_]

post #13 talks about what his back end is, Firebird not Firebase.

OP could be using a sack of sticky notes as a backend and the websocket would still communicate all workstations

 

[sonic8]

What point? Do you have trouble with the code?

I don't have trouble with the code. First of all, because I didn't even try to implement and run it.

However, I think people may have trouble with this short instruction of yours:

First, go create some realtime database, [...]

Well, my knowledge about Firebase is very limited. Based on that, I believe what you summed up in that short sentence means:

1. Get permission to use cloud infrastructure hosted in the US in a corporate application.
2. Find/contact the person(s) who manages the Google user accounts intended for such purposes.
3. Get permission and corporate credit card details for billing for this use case
4. Familiarize yourself with the Firebase console and create a new Firebase project
5. Create a new realtime database

The "get permissions" steps above may very well fail, as there is an existing (Firebird) database backend in place already.

I didn't call your suggestion wrong or claimed it does not work, I called it "far fetched". And I still think it is. Very far!

 

[amorosik]

Well I assumed the server was on the LAN, and somebody must be able to connect to it.
Even if it's Firebird, does Firebird not offer management tools to ascertain who is connected, and stop the connection if necessary?
I assume we have a professional corporate system here.

If you review post #30 you will understand that the db server allows you to track connected users
On REMOTE_PROCESS field there is a name of executable/runtime connected
But does not allow you to see which program is used to connect (for different Access procedure it show '..msaccess.exe' for both
And my question was "how to understand if procedure1 is active on pc1/pc2/pc3..."

 

[Edgar_]

I don't have trouble with the code. First of all, because I didn't even try to implement and run it.

That's OK. I'm aware most Access developers don't use the browser control and are unfamiliar with the methods and strategies employed in such technologies. However, websockets have been around for a while, even IE11 was capable of using them back in the days, so it's a mature technology and if you've ever used a chat application, it's very likely you've been using websockets without realizing. This is just another thing they're good for.

The "get permissions" steps above may very well fail, as there is an existing (Firebird) database backend in place already.

The websocket does not care one bit about Firebird, Access, SQL server, or whatever. The frontend running the websocket is the one responsible for the communication across all the other frontends.

Now, speaking of permissions, if you need any permission, it shouldn't be that different from the one they gave you to run Access. If your problem is Firebase, the first thing I said was that I could give an example with Firebase but there are plenty or resources to write your own.

I didn't call your suggestion wrong or claimed it does not work, I called it "far fetched". And I still think it is. Very far!

I don't know, man. Integrations are pretty common.

 

[sonic8]

Now, speaking of permissions, if you need any permission, it shouldn't be that different from the one they gave you to run Access.

@Edgar_ , I don't want to drag this OT sub thread much further, but I can't refrain from highlighting two key differences you appear to be missing.

1.) If an organization wants to build a database application it must choose two types of tools. A tool to store structured data (Firebird database server in this case) and a tool to design the UI of the application (Access in this case). Without either of these types of tools the project becomes impossible to implement.
Now, adding another tool to solve some subordinate technical requirement is an entirely different matter. It is not a mandatory key component for this type of project. Decision makers may very well choose to avoid this additional and optional risk.

2.) Access and Firebird (usually) run within the company's network, on their hardware, and under full control of the company.
Firebase (or a similar product) runs externally in the cloud. The application becomes dependent on internet connectivity and the availability of an external service. Data, even if it is not very sensitive, is stored outside the companies full control, potentially even outside the company's country of jurisdiction. This a a huge deal with a ton of red flags attached in many organizations.