On database close event

[Taha60]

Hello...

I have s question which I would be extremly grateful to whosoever helps out in anyway.

FIRST THE SCENARIO

I have a database that has both front ends and a back end. In the front end, the user has the ability to use and Administrative Menu that is password protected in order that the user may change/add/append the data in the back end. (Note the back end is a set of linked tables in the front ends)

AND NOW THE QUESTIONS.

I would like to make the Admin menu Exclusive to a single front end. That is only one user can get click the admin button and if another user clicks the admin button he is informed that Mr. X is using it currently.

My solutions were as follows:
1. everytime someone enters the admin menu a table in the back end is appeneded to and the last and only record in that table is rewritten to reflect the new Admin user and a boolean saying he is in. Now when he returns to the main menu the boolean is set to false. This works perfectly until someone pulls the power plug on the computer that is using the admin menu or he exits the database direcly using the X button on the main access window. Then i have to manually reset the boolean.

2. The table is rewritten every 5 seconds with a datestamp. And if someone needs to use the admin menu it compares "NOW" to the datestamp. if there is a 6 second gap you can get it if not ... it will tell you who is curretly using it. HOWEVER, the computer with the admin menu running uses 178KBytes/sec UP AND DOWN to accomplish this. And i still dont get real time DATA on the BACK END. ie. sometimes I get when other front ends check for the date stamp they just HANG for about 15 secs before they return the error.


Thanks ....


Taha
thamiral@uwo.ca

 

[scking]

In the back end simply create the functions needed to implement the needed functionality. You would need to set it up as a library for this to work properly. You would need to evaluate the pros/cons of this technique.

This may involve more work than you want to put into it but I've never liked the idea of the table being updated to show state for the very reasons you stated. This, with a heartbeat capability, will close the session if it doesn't receive a heartbeat within the stated time.

1) Name it with a .mda extension
2) Register it as an Add-In in the registry
3) Create a runtime reference

Type Session
CurrentUser As String
StartTime As Date
LastHeartbeat As Date
OnTime As String
End Type
Dim mSession As Session
Const CONST_SESSION_LOGOUT As Integer = 60

Public Function Heartbeat(CurrentUser, Optional CancelReservation As Boolean = False)
If Not CancelReservation Then
mSession.LastHeartbeat = Now()
Else
mSession.CurrentUser = ""
mSession.StartTime = ""
mSession.LastHeartbeat = ""
End If
End Function

' You could enforce many rules using the SessionMonitor
Private Sub SessionMonitor()
Do While DateDiff(mSession.StartTime, mSession.LastHeartbeat, &quot;s&quot <= CONST_SESSION_LOGOUT
DoEvents
Loop
mSession.CurrentUser = &quot;&quot;
mSession.StartTime = &quot;&quot;
mSession.LastHeartbeat = &quot;&quot;
End Sub

Public Function ReserveSession(CurUser As String) As Boolean
If mSession.CurrentUser = &quot;&quot; Then
ReserveSession = True
mSession.CurrentUser = CurUser
mSession.StartTime = Now()
mSession.LastHeartbeat = Now()
Else
ReserveSession = False
End If
End Function
-----
Client system
Dim mIsReserved As Boolean

frmYours_OnOpen
If Application.Run(&quot;YourServerDatabaseName.ReserveSession&quot;, CurrentUser) Then
' You have reserved the session
SetReserved(True)
Else
MsgBox &quot;The session is already reserved.&quot;
Cancel = True
End If

Sub SetReserved(IsReserved As Boolean)
mIsReserved = True
Me.Timer.Interval = 1000
End Sub

Sub Timer()

Application.Run(&quot;YourServerDatabaseName.Heartbeat&quot;, CurrentUser)
End Sub

frmYours_OnClose
Application.Run(&quot;YourServerDatabaseName.Heartbeat&quot;, CurrentUser, True)
End Sub


----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[Taha60]

Thanks for the solution... But I tried and I got a bit of a better result... I set my admin menu to lock (allrecords) onto a dummy table on the back end! Therefore no other front end can use the admin menu since it will try to lock onto a table that is already locked... I think of my self as being stupid since i did not come up with the solution earlier...

Thanks anhoo,,,
Taha
thamiral@uwo.ca

 

[scking]

Lots of questions, lots of answers. But you can't be hard on yourself. There is a big difference between being ignorant of a solution and being stupid. If I were that hard on myself I would probably have to quit this job. You came up with a solution that I never would have though of. Have you tested it and will it have any adverse effects on other users? I guess that is always the bottom line.
----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[Taha60]

Hello...

Yes I fully tested it and found no adverse affects on other users.... I dont know why there would be adverse affects on anyother users since i am locking onto a Dummy table which has nothing in it...

thanks Taha
thamiral@uwo.ca