Kicking users out of database

[CEV]

I have a split database. There are times when I need to access the backend
to make changes or additions, but often it is always open by a user. I am
wondering if there is a way to have Access automatically close on the users
pc after a set period of inactivity?

Thanks,

CEV

 

[Douglas J. Steele]

Take a look at http://support.microsoft.com/kb/210297

(Don't worry if you're not using Access 2000: that approach works in all
versions)

 

[CEV]

Thanks for the response Doug, but I am still a little confused. I am not
familiar with exactly where to enter these codes. I entered the following
under the OnTimer Event Procedure:

Private Sub Form_Timer()
' IDLEMINUTES determines how much idle time to wait for before
' running the IdleTimeDetected subroutine.
Const IDLEMINUTES = 1

Static PrevControlName As String
Static PrevFormName As String
Static ExpiredTime

Dim ActiveFormName As String
Dim ActiveControlName As String
Dim ExpiredMinutes

On Error Resume Next

' Get the active form and control name.

ActiveFormName = Screen.ActiveForm.Name
If Err Then
ActiveFormName = "No Active Form"
Err = 0
End If

ActiveControlName = Screen.ActiveControl.Name
If Err Then
ActiveControlName = "No Active Control"
Err = 0
End If

' Record the current active names and reset ExpiredTime if:
' 1. They have not been recorded yet (code is running
' for the first time).
' 2. The previous names are different than the current ones
' (the user has done something different during the timer
' interval).
If (PrevControlName = "") Or (PrevFormName = "") _
Or (ActiveFormName <> PrevFormName) _
Or (ActiveControlName <> PrevControlName) Then
PrevControlName = ActiveControlName
PrevFormName = ActiveFormName
ExpiredTime = 0
Else
' ...otherwise the user was idle during the time interval, so
' increment the total expired time.
ExpiredTime = ExpiredTime + Me.TimerInterval
End If

' Does the total expired time exceed the IDLEMINUTES?
ExpiredMinutes = (ExpiredTime / 1000) / 60
If ExpiredMinutes >= IDLEMINUTES Then
' ...if so, then reset the expired time to zero...
ExpiredTime = 0
' ...and call the IdleTimeDetected subroutine.
IdleTimeDetected ExpiredMinutes
End If
End Sub

Sub IdleTimeDetected(ExpiredMinutes)
Dim Msg As String
Msg = "No user activity detected in the last "
Msg = Msg & ExpiredMinutes & " minute(s)!"
MsgBox Msg, 48
End Sub

Sub IdleTimeDetected(ExpiredMinutes)
Application.Quit acSaveYes
End Sub

I am also not sure where to find the IDLEMINUTES constant value to change
that.

Thanks,

CEV

 

[Douglas J. Steele]

You're only supposed to have one of the two examples given for
IdleTimeDetected.

The first one is there to demonstrate to you that it's working:

Sub IdleTimeDetected(ExpiredMinutes)
Dim Msg As String
Msg = "No user activity detected in the last "
Msg = Msg & ExpiredMinutes & " minute(s)!"
MsgBox Msg, 48
End Sub

The second one is what you'd use to actually shut down the application
without any warning:

Sub IdleTimeDetected(ExpiredMinutes)
Application.Quit acSaveYes
End Sub

IDLEMINUTES is defined as the first line in the sub (okay, the third line:
the first two lines are comments.)

 

[CEV]

I took out step 4 and it now works. Thanks for the help.

Thanks,

CEV

 

[dbahooker]

you don't need this-- what you really need is a reliable database that
allows for 24x7 backups

a real database-- like SQL Server-- would fit all your needs.
And it is FREE.

Come on, lose the training wheels!


-Aaron

 

[CEV]

How could I do this and give the user an option to keep it open if they
select OK say within 30 seconds? Would this be possible?

Thanks,

CEV

 

[CEV]

I have thought about that for the future, but that would be a big step for
me. When the time does come, will I be able to move my backend tables to SQL
and keep my front-end Access applications without losing any info?

Thanks,

CEV

 

[Douglas J. Steele]

Yeah, it's possible.

One way would be to declare a Global Variable in a module (can't be a class
module, nor a module associated with a form):

Public gbooShutdown As Boolean

In the Load event of the DetectIdleTime form you just created, set
gbooShutdown to False.

Create a new form (call it PromptUser) that has a label telling the user
that the application will be shut down in 30 seconds unless they click on
the NO button. Add two buttons onto the form: one labelled OK and one
labelled NO.

