What/Who is connected (1 Viewer)

Isskint · Sep 7, 2018

Hello guys and girls, it has been a while since i was around. Hope you are all well?

I have a BE database whose tables overtime have become the source data for several excel spreadsheets and been linked in other FE database.

Is there any way to see active connections to the BE database?

isladogs · Sep 7, 2018

Several ways of doing this.

If users login with username, you can record that and login time plus logout time in a table then query it as needed. This is what I do.

Or try this utility https://www.access-programmers.co.uk/forums/showthread.php?t=295174

Isskint · Sep 7, 2018

Thanks ridders, I already capture log in / log out data on this database, so seeing who is logged in is easy. I also have a way to 'kick out' active users if required. It is not the who i am trying to identify.
What i want to try and identify is the names of other DB's and excel spreadsheets that are currently open and are using a data connection to this BE DB.

isladogs · Sep 7, 2018

Ah, I wondered why the title contained What as well as Who.

Two things that may help

1. Attached is one of my favourite utilities which checks the connections currently being used.
Its originally by Ben Sacharich & brought to my attention by AWF user moke123.
All I've done is add error handling and make cosmetic changes to its apearance

Just copy the single form into a database front end, leave it running & monitor the effect of different actions
Useful whilst developing to check what acts as a resource drain and where connections aren't released after an action is completed

It doesn't currently log these connections but it should be easy enough to adapt for that purpose

2. I have code which logs the vast majority of events taken by users running one of my large schools databases.
This is a small extract from the 0.5 million plus records in the demo version.
I prune it at intervals for obvious reasons!

I also have a similar system for logging ALL errors that occur
This was extremely useful when I added a detailed error logging system.
When errors occurred, an email was automaticvally triggered giving details of who/what/where it happened together with the full error log

Let me know if you want more details about the above

Isskint · Sep 7, 2018

Nice little utility ridders. Something to add to my toolkit

Nothing to help with external application connections to back end though (unless i am missing something?)

isladogs · Sep 7, 2018

Isskint said:
Nice little utility ridders. Something to add to my toolkit

Nothing to help with external application connections to back end though (unless i am missing something?)

Hmm....
I've only ever tested it by opening forms & reports where it counts the connections used by each object but without listing the tables/queries used in the recordsource

I've just tried opening tables & queries direct & requerying the display
The connection count drops by:
4 for each local table / linked SQL table opened
6 for each linked access table opened
7 for each linked spreadsheet

BUT the table names are never listed ...which is irritating!

Can't see why these aren't included as open objects in the code
Not sure whether the code can be adapted to do so.
Will look into it in the next few days &, if I succeed, will send you a PM.
If you want to do the same, I'd be very happy

isladogs · Sep 8, 2018

I've just tweaked the available connections utility so it now also lists tables or queries opened directly from the nav pane or DoCmd.OpenTable/OpenQuery

To use this new version in your apps, copy both form and modFunctions (or just import the IsObjectOpen function into an existing module)

This new version does not list tables/queries separately where these form part of a form or report record source (though these are counted as used connections).

I may tweak it further to do that as well
Just noticed the spelling error in the top line of the form display.... intitially

HTH

The_Doc_Man · Sep 8, 2018

Depending on your network setup, knowing WHAT is connected on the other end of a remote link might not be possible. The information for that is stored in a lower layer of the ISO 7-layer model, probably in the Session layer. Accessing it requires you to examine some parts of the TCP/IP socket infrastructure. Doing so on a home system might be possible but in a domain environment, most users are not likely to have the system rights required to see this kind of information.

isladogs · Sep 8, 2018

Doc
True but that's not the point of this utility
It was designed to check what is using up available connections and indeed not releasing them. As such it is only recording open objects, both local and linked.
The purpose is to inform developers of items causing such issues

moke123 · Sep 8, 2018

Hey Colin,
I made a couple tweaks to your tweaks.
Setting the border to none does not enable you to move the form to an out of the way place on your desktop. I changed it to "Thin"
I also added an auto requery feature so I can keep an eye on things during testing without having to click a requery button. Also corrected your spelling.

edit:Also moved IsObjectOpen to the form module.

