Who's In The Database?

[Guest]

Hi,

I'm wondering if there is an easy way to display the usernames of all people
accessing a database at the same time. I would rather avoid having users log
in to the database. They are already given access to the server on which the
database resides, and that's enough. All I would like to do is to display a
list of current users (i.e. those who have the database file open).

Any suggestions are appreciated.

Eric

 

[Granny Spitz via AccessMonster.com]

I'm wondering if there is an easy way to display the usernames of all people
accessing a database at the same time.

In the startup form, place this code:
http://www.mvps.org/access/api/api0008.htm. Save the user name in a table
and the date/time. Have this startup form open a hidden form when it closes.
In the hidden form's close event, put code that deletes the user's record in
the table. When the user quits the app the hidden form will close, deleting
the record.

All I would like to do is to display a
list of current users (i.e. those who have the database file open).

Create a query that querys the user table. Either display the query when you
want to see all the users, or create a list box on a form and set this query
as the rowsource.

 

[Guest]

Thanks, I'll give that a try. Looks like a possible way to create a simple
audit trail, too.

 

[Granny Spitz via AccessMonster.com]

Thanks, I'll give that a try. Looks like a possible way to create a simple
audit trail, too.

You're welcome, and you're right. It's perfect for the audit trail.

 

[Ron2006]

Beaware that if a person aborts out of Access or there is a power
outage - that type of thing, then the table will be incorrect untill
then exit normally again.

If you are just trying to see who is on, the following may be usefull.

Try this link: http://www.mvps.org/access/modules/mdl0055.htm

it has a small access app that looks at the ldb file and will tell you
all of the users that are logged into mdb that you reference.


really neat and small, and the code for the form can be imported into
any app that you want or run standalone out of this app. With that you
may be able to tell what machine is actually holding open the ldb file
without having to try to guess.


Ron

 

[Jamie Collins]

Save the user name in a table

Sounds a bit 'manual'.

What about this?

http://support.microsoft.com/kb/287655

"The user list feature provides a way of determining who is currently
connected to a Microsoft Jet database. The list can be obtained via the
ADO programming interface and returns the following information for
each user:
· Name of the computer being used.
· Security name, that is, the user ID.
· Whether or not the user is currently connected to the database (A
user's ID remains in the lock database until the last user disconnects
or until the slot is reclaimed for a new user connection.)."

Jamie.

--

 

[Granny Spitz via AccessMonster.com]

Sounds a bit 'manual'.

What about this?

http://support.microsoft.com/kb/287655

Yes, I can see how running that sub, opening the immediate window and seeing
this:

COMPUTER_NAME LOGIN_NAME CONNECTED SUSPECT_STATE
A67 Admin True Null
A67 Admin True Null
A67 Admin True Null
A67 Admin True Null
A16 Admin True Null
A42 Admin True Null
A48 Admin True Null
COMPUTER_NAME LOGIN_NAME CONNECTED SUSPECT_STATE
A67 Admin True Null
A67 Admin True Null
A67 Admin True Null
Conn Err Num : 0
Conn Err Desc:

then opening a spreadsheet to see all the employees who *usually* sit at the
desks where computers A67, A16, A42 and A48 are located is a *lot* less
manual labor than creating a table and a query to show the current users,
writing these 2 lines of code:

CurrentDb.Execute "INSERT INTO Logins (UserName, LoginTime) " & _
"VALUES ('" & fOSUserName & "', #" & Now() & "#);", dbFailOnError

and then later running that query to see who's logged in:

UserName LoginTime
JWayne 11/29/2006 7:38:58 AM
HBogart 11/29/2006 7:40:29 AM
IBergman 11/29/2006 7:42:17 AM
MMonroe 11/29/2006 7:43:04 AM

 

[punjab_tom]

exec sp_who2

SQL Server has a lot of functionality like this that is built into the
db.

MDB isn't suitable for a single user and a single record

-Tom

 

[Jamie Collins]

Yes, I can see how running that sub, opening the immediate window and seeing
this:

COMPUTER_NAME LOGIN_NAME CONNECTED SUSPECT_STATE
A67 Admin True Null
A67 Admin True Null
A67 Admin True Null
A67 Admin True Null
A16 Admin True Null
A42 Admin True Null
A48 Admin True Null
COMPUTER_NAME LOGIN_NAME CONNECTED SUSPECT_STATE
A67 Admin True Null
A67 Admin True Null
A67 Admin True Null
Conn Err Num : 0
Conn Err Desc:

then opening a spreadsheet to see all the employees who *usually* sit at the
desks where computers A67, A16, A42 and A48 are located is a *lot* less
manual labor than creating a table and a query to show the current users,
writing these 2 lines of code:

CurrentDb.Execute "INSERT INTO Logins (UserName, LoginTime) " & _
"VALUES ('" & fOSUserName & "', #" & Now() & "#);", dbFailOnError

and then later running that query to see who's logged in:

UserName LoginTime
JWayne 11/29/2006 7:38:58 AM
HBogart 11/29/2006 7:40:29 AM
IBergman 11/29/2006 7:42:17 AM
MMonroe 11/29/2006 7:43:04 AM

Do I detect a little sarcasm <g>?

Really, mine was a question to you rather than a recommendation to the
OP so no need to bite my head off (but granny what big teeth you have
<g>!)

Me, I'm more inclined to get into bed with susie: if you need this sort
of information you should consider asking Santa for a new SQL product.

Speaking of spreadsheets, another question could be: what if I used
Excel, rather than your form, to connect to the database? but I think I
know the answer to that one ;-)

Jamie.

--

 

[Granny Spitz via AccessMonster.com]

I'm fairly ignorant about using VB in Access (and Access in general - I'm a
hard core Excel user).

There's no time like the present to learn! If you study Access you'll be
able to do so many more things with the data, like ensure data integrity,
validate data entries, and run queries to find out all kinds of things about
your data.

What I want to do is a) save the unique ID created in
the audit trail table (known as tlkpUsers) when the user opens the DB, and
then b) when the hidden form's Close event is triggered, add the current time
stamp to that record.

Create your table with this structure:

UserName, Text
LoginTime, Date/Time
LogoutTime, Date/Time

Place this code in the startup form's open event:

CurrentDb.Execute "INSERT INTO tlkpUsers (UserName, LoginTime) " & _
"VALUES ('" & fOSUserName() & "', #" & Now() & "#);", dbFailOnError

This will insert a new record in the table indicating the user's name and the
time logged in. Place this code in the hidden form's close event:

Dim sUserName As String

sUserName = fOSUserName()

CurrentDb.Execute "UPDATE tlkpUsers " & _
"SET LogoutTime = #" & Now() & "# " & _
"WHERE UserName = '" & sUserName & "' AND LoginTime = #" & _
DMax("LoginTime", "tlkpUsers", "UserName = '" & _
sUserName & "'") & "#;", dbFailOnError

This will update that record with the user's log out time. For auditing
purposes, if there's no log out time, it means the user still has the app
open or else the app quit abnormally, like with a lost network connection or
a loss of power to the computer.

 

[Granny Spitz via AccessMonster.com]

Do I detect a little sarcasm <g>?

Only a little. <g> I know you're an Excel guy so I added the part about
looking up the information in a spreadsheet. I forgot to list the creation
of the table, startup form and hidden form as some of the tasks required.
There's actually more preliminary *manual* work to be done to implement my
suggestion than yours, so I can see where your comment is coming from. But
once it's set up it gives exactly what Eric wanted. Your suggestion shows
Admin for all user names, which means Eric would have to apply user level
security to get their login names, something Eric didn't want to do, or else
he'd have to cross reference the computer name with the user who usually
works on it. That's open to error when different people use the same
computer for his application.

Really, mine was a question to you rather than a recommendation to the
OP so no need to bite my head off (but granny what big teeth you have
<g>!)

Whoops! I didn't mean to come off like that. Sorry. I was kidding about
the spreadsheet because I know you favor Excel.

Me, I'm more inclined to get into bed with susie: if you need this sort
of information you should consider asking Santa for a new SQL product.

Susie could use the company. Susie's pretty lonely now that Aaron dropped

Speaking of spreadsheets, another question could be: what if I used
Excel, rather than your form, to connect to the database? but I think I
know the answer to that one ;-)

I'm sure you do. It will use the information from the workgroup information
file you're currently joined to, which is probably the default workgroup.
Admin on machine 'whatever'.

 

[Jamie Collins]

Only a little. <g> I know you're an Excel guy so I added the part about
looking up the information in a spreadsheet.

I trust you picked up on my feigned 'offence' ;-) I'm always open to
persuasion that a certain approach sucks and this so-called 'user list'
doesn't appear to be workable.

As for querying spreadsheet data using Jet SQL, I've moved on from that
gig but I know there are a lot of people out there who seek to use
Excel to look at Access data. It's a fact of life and is best planned
for from the start: data integrity, validation rules, permissions,
helper VIEWs and PROCs, being ANSI query mode neutral (supporting both
* and % flavors of wildcard etc), etc.

Jamie.

--