In the PromptUser form's Load event, set Me.TimerInterval = 30000

In the Click event of the NO button, you want:

Private Sub txtOK_Click()
gbooShutdown = True
DoCmd.Close acForm, Me.Name
End Sub

In the Click event of the OK button, you want:

Private Sub txtOK_Click()
gbooShutdown = False
DoCmd.Close acForm, Me.Name
End Sub

In the Timer event of the form, you want

Private Sub Form_Timer()
gbooShutdown = True
DoCmd.Close acForm, Me.Name
End Sub

What this does is if the user clicks on OK, it sets the global variable to
True and closes the form. If the user clicks on NO, it sets the global
variable to False and closes the form. If the user does nothing in 30
seconds, it set the global variable to True and closes the form.

Go back to the DetectIdleTime form, and change sub IdelTimeDetected to
something like:

Sub IdleTimeDetected(ExpiredMinutes)
If gbooShutdown = False Then
DoCmd.OpenForm "PromptUser", WindowMode:= acDialog
End If
If gbooShutdown = True Then
Application.Quit acSaveYes
End If
End Sub

What this does is if the global variable is False, it opens the PromptUser
form in Dialog mode (meaning that code processing stops until the PromptUser
form is closed). Once processing resumes, it checks the value of the global
variable. If the user closed the PromptUser form by clicking on OK or if the
user didn't do anything so that the form closed on its own after 30 seconds,
the global variable will now be True, so it shuts down the application. If
the user closed the PromptUser form by clicking on NO, the global variable
will still be False, so it doesn't shut down the application.

 

[Tony Toews [MVP]]

Note that Aaron Kempf is very monomaniacal when it comes to ADPs and SQL Server.
He will strenuously recommend ADPs as the only solution to any problem. However ADPs
have their quirks and haven't seen any new improvements in functionality in A2007.

I have thought about that for the future, but that would be a big step for
me. When the time does come, will I be able to move my backend tables to SQL
and keep my front-end Access applications without losing any info?

Yes, you can upsize the data to SQL Server without making significant changes to your
front end. You will have to tweak things of course. This could take between a week
and a month or two depending on the size of your database.

Also see my Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

There is a new tool from the SQL Server group.
SQL Server Migration Assistant for Access (SSMA Access)
http://www.microsoft.com/sql/solutions/migration/default.mspx

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[CEV]

I really appreciate your patience with me Doug. I almost have it working.
The new PromptUser form pops up when it is supposed to. Regardless if I
click OK or NO or do nothing, the form will close but the application stays
open. I think it may have to do with the Global Variable but I am not sure.
I entered the following in Load event of the DetectIdleTime of the form:

Private Sub Form_Load()
gbooShutdown = False
End Sub

But I am not positive if that is correct. You mentioned

Public gbooShutdown As Boolean

but I have no ideal what that means or where I should be putting that, if
anywhere.

Thank You again for your patience.

Chad

 

[Douglas J. Steele]

As I stated, you need to put

Public gbooShutdown As Boolean

in a code module (not a class module, nor a module associated with a form):.
Any module will do. If you don't have one, create one.

The fact that you didn't have that code and didn't get an error indicates to
me that you have not told VBA to enforce variable declaration. (For some
bizarre reason, Microsoft didn't make this the default, despite the fact
that it's essential to writing good code). While in the VB Editor, choose
Tools | Options from the menu and go to the Module tab. Make sure the
"Require Variable Declaration" box is checked.

You'll also have to go into every module, and change each one so that the
first two lines are:

Option Compare Database
Option Explicit

Now, this will ensure that you must always declare variables. If you've
missed declarations in what you already have, you'll run into issues until
you've declared them all. To help identify potential problems, go under the
Debug menu (while still in the VB Editor) and compile your application.

 

[dbahooker]

yes; you can keep your existing forms and reports
and you don't need to deal with any of this hassle

FILE, NEW, PROJECT EXISTING DATA

 

[dbahooker]

Tony;

just because I'm right and you're wrong-- you don't need to follow me
around and try to discredt everything I say
Trying to kick out users works all fine and good

WHAT HAPPENS WHEN ONE OF YOUR USERS DISABLES MACROS!?!

 

[dbahooker]

CORRECTION

MDB hasn't had a single bug fix in 10 years
and ADP _DOES_ have new functionality in Access 2007-- it supports SQL
2005 better now

Stop spreading mis-information you fat lazy retard


-Aaron

 

[CEV]

Thank You very much Doug. It now works perfectly!!!

Thanks,

Chad