Booting Remote Users

[SmallTime]

Using 2010

How do I go about logging off remote users?

I'm absolutely lost with this and don't even know where to start. The only thing I have is a table holding names of logged on user which I'm pretty sure will be needed. Other then that nothing. Any pointers on how I go about this would be much appreciated.

I'm currently migrating from 2000 and have updated both FE and BE to accdb format, so no more mdw file to play around with and no more CurrentUser() function.

SmallTime

 

[HiTechCoach]

See:
Detect and Logoff Idle Users

Detects and ejects idle users after set time period.

Boyd Trimmell aka HiTechcoach
Microsoft MVP - Access

 

[The_Doc_Man]

You can't log off remote users. But if they have a front end stored locally, you can have a hidden form - perhaps a "splash" page - that minimizes and turns invisible, but really still stays there. Fix up that form with a timer. You have to define your terms, but the idea is to have this timer that wakes up every so often. It tests something to see if it should go away or wait. If it is time to go, have it do an Application.Quit, perhaps with the really rude technique of having it iterate through your CurrentDB.Forms collection to close all open forms (except itself, of course). Then do the Application.Quit and >>poof<< they are gone.

The trick, of course, is to decide when and how to do this. To me, if someone is connected across a midnight, I would be well within my rights to toss 'em.

 

[SmallTime]

Thank for the replies

I wasn't overly worried about users staying logged on for long periods. In fact I expect every user to stay logged on for at least 7 hours a day. It was just for updates to the back end and also ensuring users always have the most up to date front end (latest version is loaded at log-on).

The way I'm going to go about it, after having a little think, is the same rout as The_Doc_Man suggests. Here's my thoughts although I have yet strated writing it, so not sure if it'll work.

1) at log-in store the user name in a hidden field on my MainMenu Form which always stays open.

2) Store the username in a table say TblCurrentUsers that has a couple of other fields including a check box that's true if logged-on (I know this seems surplus as simply having the username in the table means they're logged on, but it helps me visualise)

4) Remove the user name when logging off so TblCurrentUsers only holds current users. This works ok if the user logs off using my Exit button from which the delete query fires. However I'm not quite sure how I'm going to handle users that persist in closing the app from the dreaded 'X' hmm. Perhaps firing the code from the OnClose event will do the trick.


3) On an admins form display the current users. To log someone off uncheck the related checkbox and have timed coded fire from the users MainMenu as per The_Doc_Man's suggestion.

4) Perhaps check for unsaved (dirty) data before closing each form. Then .quit.

5) Make sure there's a way of stopping users from logging backing in immediately and before you've updated. I'll be using an autoexec macro to check a yes/no field in a table before proceeding to open the front-end

That's my random thoughts so far. As I said I haven't completed anything yet. Will be starting later today.

Many thanks again guys
SmallTime

 

[HiTechCoach]

SmallTime,

Thank for the replies
I wasn't overly worried about users staying logged on for long periods.

Logging out idle users in a database can help with performance.

Note:
The key is Splitting your Access database into application and data (Click Here)

 

[HiTechCoach]

4) Remove the user name when logging off so TblCurrentUsers only holds current users. This works ok if the user logs off using my Exit button from which the delete query fires. However I'm not quite sure how I'm going to handle users that persist in closing the app from the dreaded 'X' hmm. Perhaps firing the code from the OnClose event will do the trick.

Well ,... the dreaded 'X' is the Windows standard and should be allowed/used. A well designed UI used the dreaded 'X' effectively.

 

[SmallTime]

Thanks HiTechCoach,

for some reason I can't open your link. I get the message;

"Oops! Google Chrome could not connect to www.hitechcoach.com"

Anyhow, if the link shows me how to spilt my app to FE BE, then I've already done that a while back.

I also take your point about idle users and will make that my very next change after I've done the boot thingy.

Thanks for your advice, well it was well directed and gratefully received.

SmallTime

 

[SmallTime]

I had another thought which I'll share in case others searching this post also follow the above.

