Compacting Issue

[Scottie31]

I am using Access 97 and I cannot compact as it shows people are in the database. Is there any way to force users out of the database?

Thanks.

 

[HiTechCoach]

Is your database split?

 

[Scottie31]

No....the database is not split and we have approx. 15 user that may be using it at anytime.

 

[MSAccessRookie]

No....the database is not split and we have approx. 15 user that may be using it at anytime.

EEK! That is like playing Russian Roulette, where Database Corruption could be the final outcome.

Step #1: Get approval to split the database into a Back End containing the shared data, and a Front End containing everything else.
Step #2: Put the Back End into a common shared folder.
Step #3: Put a copy of the Front End on the desktop of EACH User that will be using the database.
​

These steps will not only eliminate most of the potential for Database Corruption, but should also eliminate the Compact/Repair Issues as well.

 

[AC5FF]

Scottie

I have the same problem - I'm working on splitting my DB now..
If you'd like, I'll could look for the code/etc that was used when this monster I have was created. It basically will pull up a list of everyone that is online w/the DB. I have the option to "Kick" everyone out - which doesn't really close them down, but prevents anyone else from logging in. Then I can 'search' for that individual and have them close out the DB.

I never subscribed to the 'Russian Roulette' as MSAccessRookie stated - until recently.
We've been using this shared DB for nearly 10yrs without any major problems. In all that time I think the DB crashed on us once. Until recently that is. We just had several computers upgraded to Vista. So now I have users running XP and Vista accessing the same DB. I'm now fixing crashes about weekly!! So, needless to say I've been pushing hard to get the FE/BE up and running... And fully subscribe and believe in the "russian roulette" compairison

 

[RossWindows]

I use the same tool to keep new users from logging in. I have some databases on a network which have 50-100 users at any given time.
Most of the users use a thinclient & log into the terminal server.
Since the database resides on a server, when I need to kick everybody out I just call IT and have them kick out any users that are accessing the file.

There is one solution that could solve all of the above:
Whenever a user opens the database, have it open a hidden form with a timer interval (60000 = 1 minute), so every minute, the hidden form checks your "kick everybody out" setting and closes their session if necessary

Pros: One setting can kick everybody out.
Cons: The users may lose unsaved data
If you have multiple front-ends, you will have to add the timed-hidden-form to each one
If you have not added control to the ShiftKeyBypass setting, a user could potentially open the database anyway.

 

[AC5FF]

I have a similar timer - to keep people from opening the DB and leaving it sitting open in the background. If left inactive for 5min it automatically will log the user off.

 

[dmcdivitt]

Besides splitting the database between front and back end, the front end should be copied to individual workstations. I wrote a launcher in VB6 a long time ago to do this which will synchronize workstation versions as well as tell people the application is offline. With this approach you can deploy a new front end anytime and users will be updated next time they use it.

Within the access app on the main form I sometimes run the timer to check whether a file exists at the LAN path where the front end was copied from. Only check for presence of the file and do not open it. The file can have zero bytes. If the file is found a modal popup is opened, also with a timer. If it counts down, access will close. If the user closes the popup the user can then finish work and close manually, but the popup will continue to occur.

 

[MSAccessRookie]

Besides splitting the database between front and back end, the front end should be copied to individual workstations. I wrote a launcher in VB6 a long time ago to do this which will synchronize workstation versions as well as tell people the application is offline. With this approach you can deploy a new front end anytime and users will be updated next time they use it.

Within the access app on the main form I sometimes run the timer to check whether a file exists at the LAN path where the front end was copied from. Only check for presence of the file and do not open it. The file can have zero bytes. If the file is found a modal popup is opened, also with a timer. If it counts down, access will close. If the user closes the popup the user can then finish work and close manually, but the popup will continue to occur.

Several people in this forum have shared their own version of FE Update procedures. I am not aware that any of them provides user notification of the kind that yours does. Perhaps you can share yours as well?

 

[dmcdivitt]

The launcher reads a config file. The config file has sections with [appname]. Each section corresponds to arbitrary application names given to applications. Within each section is:
access 97=filename
access 2000=filename
access 2003=filename
access 2007=filename

for as many access versions are deployed. The launcher will also copy and register activex controls so they will be there when access opens. Within a section, I forgot the noun, but if offline= is found, whatever text is found will be displayed to the user and the application won't be launched.

The launcher EXE is usually given the name of the application to be run. Several of these EXEs can be present. The name the EXE has will be looked up in the config file. Also a single EXE can be used for all applications and the application can be passed as a commandline argument.

It has a help screen and other stuff as well as interactive debug messages to show what's happening when activex controls are being copied and registered. Holding the INS key down when starting the launcher will bring up these options.

 

[AC5FF]

Just because it was discussed earlier; and I happened to run across it looking for another problem; here is the VB code we use to 'time out' a user:

Private Sub Form_Timer()

' IDLEMINUTES determines how much idle time to wait for before
' running the IdleTimeDetected subroutine.
Const IDLEMINUTES = 10

Static PrevControlName As String
Static PrevFormName As String
Static ExpiredTime

Dim ActiveFormName As String
Dim ActiveControlName As String
Dim ExpiredMinutes

Form_MD5InitMapData.Text38.Value = IIf(Form_MD5InitMapData.[D23 Date] < [Form_Config subform].D23_Date, "Bounce Required!", " ")

Form_MD5InitMapData.[Ping] = Now
Form_MD5InitMapData.Refresh

If GetUserNameA = "JohnsonJW" Then
DoCmd.Close acForm, "DetectIdleTime"
End
Else
If Form_MD5InitMapData.[Kick User] Then IdleTimeDetected ExpiredMinutes
End If


'**** Send Message
If Form_MD5InitMapData.Message Then
iResp = MsgBox(Form_MD5InitMapData.[Message Text], vbOKOnly + vbQuestion, "MAPDATA Message")
Form_MD5InitMapData.Message = False
Form_MD5InitMapData.[Message Text] = " "
Form_MD5InitMapData.[Message Recevied] = Now
Form_MD5InitMapData.Refresh
End If

If Form_MD5InitMapData.Recordset![NoIdle] Then Exit Sub

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
If GetUserNameA <> "JohnsonJW" Then
Form_MD5InitMapData.[Last Log Out] = Now
Form_MD5InitMapData.Refresh
Application.Quit acSaveYes
End If
End Sub