How to create a timeout to throw users out of the database? 4

[PaulCooper]

Hi,

I have a database that has a front end and back end. I have some lazy users who do not log out so an access.LockFile.9 is generated which prevents me from updating the code. (I cannot delete this file ~ says someone is using it, does not say who!) It is a major pain to work around folk to a) find the culprit and b) log them off.

Ideally I would like some code that logs users off after (say) 2 hours inactivity, or if that is difficult, 12 hours after logging on ~ so at least once a day (first thing) the database is available for maintenance.

Any suggestions? Paul

 

[abaldwin]

The ever-present "LUS" (Lazy User Syndrome)

I will be interested to see other post to this problem. There are many request for this type of code on the net and probably even some in these forums.

There are a couple of problems with this type of request. The biggest of which is that kicking someone out of the database without properly closing it, as I am sure you are aware, will cause corruption pretty quickly.

With access I do not know of a way to actually monitor current activity. A user process may be in the middle of updating a boat load of records and the user leaves for the day. If you just boot them the data can become corrupt and their process will terminate abnormally.

This should be an interesting thread.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Dryseals]

Thats funny, I was just talking with a friend on how I accomplished this. A quick and simple way for me was that I have a main form that cannot be closed. On that form I have a check box is visible only to my username. On the main form under the ontimer, it goes out and look for the table that this check box writes to. If the data is true, then it opens up a popup window with a warning that the db will shutdown in 5 minutes. After 5 minutes the ontimer function of the popup shuts down the program.

This has come in very handy and it works. I'm sure that I could have slid it into a module, but I do what I can.

 

[FancyPrairie]

In this thread thread700-1117479 I explain how I handle it plus how to distribute new versions of the FE to the various users

 

[markgrizzle]

I handled this with a user table that stored the names of users currently logged in. That way, I could easily identify users, and contact them to log off.

There's also some freeware floating around (I apologize if this is in fact shareware) name LDBView. Point it to an ldb file, and it identifies the users.

None of this actually answers your question, but hopefully you'll find it a useful compromise.

 

[FancyPrairie]

This is how I handle determining who is currently logged into my database. One of the things my databases does at startup is to check to see if the local computer is registered (searches table for its computer name). It it's not registered, then I popup a form and ask the user to enter the name and phone and office location of the primary user of the pc. Then I store this info in a table along with the name of the computer. Then I force every user to login to my database (Access Security). Now if want to know who's using my database, I can run the function below and match the names found to my table. Consequently, I know who's using my database, where they are located, and their phone number. (Obviously, this is not full proof since a user could log onto another machine that he/she is not registered with, but...).

This function will tell you who's logged on:


'+***************************************************************************************
'*
'* Sub: WhosLoggedOn
'*
'* Author: FancyPrairie
'*
'* Date: December, 2001
'*
'* Function: This routine will determine who is logged on the the database specified
'* by the caller (generally it should be the Workgroup database).
'*
'* This routine will return the following info in the Recordset passed by
'* the Caller:
'*
'* rst.Fields(0).Name = "Computer_Name" (Char: Name of the computer)
'* rst.Fields(1).Name = "LOGIN_NAME" (Char: Name of user whos logged in)
'* rst.Fields(2).Name = "CONNECTED" (Boolean: True if Connected)
'* rst.Fields(3).Name = "SUSPECT_STATE" (Integer: Null if not suspect)
'*
'* Arguments: strWorkgroup (string)
'* ---------------------
'* This string contains the path (and name) of the database you want to
'* see who's logged in. Usually you would check the workgroup file.
'* (Example: "\\server\ShareName\TheWorkgroup.mdw"
'*
'* rst (ADODB.Recordset)
'* ---------------------
'* This recordset will be returned to the caller. It will contain the
'* names of the computers that are logged on to "strWorkgroup" (see the
'* description of the recordset above).
'*
'* NOTE: This routine will create the recordset and populate it.
'*
'* varSortField (variant - Optional)
'* ---------------------------------
'* This variable indicates which field you want the recordset sorted by.
'* If this argument is not passed, the recordset will not be sorted. The
'* possible values for this variable are:
'* -1 = Don't sort the data
'* 0 = Sort by rst.Fields(0) (Computer_Name) (DEFAULT)
'* 1 = Sort by rst.Fields(1) (Login_Name)
'* 2 = Sort by rst.Fields(2) (Connected)
'* 3 = Sort by rst.Fields(3) (Suspect)
'*
'* varAscDesc (variant - Optional)
'* -------------------------------
'* Indicates how the data is to be sorted. The 2 possible values are:
'* "ASC" = Sort Ascending (DEFAULT)
'* "DESC" = Sort Descending
'*
'* Example: The following is an example of how to call this routine. The call shown
'* will return all of the computers logged on to "\\server\ShareName\TheWorkgroup.mdw"
'* and sorted by "Computer_Name" in Ascending order.
'*
'* Dim rst As ADODB.Recordset
'*
'* Call WhosLoggedOn("\\server\ShareName\TheWorkgroup.mdw", rst)
'*
'+***************************************************************************************