In my BE TblCurrentUsers I'm going to add a field to hold a marker for the current form the user has open. (down side is that I have to remember to add update code to every open form event) That way I can identify anyone who's using a critical part of the app, data entry for example, and can sent them a message or give them extra time before closing the app down.

 

[HiTechCoach]

Thanks HiTechCoach,

for some reason I can't open your link. I get the message;

"Oops! Google Chrome could not connect to www.hitechcoach.com"

Anyhow, if the link shows me how to spilt my app to FE BE, then I've already done that a while back.

I also take your point about idle users and will make that my very next change after I've done the boot thingy.

Thanks for your advice, well it was well directed and gratefully received.

SmallTime

Not sure why you can't see the link. It has been viewed 10+ times already today.

The link is not so much how to split your database but everything else that goes with using a split database.

 

[HiTechCoach]

I had another thought which I'll share in case others searching this post also follow the above.

In my BE TblCurrentUsers I'm going to add a field to hold a marker for the current form the user has open. (down side is that I have to remember to add update code to every open form event) That way I can identify anyone who's using a critical part of the app, data entry for example, and can sent them a message or give them extra time before closing the app down.

I have 1000+ users in my Access front ends at many locations. I have rarely needed kick active users out.

Curious, why all the effort and extra overhead? Is this something you do often?

 

[SmallTime]

Finally manage to open the link.

Methinks my computers having a day. This is my 3rd attempt to submit this post. I'm gonna Ctl C it this time.

I make BE changes about once or twice a year and have anywhere between , depending on the client, 20 - 100 concurrent users. So I need to make sure everyone's logged out before the any BE update. Also, sometimes I'll have FE change that needs to be rolled out simultaneously so logging users out and forcing them log in again ensures they're all using the same FE version. (New version is checked for at log-in)

Minor changes to Fe I'm not overly concerned with as as they'll be updated the next time users log out and in again at their own convenience.

Out of interest how do you, with 1000 users, make BE changes without booting everyone first?

Re the overhead, yes it'll be painful as I have around 170 forms in the mdb, but as I'm going to the trouble of re-writing everything and moving to accdb I might as well take the hit now and hope the end justifies the pain. I'll be reducing the number of forms as I'm getting pretty close to the limit and need to think about space for future updates.

Regards
SmallTime

 

[HiTechCoach]

I do BE update usually only once a year. The Front ends get updated quarterly and some monthly.

Download is planned and scheduled. This is critical for the shops that go 24/7. It is schedule for after hours or when the most users are out of the system.

To take the back end down:
I create a file in the folder with the back end. The front ends check to see if this file exists. If found the front end shuts down before attempting to touch the back end. This allows the back end to be processed without worrying about any front end logging in. To bring the back end online again I just delete the "flag offline" file. The front ends that are already logged in check for the "flag" file and alert the user.

 

[SmallTime]

Fantastic Site. It's now in my bookmarks as part of my ref library. I've only just had a cursory look but will be scouring it in details when time permits. I'll be keenly looking at relinking BE through VBA (something I've never managed to do but always wanted to).

Currently I have a single BE, which with one client is now 950mb after 4 years of data entry. later this year I'm going to split this in multiple BE's, I think 4 should do the trick. Are there any gotcha's in terms of relationships that I should be aware of? Should I have 4 BE + 1 that has noting but linked tables and handles relationships?

I be grateful for your insights on this.

 

[HiTechCoach]

Fantastic Site. It's now in my bookmarks as part of my ref library. I've only just had a cursory look but will be scouring it in details when time permits. I'll be keenly looking at relinking BE through VBA (something I've never managed to do but always wanted to).

Currently I have a single BE, which with one client is now 950mb after 4 years of data entry. later this year I'm going to split this in multiple BE's, I think 4 should do the trick. Are there any gotcha's in terms of relationships that I should be aware of? Should I have 4 BE + 1 that has noting but linked tables and handles relationships?

I be grateful for your insights on this.

The down side to multiple back ends tis that you can't create define relationships between tables in different back ends and Enforce Referential Integrity. Referential Integrity (RI) is critical. Because of this I avoid split back ends. I have only done it a few time where the table could be split into groups and still allow the Reinforcement of RI.

