Making design changes in an Access 97 60-user split database

[Inquirer]

Is there any way to force users out of the database since if even one user
forgets to log out, I can't delete the ldb file, making it impossible to make
changes to any table they are using? Help! This makes it very difficult to
make changes since almost inevitably at least one of those 60 users is still
logged into the database, even in the middle of the night. This has been
extremely frustrating since I have been delayed attempting to make changes
available for two weeks waiting for a time when all users are logged out, in
spite of repeated reminders to be sure they properly log out of the
application when they leave for the day.

 

[Pete D.]

Yes, create a form and make it start up when database is opened as a hidden
form. Then make a table with a form for button, yes/no. I always have a
installation table to deal with server locations and other things that
change. Make a form to warn user that db is going to be shut down in X
minutes. Read code below and post back if you get lost.. Below will give
users around 5 minutes to save what they are doing and exit. Around 6
minutes they will be forced out.

Option Compare Database
Option Explicit
Private Sub Form_Timer()
Static MsgSent As Integer
Dim LogOff As Integer
Dim Db As Database
Dim LO As Recordset
Set Db = CurrentDb()
Set LO = Db.OpenRecordset("T_Installation_Variables", DB_OPEN_SNAPSHOT)
LO.MoveFirst
LogOff = LO!LogOff
LO.Close
Set LO = Nothing
Db.Close
Set Db = Nothing
If Me.TimerInterval = 200000 Then
DoCmd.Close acForm, "F_RequestAlert"
Call RequestAlert
End If
If LogOff = True Then
If Not MsgSent Then
DoCmd.OpenForm "F_ShutDownWarning", acNormal, "", "",
acReadOnly, acNormal
DoCmd.RunCommand acCmdAppRestore
Me.TimerInterval = 60000 'change to one minute
MsgSent = True
Else
Dim intx As Integer
Dim intCount As Integer
intCount = Forms.Count - 1
For intx = intCount To 0 Step -1
DoCmd.Close acForm, Forms(intx).NAME
Next
Application.Quit 'Log them off now.
End If
End If
End Sub

 

[Jeff Boyce]

You didn't indicate whether all 60 are using a single copy of the front-end
(i.e., located on a server), or if each user has his/her own copy of the
front-end on their desktop PC.

If the former, redesign to the latter!

You should be able to make changes to YOUR copy of the front-end, whether or
not other users are using THEIR copies.

Are you saying that you want to change the back-end?

Pete's approach is similar to one I've used. He's more polite than I am.
My version simply shuts down the application if there's been no activity
for, say, 20 minutes. This catches the "lunch" crowd who launch the
application then leave for lunch...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Inquirer]

Thanks, Jeff. Much appreciated. I think I'll use Pete's approach (even though
it's a bit more work). Like you said, he's more polite.

To answer your questions, all users have their own front end copy and yes,
the changes need to be made to the back end. We've been pondering
increasinglty strong-armed tactics to get users (particularly chronically
lazy - or "forgetful" - users) to properly log off. This may help avoid those
tactics.

Thanks to both of you.

 

[Inquirer]

Pete,

Thank you! I may need to ask a couple of followup questions as I implement
your code (which, as Jeff pointed out, goes to some lengths to be polite),
but I think I get the gist glancing at it. I guess it's all about how much
you want to couch that final Application.Quit. Your help has been greatly
appreciated!

Inquirer

 

[Pete D.]

Don't worry about it, Jeff had a little tongue in cheek going on. Can't say
I haven't ever slammed a user that wasn't being helpful. He'll jump in when
I go brain dead. Pete D.

 

[Tony Toews [MVP]]

Is there any way to force users out of the database since if even one user
forgets to log out, I can't delete the ldb file, making it impossible to make
changes to any table they are using?

HOW TO: Detect User Idle Time or Inactivity in Access 2000 (Q210297)
http://support.microsoft.com/?kbid=210297
ACC: How to Detect User Idle Time or Inactivity (Q128814)
http://support.microsoft.com/?kbid=128814

However we found that the code which runs on the timer event must be
disabled for the programmers. Otherwise weird things start happening
when you're editing code.

Also print preview would sometimes not allow the users to run a menu
item to export the report to Excel or others. So you had to right
click on the Previewed report to get some type of internal focus back
on the report so they could then export it. This was also helped by
extending the timer to five minutes.

The downside to extending the timer to five minutes was if a person
stays in the same form and at the same control for considerable parts
of the day, ie someone doing the same inquiries, the routine didn't
realize that they had actually done something. I'll be putting in
some logic sometime to reset this timer whenever they do something in
the program.

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/

 

[Pieter Wijnen]

I Usually use a table with start & end time to set a time window for when
the App shouldn't be accessed by users
as I'm not really concerned wether they do something or not, only if they're
in there when I need to make design changes or to kick them out after hours
to Compact & Repair the back end ...

Pieter

 

[Guest]

If you make changes in the middle of the field list, the changes are
not fully effective until you compact the data file. You can use linked
tables until the data file is compacted, then you have to re-link,
compact, or re-distribute the front end files.

logged into the database, even in the middle of the night. This

There are also two other places where you can do this: you can add
automatic shutdown to Windows, forcing the PC to shut down, and
you can use the server admin tools to break the connection to the ldb
and mdb.

The Jet cache has a write-back interval measured in seconds, and
the network client not much different, so if you are looking at an mdb
in the middle of the night it's fairly safe to do.

Not that there is anything wrong with adding timer code, but if it's
a one-off it may be overkill.

Also, I sometimes have users connected when I make table changes...
I've got start up code in one place that checks the table structure
and updates from inside the application. Having users connected
doesn't prevent that, the application is not designed to have users
stay connected to individual tables for very long. Of course I never
delete fields like that, and DDL always adds fields at the end of the
field list, so I don't get the compact/link problems caused by putting
fields into the middle of the field list.

(david)

 

[Tony Toews [MVP]]

If you make changes in the middle of the field list, the changes are
not fully effective until you compact the data file.

I'd argue with that statement.

You can use linked
tables until the data file is compacted, then you have to re-link,
compact, or re-distribute the front end files.

The dreaded -1517 error message.

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/