CODE
Option Compare Database
Option Explicit

Public Sub WhosLoggedOn(strWorkgroup As String, _
rst As ADODB.Recordset, _
Optional varSortField As Variant = 0, _
Optional varAscDesc As Variant = "Asc")

'********************************
'* Declaration Specifications *
'********************************

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

On Error GoTo ErrHandler

'*************************
'* Open Workgroup file *
'*************************

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "Data Source=" & strWorkgroup

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'*********************************
'* Create Fields for Recordset *
'*********************************

Set rst = New ADODB.Recordset

rst.Fields.Append rs.Fields(0).Name, adVarWChar, 32
rst.Fields.Append rs.Fields(1).Name, adVarWChar, 32
rst.Fields.Append rs.Fields(2).Name, adBoolean
rst.Fields.Append rs.Fields(3).Name, adInteger

'*************************************************************************
'* Loop thru Recordset and add Computer Name, etc. to user's recordset *
'*************************************************************************

rst.Open

While Not rs.EOF

rst.AddNew
If (Not IsNull(rs.Fields(0))) Then rst.Fields(0) = rs.Fields(0)
If (Not IsNull(rs.Fields(1))) Then rst.Fields(1) = rs.Fields(1)
If (Not IsNull(rs.Fields(2))) Then rst.Fields(2) = rs.Fields(2)
If (Not IsNull(rs.Fields(3))) Then rst.Fields(3) = rs.Fields(3)
rst.Update

rs.MoveNext
Wend

If (varSortField <> -1) Then
rst.Sort = rst.Fields(varSortField).Name & " " & varAscDesc
End If

'********************
'* Exit Procedure *
'********************

ExitProcedure:

Set rs = Nothing
Set cn = Nothing

Exit Sub

'****************************
'* Error Recovery Section *
'****************************

ErrHandler:

Err.Raise vbObjectError + 20100, "Error occcurred in function WhosLoggedOn", "Error Number: " & Err.number & vbCrLf & vbCrLf & "Error Description: " & Err.Description

Resume ExitProcedure

End Sub

 

[PaulCooper]

FancyPrairie: Both your answers are very helpful, thankyou. The first leads to threads within threads, with information to be mined along the way, again very helpful. I will try your code ~ thanks for posting it.

Markgrizzle: I will try an Internet search for your suggestion, just to see what it does. Thanks for the pointer.

Dryseals: I could also use a variation of your idea by forcing a closedown at (say) midnight when there should be no user activity. Thanks for the thought provoker.

Regards all, Paul

 

[DaOtH]

Paul,

would suggest doing this through some kind of version number.

Have a table in the front-end containing a version number, and have a table in the backend containing a version number.

Then:
On startup of your application compare the 2 version numbers.

frontend version number < backend version number:
close application with message they are using an old version

frontend version number = backend version number:
startup as normal

frontend version number > backend version number:
startup a kind of development version (with db screen available or stuff like that) For you to use when developing.


And, during usage of the application, have a timer compare the 2 version numbers every now and then. If the version number is changed to a higher number in the backend by yourselves, users will automatically have their front-end closed once their timer starts comparing the version numbers.






&quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963

 

[easyit]

...I would suggest you use an alternative to the versionnumbers. Create a batch file that does the checking for you. this way ALL your users will ALWAYS have the latest version, because the batchfile will copy it to there C drive.

See my answers in thread thread181-1093425 for examples and explanation.

 

[DaOtH]

EasyIt,

That's indeed what i meant (have a little .exe doing it actually, for every application, based on a .ini file).

However, you will still need to do checking. It's a waste of network resources to copy front-ends across whilst the user is already having the correct version.

