Who is using the database?

[Jock W]

How can I ascertain whom, on a network, is accessing the Access database?
On one form, I have a text box which displays who printed the form using
"=fOSUserName()", so I am wondering if I can use a similar expression in a
form or report to show me who is accessing the database because I am fed up
of the "You do not have exclusive access to the database at this time......"
warning.
:-/
Thanks

 

[Allen Browne]

Unless the database is opened exclusively, the LDB file will tell you who is
connected. Sample code:
http://allenbrowne.com/func-ADO.html#ShowUserRosterMultipleUsers

But this issue can arise even if no other users are in the db. It seems to
occur if you modify the object (the form or report or its module), use it,
and go to save it after making other changes.

 

[David W. Fenton]

Unless the database is opened exclusively, the LDB file will tell
you who is connected. Sample code:
http://allenbrowne.com/func-ADO.html#ShowUserRosterMultipleUser
s

I have been working with the old msldbusr.dll for nearly a decade
now, and recently implemented the ADO UserRoster for the first time.
It seems to give different results, and seems to be getting data out
of the MDB file itself instead of just from the LDB file.

I just revived an old administrative database for the possible use
of one of my clients and you can download it here:

http://dfenton.com/DFA/download/Access/Admin.zip

It's an A2K database, and it uses late binding for ADO.

I did not need the complicated code you have, Allen, to get the
number of users.

I found that the msldbusr.dll code was much easier to work with (it
returns a much more versatile data structure), even though the
basics for UserRoster look really easy. The problem is that if you
want to filter the results you can't, because you don't have access
to the field names so you can query the UserRoster with WHERE
clauses. I found this to be a real lack.

