Kicking out users for administrative tasks...

[storm75m]

I have several different front end Access applications that are used by many employees all day long. The front end applications access a single back end database that is on our network. This back end database is very large, and needs to be compacted nightly. The data in the back end database comes from an external data source, and I run an import procedure every night to import the data.
My problem comes into play when users don't close the front end applications before leaving at night. If the users leave part of the application running, then I can't import the new data because the user has the table locked. And I also can't compact the database.

Is there any way to programatically "kick out" any users that still have open resources to my back end database so I can perform administrative tasks? On one of the more heavily used front end applications, I have added code so that the application automatically closes at a certain time, but it would be much more efficient if I could control this from the back end database. Sometimes, even if no one has the application open, a resource is still open on the back end server for some strange reason. I'm sure others have encountered this same situation (where they need users out of a database), and I'm simply looking for suggestions. It would be great if there was some code that would just terminate all connections to the database. By the way, this is a read only database, there is no data entry involved, so this should make it even easier. Thanks in advance for any help or suggestions that you can provide.

 

[UncleJack]

I haven't tried this, but what about copying the backend to a different name, compacting it and then replacing the uncompacted database with the compacted one?

Uncle Jack

 

[ErikZ]

I can see two solutions to this.

One is to build some VBA code that shuts down Access after, say, midnight. Add it to all your front ends.

Two is to make a mini-table. Give it a single column, make it "Yes/No". Now add VBA code that checks the status of that table every five minutes, and if the box is checked, shut down Access. (You can make it nicer than that, give a pop up warning, etc...)

Personally, I would do option one.

 

[JaneInMA]

look for previous answers to this- I hope this link works
thread181-7646 use a table with three states 1 do nothing 2 put up a form and tell users the database is closing
3 kick them out
keep a form open all the time use the timer to check the status of the table and act accordingly
Private Sub Form_Timer()
Select Case DLookup("[LogOutStatus]", "tblSystem&quot
Case 1
' nothing
Case 2
DoCmd.OpenForm "frmLogoutWarning"

Case 3
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
DoCmd.Quit
End Select
End Sub
HTH

 

[UncleJack]

Eric:

The problem with your solution #1 is it's always after midnight. So you need a time range don't you? What happens if some night owl wants to run a program at 2 a.m. or the operation runs 24/7?

I think your solution #2 has some merit, but I would extend it to an hour of inactivity day or night.

The problem with both these is that they have to run on the workstation front ends. A better solution would be one that can be triggered from the administrator's stations shutting down all work stations on command. Unfortunatly, I haven't a clue how this could be done. Any ideas? I would certainly be interested.


Uncle Jack

 

[ErikZ]

As far as I know, it's impossible to find out who is connected to your database unless you've added some sort of code to all of the front ends. I had gotten the impression that the original poster knew who was using his database, and the front-end programs that they were connecting with.

It was a project given to me by the administrators of a large corporate Access database. "Give us something we can use to kick people out of our database, that won't corrupt the tables, no matter who made the front end." I couldn't find anything; it's just a weakness in Access.

I had also gotten the impression that he isn't in a 24/7 operation. Yes, it's always after midnight, are you trying to be cute jack? I suspect that if he was going to code this in, have it check every 15 minutes to see if the time is between 12am to 1am. He was just asking for advice, not cut and paste code samples.

I like the code sample that Jane posted though. Three states is a good idea, and it's easier to code.

 

[UncleJack]

Eric:

I downloaded the database referred to in Jane's post (Security.mdb). It appears to be a clever piece of work that uses the information in the ldb file to determine and display who's logged on to a particular database. (Start by clicking the referenced thread and then to the referenced database.)

That takes care of the lion's share of the problem; finding out who's connected. The next step might be to install some code in the backend that would connect to code in the front ends and logoff a front end that's specified by the administrator. Have a go at it Eric, should keep you busy for an hour or so.

Uncle Jack

 

[ErikZ]

Heh, well that was at my old job, and the problem was that anyone could make a front-end and link to the database. It would of been impossible to track down every front end out there and add the necessary code.

The solution to their problem was “Eh, screw it, we’ll be updating to a REAL database in the next year.”

 

[Guest_imported]

I wrote a function that closes a form on a front end db on command from the backend db. if it closes the form then there isnt any reason why it cant close the whole front end (close access).

the function doesnt allow the form to be open until the "update" is complete. i dont see why this couldnt be applied to the db and not just a form.

 

[tlbroadbent]

Check out faq181-99. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[UncleJack]

Hey guys, what about this for an idea?

Place a table in the backend with a boolean value in it. The value is normally false, but can be set by the system administrator to true. (I normally install such a table anyway to enhance network access by executing a DAO call to the table and then not closing it until the user logs off.)

On each front end install a form that is always open and insert code that uses the timer to check the table boolean value every x number of seconds. (The form can be invisible if need be.) If the check detects a true value then code is launched that begins an orderly shutdown procedure including messages to the workstation operator, etc. culminating in a docmd.quit statement. Delays can be built in to permit operators to finish critical work. The actual code in the shutdown procedure would depend on the application and what processes are critical to maintaining datbase integrity.

This way it doesn't matter who is logged on, because they're all going to get logged off after x number of minutes.


Uncle Jack

 

[mrgulic]

Well UncleJack, GREAT minds do think alike.
That is pretty much what i did. I just have it closing a single Form to free up a specified tables data.

The only difference is that i created a table in the front end (it is a network shared db so i only needed to create one table) that contains a T/F field and had a Form running with a timer that checked that value every few minutes. The form wont open up again until the value is reset.

It works great. There is no reason that it cant close the db down as well and give the users a few minutes to save their work. That wouldnt matter either because Access saves records automatically.

 

[ShortTexan]

FYI all.

http://support.microsoft.com/support/kb/articles/Q186/3/04.ASP

. Look at this link and see if it will help. It is my experience that 10 percent of the users create 90 percent of the problems. Finding out who they are and educating them has normally alerted me to problems they have that I am not aware of. Shutting down remotely can leave an orphan .LDB file that will still keep the database locked. Make sure your code not only shuts down the offending user but performs the housekeeping of deleting the .LDB if there are no users attached.

 

[mrgulic]

I sure hope you weren't suggesting that jack and myself were that 10%.

Otherwise thats a darn fine addition to our suggestion.

 

[UncleJack]

Oh, we've got the code for reading the ldb file and finding who's on the database. But it appears it isn't necessary to know who's logged on at the start of the process, it's only necessary to log off everybody. The actual shutdown code needs to account for making sure records don't get orphaned, etc. during the process so it's specific to a particular application. Once the docmd.quit line is executed on the last workstation there won't be an ldb file to worry about. The system administrator can monitor who's logged on and take down the backend on the server only when he knows everybody is off.

To the AccessHack: I think a better general solution is to place the trigger table on the backend. Having it on a workstation in a shared database is no problem, but most systems that I've seen aren't set up that way. As I mentioned previously I have each work station create a DAO link to a blank table on the backend and then don't close it until they log off. This speeds network performance since a link doesn't have to be established each time the system calls data from the back end. This table could and should be the one where the T/F field for shutdown is kept.

Uncle Jack