Solved

How do I kick people out of an Access database?

Posted on 2001-06-21
11
1,264 Views
Last Modified: 2012-05-04
Is there a utility for this in Access 97?
0
Comment
Question by:stevensc
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
No.  Whatever you do, you need to do it on your own within the app.

Bsically it boils down to setting a flag in some way (record in a table, line in a ini file, or a registry entry) and then having code that checks for that flag on a regular basis.  A hidden form with a timer event set for every 60 seconds is a good way to do this.

Jim.
0
 
LVL 14

Expert Comment

by:mgrattan
Comment Utility
The LDBViewer can be used to identify who is in the database.  It is part of the Jet Utilities download:

http://support.microsoft.com/support/kb/articles/Q176/6/70.asp?FinishURL=%2Fdownloads%2Frelease%2Easp%3FReleaseID%3D12736%26redirect%3Dno
0
 
LVL 14

Expert Comment

by:mgrattan
Comment Utility
In Access 2000 you can use the Jet UserRoster:

http://support.microsoft.com/support/kb/articles/q198/7/55.asp

No matter which method you use to identify who's logged in, there's no method (as far as I know) that lets you boot them out other than disconnecting them from the network.
0
 
LVL 11

Expert Comment

by:joekendall
Comment Utility
Do you have a server that people log into each day such as a Novell server? There is a way through code to kick them out. When the database opens, you can check through code to see if a person is authorized.

The following function will read the registry to see who the individual is that logged into the Novell server. It can be changed for other servers. You would just need to compare the function to a list of logins in a table to see if that person has access. You don't need all of the constants.

I hope this will help. Thanks!

Private Declare Function RegOpenKeyEx _
 Lib "advapi32.dll" Alias "RegOpenKeyExA" _
 (ByVal hKey As Long, ByVal lpSubKey As String, _
 ByVal ulOptions As Long, ByVal samDesired As Long, _
 phkResult As Long) As Long

Private Declare Function RegCloseKey _
 Lib "advapi32.dll" _
 (ByVal hKey As Long) As Long
 
Private Declare Function RegQueryValueEx _
 Lib "advapi32.dll" Alias "RegQueryValueExA" _
 (ByVal hKey As Long, ByVal lpValueName As String, _
 ByVal dwReserved As Long, lpType As Long, _
 lpData As Any, lpcbData As Long) As Long

' Registry constants
Global Const SUCCESS = 0
Global Const REGMAXDATASIZE = 2048
Global Const REGNONE = 0
Global Const REGSZ = 1
Global Const REGEXPANDSZ = 2
Global Const REGBINARY = 3
Global Const REGDWORD = 4
Global Const REGDWORDLITTLEENDIAN = 4
Global Const REGDWORDBIGENDIAN = 5
Global Const REGLINK = 6
Global Const REGMULTISZ = 7
Global Const REGRESOURCELIST = 8
Global Const REGFULLSOURCEDESCRIPTOR = 9
Global Const REGRESOURCEREQUIREMENTSLIST = 10
Global Const REGOPTIONRESERVED = 0
Global Const REGOPTIONNONVOLATILE = 0
Global Const REGOPTIONVOLATILE = 1
Global Const REGOPTIONCREATELINK = 2
Global Const REGOPTIONBACKUPRESTORE = 4
Global Const READCONTROL = &H20000
Global Const KEYQUERYVALUE = &H1
Global Const KEYSETVALUE = &H2
Global Const KEYCREATESUBKEY = &H4
Global Const KEYENUMERATESUBKEYS = &H8
Global Const KEYNOTIFY = &H10
Global Const KEYCREATELINK = &H20
Global Const KEYREAD = KEYQUERYVALUE + KEYENUMERATESUBKEYS + _
 KEYNOTIFY + READCONTROL
Global Const KEYWRITE = KEYSETVALUE + KEYCREATESUBKEY + READCONTROL
Global Const KEYEXECUTE = KEYREAD
Global Const KEYALLACCESS = KEYQUERYVALUE + KEYSETVALUE + _
 KEYCREATESUBKEY + KEYENUMERATESUBKEYS + _
 KEYNOTIFY + KEYCREATELINK + READCONTROL
