View users logged into database MS Access 2016

[y770]

This is weird. I tried this on coworker computer and it runs beautifully.
So, I will check what are the differences. However, here are the answers:
I cannot explain how it worked out to be this way, but I do not have Microsoft Office 14.0 Access database engine library on the list. If I new in which dll file this library is I could try finding it and manually referencing it. But Access show only small part of that path and I do not know the file name.
The problem happen in the ShowUserRosterMultipleUsersLocal and howUserRosterMultipleUsersRemote functions at cn.Open command. I have tried replacing 'cn.Provider = CurrentProject.Connection.Provider' command with 'cn.Provider = "Provider=Microsoft.ACE.OLEDB.12.0' and it did not help because 'CurrentProject.Connection.Provider' returns 'Microsoft.ACE.OLEDB.12.0'. From the above it does look likes the problem is with my Access installation. I will try to work this side out.

Thanks for the help.

 

[isladogs]

The path on my machine is

C:\Program Files\Common Files\microsoft shared\OFFICE14\ACEDAO.DLL

On yours its probably

C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14\ACEDAO.DLL

You may need to repair Office before it will allow you to change the location

NOTE: this page may be worth bookmarking: http://allenbrowne.com/ser-38.html

If I type this in the Immediate window, I get the result shown:

?CurrentProject.Connection.Provider
Microsoft.ACE.OLEDB.12.0

I think you are saying you get that as well

 

[y770]

Office repair did the job.

Thanks

 

[psyc0tic1]

For informational purposes this works on access 2013 64 bit and windows 7.

This same information can be obtained by clicking on the little locked file that is created (where the back-end is located) when someone is in the database (and choose Microsoft Word to view it) which displays the computer ID but just the same shows the user as "Admin" rather than the actual username of the logged in user.

For this reason I store the users computer id in the users table so I can match it up to a user... this works ok other than when multiple users use the same computer... they all have the same computer id so I do not know which one is actually in there.

Is that something that can be obtained?

 

[isladogs]

Richard
For info I posted an updated version of this utility to sample databases area lat year https://www.access-programmers.co.uk/forums/showthread.php?t=295174

The original version by David Crake was for MDB files for which the corresponding LDB lock file was not easily readable.

I modified it to work with ACCDB files including password protected files in response to a request from a forum member. As you observed the LACCDB lock file can easily be read in a text editor such as Notepad and contains workstation and admin as user name.
However the LACCDB file doesn't always 'release' workstation info if someone logs out. The utility will refresh every 10s.

What it doesn't do is get the actual logged in user instead of Admin. I use a different approach in my own apps similar to what you outlined except that I store the logged in user name and workstation.

As for your point about distinguishing between users sharing the same app....this should never arise. Each user should always run their own copy of the FE from their own hard drive. If users share the same FE, corruption WILL occur and you risk losing everything. Both database objects and data could be permanently lost.

If on the other hand, you meant different users login to the same PC at different times and there is no simultaneous app sharing, that's fine. To get their network user names, each user should login to Windows and logout afterwards....or you'll need them to login to the app itself with user name and password.

 

[psyc0tic1]

What it doesn't do is get the actual logged in user instead of Admin

That is the part I was hoping for

Each user should always run their own copy of the FE from their own hard drive. If users share the same FE, corruption WILL occur and you risk losing everything. Both database objects and data could be permanently lost

Definitely not what I was referring to... All people in the company can log into any computer and it is just like if they were sitting at their own desk and would have their own copy of the front-end under their own login credentials. In one lab we have 4 people that use the database but all use the same computer but under their own login credentials.Of course only one can be logged in at a time so there is no sharing. I learned my lesson on that before I split the database.

I am thinking of changing the credentials system to use the windows login information rather than the stand alone within access.

 

[psyc0tic1]

I found some code on the interweb that if the form is run from the back-end it is supposed to work like I was asking about but I have to wait until the users are out of it before I can put the form in.