Anyway, the above database has a UserRoster implementation in its
main "Who's logged on?" form, and also has two alternate approaches
that don't use UserRoster. One uses the old msldbusr.dll, which was
for Jet 3.5. But most of its functionality works just fine with Jet
4 (it can't do the suspect users lookup, though; or, at least, I
haven't figured out how to make it work). The other form just opens
the LDB file with basic I/O functions and eads it directly. This
doesn't give you as much information about the states, but can work
in some situations where msldbusr.dll will not.

Feedback and suggestions welcome -- it really was a very
quick-and-dirty conversion of code that I wrote in 1998! And it was
my first use of the ADO UserRoster.

 

[Jock W]

Allen,
thanks for that, but where would I put this code? in a form or report?
I am not as advanced as some users but am willing to try anything if I can
understand the concept!
tia

 

[Allen Browne]

The code goes into a standard module.
Click the Modules tab of the Database window.
Click New, and paste the code in there.
In the code window, choose Compile on the Debug menu.
If there are errors, you may need to add the ADO reference:
http://allenbrowne.com/ser-38.html

You can then use the function just like Date() or any of the built-in ones.
One way is to open the Immediate Window (Ctrl+G), and enter:
? ShowUserRosterMultipleUsers()

 

[Allen Browne]

Hi David. It's been ages since I fiddled with this, but my recollection is
that the LDB may not be up to date (i.e. it retains info on users who have
logged out), so the newer approach is more useful.

As always, we would love to hear from anyone who uses these regularly.

 

[Jock W]

Ok, getting somewhere now.
Set up the module as stated. Added a text box to the switchboard and made
the control "=ShowUserRosterMultipleUsers()".
When executing, I get a run time error stating I don't have the necessary
permissions to use the *****.mdb object. I have full rights so I am a bit
bewildered by this message. Also, I don't understand the bit about needing a
GUI to reference the schema.
Help and thanks for your patience - it's much appreciated
regards

 

[Allen Browne]

If the database is secured (MDW file), you may need to include the security
info as part of the Open string.

I'm not sure what was meant by "needing a GUI" either. Not sure what you are
referring to here.

 

[Jock W]

Sorry for not being clear.
Firstly, it's not a secured db.
In your code for ShowUserRosterMultipleUsers(), there's an information line
refering to 'having to use a GUID to reference the schema'. This is the part
I was referring to. Being unsure as to what a schema even is, I feel a bit
daft asking all these questions!
Thanks

 

[Allen Browne]

Okay: okay. The GUID is just the weird looking hex number in the string,
i.e.
947bb102-5d...
You don't have to do anything with that.
(GUID stands for Globally Unique IDentifier, i.e. it's a string representing
unique number(s).)

What you do have to do is change the line:
cn.Open "Data Source=C:\Data\Northwind2003.mdb"
so that the string refers to your mdb file, in the correct folder.

Hopefully you know to Ctrl+G to open the Immediate Window, and then enter:
? ShowUserRosterMultipleUsers()

 

[Jock W]

Fine - getting mixed up with Graphic User Interface!
Already changed the Data Source location to our .mdb.
Pasting "? ShowUserRosterMultipleUsers()" in to the Immediate Window and
hitting enter does nothing - just sits there, cursor blinking.

 

[Allen Browne]

Are you sure someone has the database open?
In shared mode?

To verify it is doing something, you can add:
Debug.Print "Fired at " & Now()
as the first line in the procedure.
Then as the next line:
Stop
When you run it, you should the the output of the first line, and it will
stop on the second. Then press F8 to step through the procedure and see
what's happening. Pause the mouse over the variables to see their value.

 

[Jock W]

I wish to extend my gratitude to Allen, David and Jamie for your help and
suggestions on this thread, however, it is proving (to me anyway) not to be
as simple as I had first thought, I am going to shelve this project for the
time being.

Thanks guys, it's very much appreciated.

 

[mscertified]

We sometimes use the LDB viewer and the problem we are now having is we have
many users on virtual office (they work home permanently) and connect via
terminal server. In that case the LDB entries are useless in identifying a
person because they are assigned automatically from a pool and they often
duplicate for different users.

-Dorian

Hi David. It's been ages since I fiddled with this, but my recollection is
that the LDB may not be up to date (i.e. it retains info on users who have
logged out), so the newer approach is more useful.

As always, we would love to hear from anyone who uses these regularly.

 

[David W. Fenton]

It's been ages since I fiddled with this, but my recollection is
that the LDB may not be up to date (i.e. it retains info on users
who have logged out), so the newer approach is more useful.

Yes, but that's only a problem for the method that reads the LDB
file directly. It is *not* a problem with the msldbusr.dll (with Jet
4 or Jet 3.5 data) because you can filter those (if you look at my
DLL-based form and compare it to the UserRoster-based form, it is
mostly identical).

As always, we would love to hear from anyone who uses these
regularly.

I don't think there's actually issue, since the DLL has always
allowed you to get the current list, versus the list of everybody
who has ever logged into the LDB, and the users currently listed as
suspect. The latter seems not to work in Jet 4.

My problem with the UserRoster is that the only way to get this same
information is to walk through each row of the returned recordset
and check the values for currently logged on and suspect. This is
not a very useful interface for a *database*.

The DLL did the processing in the DLL based on what you asked for
and returned only the users that matched the criteria you'd
requested.

 

[David W. Fenton]

I wish to extend my gratitude to Allen, David and Jamie for your
help and suggestions on this thread, however, it is proving (to me
anyway) not to be as simple as I had first thought, I am going to
shelve this project for the time being.

You might try downloading this database, which has nothing but the
form to do an ADO UserRoster lookup:

http://dfenton.com/DFA/download/Access/WhosOnADO.zip

It doesn't account for logging on to a secured workgroup, but it
should give you what you need. You can import the form and the
module into an existing application and user it there.

Dunno if that was what you were looking for, but it might help
someone else.

I also did some code cleanup in the Admin.mdb I posted yesterday:

http://dfenton.com/DFA/download/Access/Admin.zip

The changes were trivial and don't change functionality at all --
they just removed dead code (a bunch of stuff after an Exit Sub that
was a legacy of the form it was copied from).

 

[David W. Fenton]

We sometimes use the LDB viewer and the problem we are now having
is we have many users on virtual office (they work home
permanently) and connect via terminal server. In that case the LDB
entries are useless in identifying a person because they are
assigned automatically from a pool and they often duplicate for
different users.

Well, the latter issue has always been the case, because sometimes
Access opens more than one connection from the same database. I saw
this in Jet 3.5 back in 1998 or so and could never figure out what
was causing it.

I was running this on a terminal server just yesterday, and was able
to look up the virtual host name in the Terminal Server manager. But
maybe you don't have permission to view that.