Solved

How do I kick people out of an Access database?

Posted on 2001-06-21
11
1,278 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
ID: 6215662
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
ID: 6215685
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
ID: 6215694
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 11

Expert Comment

by:joekendall
ID: 6215827
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
ID: 6218394
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
 
LVL 57
ID: 6218627
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
ID: 6218642
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
ID: 6218710
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
ID: 6218736
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
ID: 6219883
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
ID: 6306216
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

809 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