How do i limit the number of connectable clients? (3 Viewers)

[amorosik]

How can I check that X workstations are connected to the database server and block the X+1 workstation that tries to connect?

 

[sonic8]

You are using SQL Server?
Then use a LogOn-Trigger to count the connections grouped by either user or computer name.

 

[amorosik]

You are using SQL Server?
Then use a LogOn-Trigger to count the connections grouped by either user or computer name.

Firebird

 

[sonic8]

Firebird

I'm not familiar with Firebird but a quick look into the docs shows that it is possible to create a database trigger for the CONNECT event.
That should allow you to implement my suggestion although you need to work out the details yourself.

 

[amorosik]

Ok, the query below seem to solve the problem

SELECT
    MON$ATTACHMENT_ID,
    MON$USER,
    MON$REMOTE_ADDRESS,
    MON$REMOTE_PROCESS,
    MON$REMOTE_PID,
    MON$TIMESTAMP
FROM MON$ATTACHMENTS
ORDER BY MON$TIMESTAMP;

 

[sonic8]

Very good.
You might need to count either distinct user or distinct remote PID because one Access frontend might use more than one connection.

 

[The_Doc_Man]

In fact, I had a similar problem with my "hung job" finder on my Navy Personnel system. The users logged in and got process ID's (PIDs, in OpenVMS-speak). But if they talked to the DB server, they might open multiple connections such as looping through a set of parent records and sub-iterating through children of that parent record. I had to test not only the VMS process status, but also the DB server's linked processes, which I could link via data in the DB server task table, such as the "remote ID" for each task. So it was a matter of building a parent/child structure to determine the overall status of the action as a whole across the two machines. It might sound a bit complicated, but the end result of killing truly idle processes saved us money twice.

Once, because in the early days we had dial-up connections over modems and long-distance charges started building due to hung jobs that should have, but didn't, get hung up properly. We settled on a rule to kill idle jobs if their resource usage statistics hadn't changed over a 15 minute interval. That one rule saved the Navy 64k$/month.

The other time was closer to the original question. We had a licensed interfacing kit that talked between our programs and ORACLE. It was an equivalent to the Access forms-and-reports GUI, though not quite as easily built as what Access did. I would have diverted the project to an Access FE, but the manager was suspected of a "sweetheart deal" with the interfacing software. But the point at hand was that the license fees were based on user count and had to be renewed each year based on usage levels. My hung job checker detected that 60% of our license consumption on a given day was idle jobs, abandoned by their users. Trimming idle jobs like that saved the Navy about 75k$/year. By that time, though , VMS had gained an "installed bulk license" checker and we COULD establish a rule that said, essentially as an error message, "Insufficient licenses, try again later."

You can do a Google search for "On Windows, track licenses in use" and get at least half-a-dozen suggestions. Things like SAM (Software Asset Management) and VAMT (Volume Activation Management Tool) may be helpful. There are other ways such as associated a system file lock with a particular utility and write some lock-checking code that uses an API to count "interest" locks and cause the app to terminate if more locks are attempted than are allowed.

 

[amorosik]

Very good.
You might need to count either distinct user or distinct remote PID because one Access frontend might use more than one connection.

Actually, I see now that it's not that simple
The information returned is insufficient to understand which program initiated the connection
I mean, there are several fields, one of which is REMOTE_ADDRESS, another is REMOTE_PROCESS
But even reading these fields, I can't tell which program initiated the connection
I mean, if I had three different Access programs connected to the same database, in the MON$ATTACHMENTS table I'd see three distinct records, but always with c:\program files\office\root\msaccess.exe in the REMOTE_ACCESS field
So, I can't figure out which program is calling
So, it's not possible to count how many workstations are connected to a given database with a given program

 

[tvanstiphout]

Maybe your app can update a server table at startup time, and again every 5 minutes, to say "I'm here, this is my name, and I am connected.".

 

[DaveMD]

I am not familiar with Firebird, but potentially this might work if it is supported. In SQL Server, you can include the "Application Name" in your ODBC/OLE connection string. Then, from SQL Server, you can identify the application related to each connection.

Driver={SQL Server};Server=mySqlServer;Initial Catalog=myDatabase;Application Name=My Access App;

Edited to Add: From some very brief research, it looks like Firebird does not support specifying the Application Name in the connection string. However, it does have the ability for you/your app to set custom attributes for the user session. As I do not use Firebird, this is untested. Potentially these could be used for your scenario to help identify how many users are connected to your database specific to your custom application. See below.

-- Set custom attribute related to current user's session
SELECT RDB$SET_CONTEXT('USER_SESSION', 'APP_NAME', 'MyApp v3.2.1')
FROM RDB$DATABASE;

-- Get custom attribute
SELECT RDB$GET_CONTEXT('USER_SESSION', 'APP_NAME')
FROM RDB$DATABASE;