Also, besides that the users might be able to start up the .mde file directly, bypassing your startup .bat file. So still in the .mde you will need to check the version and block older versions from having access.

Last but not least, based on a timer checking the versions makes your users logout as well. like the following.

1) you prepare a new version of your front-end, in it's local version table it has version number 2

2) Change the version number in the backend from 1 to 2
This makes all users logout. and blocks access to users having front-end version 1

3) User starts up the app, your startup file
(be it a .bat, .exe, .kix or whatever) compares the local version number and the version number of the new front-end (see 1) that resides on a network path to which the user has access but no mapping.
Version number is different, front-end is copied.

4) new front-end is opened, version number is compared to the backend, which is similar -> continue.

Block all access to the app ?
Raise the version number in the backend, but do not prepare a new version of the front-end.

Works like a charm...






&quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963

 

[PaulCooper]

DaOtH, EasyIt,

You both seem to have workable solutions.
I cant write .exe's so the BAT solution seems right, although I agree with not downloading a new front end every time.
Perhaps the .bat can call the front end and if it fails (mismatched version numbers) the front end "ends" and the bat downloads the current copy and recalls it. I'll have to work on that a bit, but it seems the way to go.

Thanks both for your time. Paul.

 

[easyit]

..the bat only copys if the front end is new; check the thread.

 

[DaOtH]

EasyIt,

Just out of interest, but how would you be checking if there is or isn't a new version ?
In my case the .exe uses OBDC to look into both .mde files and check the vesion number stored in a table.
Long time ago i used .bat files, but how would you handle that ?, how would you compare versions ?




&quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963

 

[FancyPrairie]

I prefer storing the version number (date/time stamp) in the database properties of the FE. That way it resides with the database.

 

[easyit]

I use a (empty) text-file, named "version1A.txt". in the batch file I check if this file is on the local (the users- c drive) if not, than either the version file is missing or has a different name hence version. In that case I copy both files (text file and mde) from the network to the c drive.
You can see the copy if exists statement sin the thread that I mentioned (it is the complete batch-file, you can use it with the proper changes to paths & names).
My users only get the new FE if I make two name changes; in the batch-file and the text-file (the names should correspond ofcourse). This is easy for testing, if I clear (or ask a user to) the folder on the c drive they automatically get a new file.

Storing this info in the Db forces the user to quit and copy the new FE - your choice ofcourse, but not elegant for your users (especially if you need to make many changes to the front end). In my case the just don't know it is happening, copying takes about 5 tot 10 seconds extra (the !@@#%! network is slow again!!)

If you really want to automate this, you could write code to write the batch file for you (and rename the text-file accordingly ofcourse).


EasyIT

 

[DaOtH]

Easy It,

The existence of a text file, file name of a text file, doesn't really sound waterproof. But if it works for you...

Storing this info in the Db forces the user to quit and copy the new FE - your choice ofcourse, but not elegant for your users

Actually storing it in the DB (whether it will be a table entry or a db property like FancyPrairie says) does not work as you suggest.

The startup .exe (where you are using a .bat file) reads the DBs with OBDC, and, depending on the result does or does not copy the FE. After that the FE is opened. Although in fact the user is indeed opening the front-end initially for the version check, this is done though OBDC in the background, invisible to the user.

For the user it only looks like the .exe starts, does a version check, potentially copies the front-end and the front-end is opened. Just a little dialog box on their screen that gives some messages on what is happening.


&quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963

 

[PaulCooper]

DaOtH is there a way you can share the .exe and associated .ini file? I guess this is doing what easyit's .bat file does, but including checking within the database (as you previous comments) rather than checking an associated txt file.
Although this started with lazy users, I also have to contend with a lazy programmer (me!) and it might be easier to remember to update the issue numbers that the bat files and text file.

 

[DaOtH]

Sure can,

give me some time so i jot down some 'documentation' as well and publish it somewhere.

Little busy the next few days but i will come back to you.

&quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963

 

[easyit]

Well,

I am lazy and it works for me...but:

Wat tools do you use to create the exe? Do you have VB or something else? I only can use VBA, and ofcourse if there is a way to get rid of the bat file I would.


EasyIT

 

[PHV]

I only can use VBA, and ofcourse if there is a way to get rid of the bat file I would
You may consider VBScript with OLE Automation and FileSystemObject.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886