Gaining exclusive control of the Database...

[Guest]

We have a DB that is used by around 20 users.
My problem arises when I need to make a change, and I can't seem to find who
is still in the DB. Is there any way for me to find out who it is, or to
kick them out?

Thanks,
Ricky Painter

 

[Keith Wilby]

We have a DB that is used by around 20 users.
My problem arises when I need to make a change, and I can't seem to find
who
is still in the DB. Is there any way for me to find out who it is, or to
kick them out?

Yes but it requires some coding and a new table with fields "Value" and
"Type". Here is the code I use in the main form's timer event which you
may be able to adapt. Post back if you need any explanation.

Keith.
www.keithwilby.com

Private Sub Form_Timer()

On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dtmTimeOut As Date
Dim varType As Variant
Dim varValue As Variant

Set db = CurrentDb()
Set rs = db.OpenRecordset("qtblShutdown")
Do While Not rs.EOF
varValue = rs!Value
If Not IsNull(varValue) Then
Select Case rs!Type
Case "User"
If varValue = fOSUserName() Then
DoCmd.OpenForm "fdlgShutdown", , , , , acDialog
mfTimedOut = True
DoCmd.Close acForm, Me.Name
End If
Case "Computer":
If varValue = fOSMachineName Then
DoCmd.OpenForm "fdlgShutdown", , , , , acDialog
mfTimedOut = True
DoCmd.Close acForm, Me.Name
End If
Case "Time"
dtmTimeOut = varValue
If mdtmTime < dtmTimeOut And Time > dtmTimeOut Then
DoCmd.OpenForm "fdlgShutdown", , , , , acDialog
mfTimedOut = True
DoCmd.Close acForm, Me.Name
End If
End Select
End If
rs.MoveNext
Loop

End Sub

 

[Guest]

Thanks for your help. First off, I'm not familiar with Basic. I'm a COBOL
guy who knows enough SQL to be dangerous...
Just looking at the code, I'm guessing that the new table is called
"Shutdown" and the values for this table are going to be set by the timer
event.
How do I set up a timer event?
Thanks again for your help,
Ricky Painter

 

[Keith Wilby]

Thanks for your help. First off, I'm not familiar with Basic. I'm a
COBOL
guy who knows enough SQL to be dangerous...
Just looking at the code, I'm guessing that the new table is called
"Shutdown" and the values for this table are going to be set by the timer
event.

Yes, that's right. fOSUserName and fOSMachineName are separate functions
lifted from here:

http://www.databasedev.co.uk/get_username_or_computername.html

How do I set up a timer event?

The events are built-in code modules which you access via the form's
properties pallete in design view. Select "Event Procedure" from the
drop-down list in the On Timer event, then click just to the right of the
drop-down and it will open the module. That's where you insert the code
(it's VBA code BTW).

You then set the timer interval, also on the proerties pallete, in
milliseconds. I have mine set to 30000 (30 seconds).

Thanks again for your help,

You're welcome. Post back if you need more help.

Keith.

 

[Guest]

OK, I got the code loaded, but during the debug, I got an error:
"Sub or function not defined"
on fOSUserName. Where and how do I need to define this?
I'm sure that I will need to define the fOSMachineName also.

Thanks again

 

[Keith Wilby]

OK, I got the code loaded, but during the debug, I got an error:
"Sub or function not defined"
on fOSUserName. Where and how do I need to define this?
I'm sure that I will need to define the fOSMachineName also.

Did you miss this?

 

[Guest]

I did. Thanks for all or your help. I have all of the pieces that I need,
now all I have to do is put them together.

Thanks again. I appreciate your patience.
Ricky Painter

 

[Keith Wilby]

Thanks for all or your help.

Pleasure.

Keith.