I would NOT keep using a ACE/JET (Access) back end. THE data has just outgrown the ability OF ACE/JET to properly handle it. It is definitely time to migrate to an SQL Server.

 

[SmallTime]

Ah

I'll have to think of another strategy then as my knowledge of SQL server is zilch, nothing, zero. I wouldn't even know where to begin.

On the other hand I do have about a year or so to learn. I just don't fancy or have the time for that matter to learn a new trick, it's taken me the best part of 5 years to get this far with access.

Regards
SamllTime

Regards

 

[HiTechCoach]

FWIW, check out these SQL Server Links

If the back end is already at 1 GIG I would look into migrating ASAP.

Curious, are you storing files or attachments in the database?

 

[SmallTime]

No files, no attachments, no pictures just text.

I know Access has a max size of 2Gig and suspect that it's more like 1.5 gig before things get wobbly. I was in the back of my mind hopping Ms would have increased the max size in 2010, but it seems that's not to be.

I'm currently migrating one of my Db's to 2010 version which will probably take the best part of 6 months or so. Once that's done I think I'm going to be forced to look SQL Server.

I not ashamed to admit the thought of jumping in at the deep end again feels more than a little daunting, even a bit scary as it's out of my expertise, but like everything else I'm sure it'll be less like swimming through treacle once I take the first dip into SQL server.

Thanks for link.

SmallTime

 

[HiTechCoach]

FWIW: I will not use Access 2010 in production yet. Just to many issues (bugs). I an waiting on Office 2010 SP1 update to be release. Then I will test everything again. I will only use Access 2007.

I know Access has a max size of 2Gig and suspect that it's more like 1.5 gig before things get wobbly. I was in the back of my mind hopping Ms would have increased the max size in 2010, but it seems that's not to be.

In a way Microsoft already has. They made using an SQL server back end easier in 2010. Microsoft has been pushing developers to use an SQL Server back end since Access 2000 first was released. That is why with Access 2000 the default was to use ADO not DAO.

I'm currently migrating one of my Db's to 2010 version which will probably take the best part of 6 months or so. Once that's done I think I'm going to be forced to look SQL Server.

WOW. Why taking soooooog long? Would you mind sharing a little about why it will take 6 months to convert?

The Access 2003 databases I have convert to a ACE (.accdb) running 2007 took no more than a week to tweak and fully test. I still have lots of VBA code that was used in Access 2.0 that has gone to 97, 2000,2002,2003, and 2007 with very little or no changes.


FWIW: I have an Access app built in 2003. The MDE has been used with Access 2007 for years without any issues. This way I can use the same MDE for both Access 2003 and 2007. Still waiting on 2010 SP1.

 

[SmallTime]

The App I'm redeveloping was my very first attempt at writing a DB, previous to that I hardly even knew how to use a mouse let alone how anything else. A kind workmate once spent about 6 hours showing me what a Db was and the basic principles. Fortunately I managed to commercialise my first attempt\practice project (it took me 2 years). That system now, instead of being a hobby, supports my whole family, so it realy has become a life-line.

Since then large companies have jumped into my niche and have been drawing my customers away. So I'm starting from scratch again to make it more functional, better UI and allow greater the user more options that they have control over. It's quite a task as there's somewhere around 180 forms, perhaps 150 queries and reams of code sitting behind each form.

I could reuse a lot of the objects as they are, but really want to have a top to bottom re-write so I know my existing customers have something that I can honestly say is my best effort. The existing system is fine and works well, but I know it could be done better.

I've learned a trick or two over the years and hopefully will reduce the number of objects and improve the coding.

SmallTime

 

[HiTechCoach]

Ah ... not really Access version upgrade issues but a total rewrite of the application.

What I have found is that the best time to upsize the back end to Microsoft SQL Server is when you are doing a rewrite. I do believe that at the rate your client's data is growing you really can't wait.

FWIW: My experience has been that using SQL also gives you a competitive advantage. It really does open up what you can do with your application.