Want to show the number of users in a form (accessXP)

Topic

New Reply

How do I code in a text box the number of users in a form,
What I need is as a person opens a form if they are the
second person in the form need them to see 2 …and so on?

Reply | Quote

Replies

Microsoft provides a way of counting or listing all users who are logged in to a database: see
HOW TO: Check Who Logged into Database with Jet UserRoster in Access 2000.

Counting the number of users who have opened a form is trickier. You could create a table with two fields: FormName (text) and UserCount (numeric, integer, default value 0). Create a record in this table for each form you want to monitor.
You would have to write code in the OnLoad event of the form to add 1 to the UserCount field of the record corresponding to the form (and displaying the new UserCount in a text box), and in the OnClose event of the form to subtract 1 again.
But … if the form isn’t closed properly (Access crashes, or the user pulls the plug, or power fails), the OnClose event doesn’t occur, so UserCount won’t be decreased. I don’t know how to get around this, apart from periodically setting all UserCounts to 0 at a moment you are sure that nobody has a form open.

Reply | Quote

Hans:

Thanks for the suggested MSKB article link. Have got it working in one of my “secured” apps. I specified the backend (full path) in the “Data Source” code lines since it seemed to make sense that the backend was the part that had multiple simultaneous users.

Since this is a “normal” split situation (backend on the server / frontend and .mdw file on the workstations) I was surprised to find that it showed me logged in to the back end as ‘Admin’. I thought I had a pretty good understanding of Access Security, having worked with it for a few years. Now I guess I’m missing something. Security was handled before splitting the app. The normal make new workgroup file, join it, give Admin a password, create a new user (myself) in Admins Group, take Admin out of Admins, import everything to a new database under the new workgroup so I’m the “owner”, create necessary users and groups, assign permissions to the Groups depending on function, and take all permissions away from the Users group. Then the database gets split and the backend placed on the server.

Then a shortcut is created calling the appropriate security .mdw file, and I rejoin System.mdw (where all workstations are). The frontend mdb and the security mdw files are placed in a new directory on the workstations and linked to the server backend. The shortcut goes on the desktop.

Since Admin is now part of Users only and that group has NO permissions, how can Admin be showing up as logged at my workstation? When checking permissions, the linked backend tables show the proper group permissions (Admin has none!)

I guess my question is: If I need to provide additional security for the back end, how do I do it? My “bible” (MS Security FAQ) seems to have been leading me in circles today. Maybe someone can help. I’m probably missing something obvious and am over analyzing.

Thanks

Reply | Quote

Are you getting a login prompt when you start the short-cut? If not, you are really logging in as Admin. This can get mighty confusing when you are working with multiple .mdw files, so you may not be using the security file you think you are. A shot in the dark, but sometimes this business requires it.

Reply | Quote

Thanks for trying. We seniors have to stick together.

Yes … and if I try to log in as admin with the assigned admin password I get the “You can’t get there because you don’t have the necessary permissions” message (or words to that effect!). You know the one I mean.

Reply | Quote

That probably indicates that the way you’re starting the session of Access used to log in to the database is NOT using your secured mdw file, but the default file. If you try that on a properly secured back end, it won’t work.

Reply | Quote

Hi Dick,
Does your shortcut include the full path to msaccess.exe? If not, I seen this kind of thing cause the default .mdw file to be used, which will cause all sorts of funnies. Run the tool against both the FE and the BE and see what it tells you about who is logged in. As far as the logged in as Access part, that can also happen as long as Admin is still the owner of the database. If you do what you described, but using the security wizard, I’m sure you’ll find it all hangs together. And I appreciate that hanging together for us “more mature” loungers – we could even include Charlotte!

Reply | Quote

Wendell & Charlotte,

Thanks for the replies. Stayed out of the office Sat & Sun, but am back in today. My intention is to go back over all steps beginning with creating and joining the work group, making a new database, and importing all files including the backend data files. That way there’s no question about my being the “owner”, although the current fe security shows me as the owner. I work in a directory called “Shared”. The database and mdw files are in c:shared.

Full shortcut text is as follows: “c:program filesmicrosoft officeofficemsaccess.exe” c:sharedMitProj.mdb /wrkgrp c:sharedMitProj.mdw (My usual practice is to create the mdw file with the same name as the mdb file, which avoids confusing myself.)

Once the new db is split, is it necessary to reapply security separately to the backend? If so, this is probably what I’m missing. BTW I’ve tried the Security Wizard a few times, but don’t regularly use it since I thought I finally understood the security routine.

Thanks for the input … am off to rebuild the whole thing.

Reply | Quote

Yes, you have to apply security to both front and back end separately.

Reply | Quote

Charlotte:

Thanks … will do. Have used the Sec Wiz this time. Now will split and secure back end, then copy to server location.

Reply | Quote

Charlotte and Wendell:

Thanks … everything working correctly. Now when someone tries to open the back end on the server by double clicking the file, the “You don’t have the necessary permissions …” message.

I had been missing the obvious — Open the back end and secure the objects!

Thanks again,

Reply | Quote

That’s the way it’s supposed to work. Good for you!

Reply | Quote

Hi Hans

I tryed ShowUserRosterMultipleUsers and it displayed the following:

I am the only one in the database, how should I interpert the following?

COMPUTER_NAME LOGIN_NAME CONNECTED SUSPECT_STATE
PC4 Admin True Null
PC4 Admin True Null
PC4 Admin True Null

Thanks, John

Reply | Quote