The_Doc_Man · Sep 8, 2018

I answered the way I did because of two statements:

From post #1:

tables overtime have become the source data for several excel spreadsheets and been linked in other FE database.

From post #3:

What i want to try and identify is the names of other DB's and excel spreadsheets that are currently open and are using a data connection to this BE DB.

You can fairly easily find out the "WHO" but not so easy for the "WHAT".

If Isskint wants to know whether this is specifically a linked FE or a query from another utility such as Word or Excel (both of which can query an Access database), that distinction is what might not be possible. That was my point.

Yes, you can find the WHO and often even the WHERE, because the .LDB file usually includes the computer name {=Environ("ComputerName")} that you can read by opening the LDB with IE. But it won't distinguish among the various utilities that can link to an Access BE. That is data buried in the bowels of socket infrastructure.

isladogs · Sep 8, 2018

moke123 said:
Hey Colin,
I made a couple tweaks to your tweaks.
Setting the border to none does not enable you to move the form to an out of the way place on your desktop. I changed it to "Thin"
I also added an auto requery feature so I can keep an eye on things during testing without having to click a requery button. Also corrected your spelling.

edit:Also moved IsObjectOpen to the form module.

Your tweaks are accepted!

Actually I was going to do all of those so you've saved me time. Thanks

I'm going to add a report & then leave it alone. Will post it later

isladogs · Sep 8, 2018

Version 3 attached.
Further minor tweaks as follows:
a) minor changes to layout of form to identify each object type
b) logging object names on by default
c) added summary report
d) updated info section

The screenshot below is from the info section & should help explain what the purpose / limitations of this utility are:

I'm done now!

The_Doc_Man · Sep 9, 2018

I have continued to search for ways to identify the remote application. The consensus of the articles I have found so far (and I stress so far) is that nothing on your local system can go back across the network barrier to ask what is running unless it was already set up to answer that question. There are some network monitoring tools that actively or passively identify what initiates a remote connection to another system, but they are in 5 to 6 digit price range in US$.

It IS possible to use the Windows Resource Monitor to look at network activity and you can filter what it looks at. However, it is one-sided. I.e. it tells you what is running locally and you can know that something is running remotely, but not WHAT is running. You can use ResMon to kill a remote process connection if you were worried about a runaway or hanging remote session.

I'm not convinced it is impossible but so far I have not found anything useful. And given Isskint's questions, the WHAT part might not be an easy task. I've done some network programming in the past and know the general theory, but I haven't found the structures I need yet to answer this question.

Isskint · Sep 10, 2018

Yeah i have asked my IT people about this also.

Doc, strangely the response i have from them today, was

"I do not think it is impossible but thus far I have not identified the relevant paths/structures. I think the data is there, but the time and effort may not be warranted for the result"

Doc, you work for them?

Appreciate your thoughts and suggestions on this guys

The_Doc_Man · Sep 10, 2018

Isskint, I worked for the U.S. Navy as a contractor, but big IT departments often instill the same kind of thought patterns in their people. It is never that you CAN'T do something, but that you don't know how to get there from where you are at the moment. I know it exists and PROBABLY is part of the session initiation handshake, which makes it a Session Layer item. However, I doubt that it is a persistent item. I.e. once the session has been established, the data for it has been overlaid by subsequent protocol exchanges.

As to the "can't do something" - the only exceptions I have ever seen for this involved silk purses and sow's ears, and that is not usually an IT problem.

Isskint · Nov 14, 2018

I have managed a work around.

Each spreadsheet/database etc that is linked to this main BE database, now logs connection time and userID and disconnection time. Thus i can look at this connection table to identify who/what is connected.

What/Who is connected (1 Viewer)

Slowly Developing

MVP / VIP

Slowly Developing

MVP / VIP

Attachments

Slowly Developing

MVP / VIP

MVP / VIP

Attachments

Immoderate Moderator

MVP / VIP

AWF VIP

Attachments

Immoderate Moderator

MVP / VIP

MVP / VIP

Attachments

Immoderate Moderator

Slowly Developing

Immoderate Moderator

Slowly Developing

Similar threads

Users who are viewing this thread