It would be nice if a form can be linked to the front-end like a table... then I wouldn't have to open the back-end to run this form.

I have not tested it yet... if it works I will post it here.

 

[isladogs]

It's very easy to get the network user name using e.g Environ("UserName").
But see this post about spoofing user names https://www.access-programmers.co.uk/forums/showpost.php?p=1590751&postcount=18

 

[isladogs]

Just a quick update to see if you need user name details rather than the default Admin, have a look at this alternative version instead https://www.access-programmers.co.uk/forums/showthread.php?t=193131

 

[Mick3911]

Hi isladogs,

I came across this DB that you have created, (which is exactly what I was looking for, think it’s great) downloaded it (Version 3) and followed your instructions; imported the module into my database and added the Call LockedOut to my startup form.

When I open my database I get the screenshot attached.

VBA is not my forte (but I’m learning as I go). Can you please advise what I need to do/have done wrong?

 

[isladogs]

You forgot to supply the screenshot.
I have two similar utilities. Can you also supply a link to the relevant thread.
I would also b helpful to know your Access version and 'bitness'
Finally, have you checked your VBA references

 

[Mick3911]

Hi isladogs,

I meant to say that this only happens when i 'lock' the database.

The screenshot can viewed here; https://www.access-programmers.co.uk/forums/album.php?albumid=71

The link I downloaded the DB from is; https://www.access-programmers.co.uk/forums/showthread.php?t=295174

 

[Mick3911]

Hi isladogs,

Access Version: 2010 32bit

Not too sure what you mean by

checked my VBA references

Have attached screenshot here if easier.

 

[isladogs]

Sorry but the image is illegible.
Please redo showing just the error message and the procedure where it occurs.

Open the VB Editor and go to Tools....References. Take a screenshot of the References window.
Also, still in the VBE, go to Debug...Compile and fix any compilation errors that are detected.

 

[Mick3911]

Good morning isladogs,

I have debugged and fixed a couple of compilation errors that were detected and have attached an .xlsx file containing a screenshot of the ‘References’ and a screenshot of the screen I get when I open my startup page (frmLogin). (This was the only way I could attached screenshots due to the size).

As I said , this only happens when I ‘lock’ the DB using your DB.

 

[isladogs]

Add this line to the declarations section of that module

Public strProc As String

In my example, that was in the declarations section of modFunctions.
So an alternative would be to just copy that complete module.

Also I have one reference you've missed out:

If its not in the list, browse for the file - as we both have A2010, the location may be the same as mine

If you get an error after doing that, come back to me.
I'm busy all day but can look this evening (UK time)

BTW if you use Alt+PrtSc you can get a screengrab of the active window.
Or use the Windows snipping tool to just grab the relevant part of the screen

HTH

 

[Mick3911]

Where do I add this line; Public strProc As String?

“In my example, that was in the declarations section of modFunctions”. Don’t you mean modLockedOut?

I do have the ‘Microsoft 14.0 Object Library’ in the References ticked, albeit it is in a different location (C:\Prgram Files(x86)\Common Files\Microsoft Shared\OFFICE14\MSO.DLL)

No need to take a look this evening as I’m sure you have better things to do

 

[isladogs]

I did mean it was in modFunctions though it would have been better in modLockedOut. It should go at the top of the module under Option Explicit.
Have a look at my modFunctions and do the same. Whilst you are there, look at the code in that module. You may wish to use it.

That reference was missing from your screenshot. You should have all six of my references.

The code should work once you've fixed the above points. Let me know please.
It it still doesn't work b as precise as possible about any issues. The code has been used by many people over ten years or more so hopefully it will for you as well.

For info, I'm 'semi retired' and tend to do Access work in the evening and other non IT things in the day.

 

[Mick3911]

Hi isladogs,

Works fine now after adding that line of VBA.

Many thanks for your time and patience.

 

[isladogs]

Excellent. Glad I could help