Global Const HKEYCLASSESROOT = &H80000000
Global Const HKEYCURRENTUSER = &H80000001
Global Const HKEYLOCALMACHINE = &H80000002
Global Const HKEYUSERS = &H80000003
Global Const HKEYPERFORMANCEDATA = &H80000004

Function ReadCurrentUser() As String
    Dim hKeyLogon As Long
    Dim lngResult As Long
    Dim strBuffer As String
    Dim lngBuffer As Long
   
    ' Open the KHEY_LOCAL_MACHINE\Network\Logon key
    lngResult = RegOpenKeyEx(HKEYLOCALMACHINE, _
     "Network\Logon", 0&, KEYALLACCESS, hKeyLogon)
   
    ' Make sure the call succeeded
    If lngResult = SUCCESS Then
       
        ' Create the buffer
        strBuffer = Space(255)
        lngBuffer = Len(strBuffer)
       
        ' Read the User Name
        lngResult = RegQueryValueEx(hKeyLogon, "Username", _
         0&, REGSZ, ByVal strBuffer, lngBuffer)
       
        ' Check return value
        If lngResult = SUCCESS Then
           
            ' Display the current value
            ReadCurrentUser = Left(strBuffer, lngBuffer - 1)
                                           
        End If
       
        ' Close the Logon key
        lngResult = RegCloseKey(hKeyLogon)
    End If
End Function

0
 
LVL 1

Expert Comment

by:dhinkle
Comment Utility
I have to kick users out too. I do maintenance after 5:00 and sometimes they go home without shutting down. I have to get the sys admins to do it for me though. This is what they do. Open Server Manager, hilight the server you want to kick them off of and open Properties. Click the In Use button and then scroll down the list to find the offenders. Hilight the one you want to kick out and click Close Resource.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Cutting off their network connection is asking for trouble.  It's possible that the database will become corrupt.

The shutdown needs to occur gracefully from within the application.

Jim.
0
 
LVL 1

Expert Comment

by:dhinkle
Comment Utility
JDettman - corruption is possible but we've been doing it for over 2 years now and never had a problem. I would expect that the corruption would happen if someone were actually in edit mode when you kicked them out.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
dhinkle,

Exactly. If they have pending writes and you close the connection, at a minimum the repair flag will get set.

I'd have to say, you've been lucky so far<g>. Got more guts then I do!

Jim.
0
 
LVL 1

Accepted Solution

by:
dhinkle earned 200 total points
Comment Utility
Jim,

Actually what I've got is users who won't listen and BACKUPS! And a guardian angel I guess too then :)
0
 

Author Comment

by:stevensc
Comment Utility
OK I got this code to work:

"Private Declare Function RegOpenKeyEx _
Lib "advapi32.dll" Alias "RegOpenKeyExA" _
(ByVal hKey As Long, ByVal lpSubKey As String, _
ByVal ulOptions As Long, ByVal samDesired As Long, _
phkResult As Long) As Long

Private Declare Function RegCloseKey _
Lib "advapi32.dll" _
(ByVal hKey As Long) As Long

Private Declare Function RegQueryValueEx _
Lib "advapi32.dll" Alias "RegQueryValueExA" _
(ByVal hKey As Long, ByVal lpValueName As String, _
ByVal dwReserved As Long, lpType As Long, _
lpData As Any, lpcbData As Long) As Long

' Registry constants
Global Const SUCCESS = 0
Global Const REGMAXDATASIZE = 2048
Global Const REGNONE = 0
Global Const REGSZ = 1
Global Const REGEXPANDSZ = 2
Global Const REGBINARY = 3
Global Const REGDWORD = 4
Global Const REGDWORDLITTLEENDIAN = 4
Global Const REGDWORDBIGENDIAN = 5
Global Const REGLINK = 6
Global Const REGMULTISZ = 7
Global Const REGRESOURCELIST = 8
Global Const REGFULLSOURCEDESCRIPTOR = 9

etc.........." (see above)

But the problem is how can I make it execute on the remote pc.????
0
 
LVL 11

Expert Comment

by:joekendall
Comment Utility
stevensc:

First, I have been on vacation and have not had a chance to respond.

I'm not sure if you have a client/server application, but you can have the code in the client side and close the client application.

If you have the code in the server side, you could make an Access library out of the code and include it in your client side.

Unless someone else knows of another way, you would almost definitely have to have the code on the client side. Thanks!

Joe
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now