Question Allow only 1 user to use DB or Make database read only if someone has it opened (1 Viewer)

hardhitter06

Registered User.
Local time
Today, 07:14
Joined
Dec 21, 2006
Messages
600
Hi All,

Access 2003.

I was wondering if there is a way to have the database set up so that only one person can use it at a time

OR

If someone has it opened, to make the database read only?

The reason behind this is because my database keeps track of purchase orders and users are only allowed to spend up to 50,000 a year so that if two people were in the database at the same time, under the same acct, if one puts it at 50,000, and the other does the same, the account will be over 50,000 and the users might be unsure why.
 

Alansidman

AWF VIP
Local time
Today, 06:14
Joined
Jul 31, 2008
Messages
1,493
In your dB, click on Tools | Options | Advanced | Exclusive. This will allow only one person to have the dB open at a time.

Alan
 

hardhitter06

Registered User.
Local time
Today, 07:14
Joined
Dec 21, 2006
Messages
600
That doesn't work. At work, we have a network drive and I changed that option and I still can open it from multiple PCs...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:14
Joined
Feb 19, 2002
Messages
42,970
Change the network permissions for the directory that holds the database to take away the permission to create new files. This will prevent Access from creating the .ldb (locking file) and so it will open the database read only if any other user has the db open already.
 

DCrake

Remembered
Local time
Today, 11:14
Joined
Jun 8, 2005
Messages
8,632
Pat, I see your logic but how does the first person to open the database manage it if Access can't create the ldb file in the first instance due to network permissions?
 

hardhitter06

Registered User.
Local time
Today, 07:14
Joined
Dec 21, 2006
Messages
600
Will that work because I don't have "access" to change those permissions, but I could probably get it. But I don't know if it's pointless to go through that trouble...
 
Last edited:

hardhitter06

Registered User.
Local time
Today, 07:14
Joined
Dec 21, 2006
Messages
600
Will this involve "Take Ownership" in the security tab for my users?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:14
Joined
Feb 19, 2002
Messages
42,970
If Access cannot create the .ldb file in the directory with the database, it creates it locally. I can't remember where exactly.
 

Dennisk

AWF VIP
Local time
Today, 11:14
Joined
Jul 22, 2004
Messages
1,649
I've just developed code to do this. I had a single user booking system, and before I upgraded it I was able to allow one person in the db at a time.

you may find these procedures/functions useful

Code:
Option Compare Database
Option Explicit

' Routine to Obtain a users permission Group
' Count ReadWrite Groups

Private Const JET_SCHEMA_USERROSTER = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
Private Const UPDATE_PERMISSION_GROUP = "ReadWrite"
Private Const DEVELOPER_USER_NAME = "Backdoor"

Sub test()
    If CurrentUser() <> DEVELOPER_USER_NAME Then
        Debug.Print "Is allowed in "; isUserAllowedIn(CurrentUser())
    End If
End Sub
Public Function isUserAllowedIn(strUser As String) As Boolean
    Dim strUserGroup As Variant
    Dim intUpdateCount As Integer

    strUserGroup = GetUsergroup(CurrentUser())
    intUpdateCount = GetNoUpdateLogins()
    
    Debug.Print "Currently Logged In "; CurrentUser()
    Debug.Print "Is a member of the Update group "; strUserGroup
    Debug.Print "Count of Update Users "; intUpdateCount
    If intUpdateCount > 1 Then
        isUserAllowedIn = False
    Else
        isUserAllowedIn = True
    End If
    
End Function

Private Function GetUsergroup(strLoggingOn As String) As String
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i As Integer
    Dim strUser As String
    Dim fFound As Boolean
On Error GoTo Err_handler

Set cn = CurrentProject.Connection
    
    ' The user roster is exposed as a provider-specific schema rowset
    ' in the Jet 4.0 OLE DB provider. You have to use a GUID to
    ' reference the schema, as provider-specific schemas are not
    ' listed in ADO's type library for schema rowsets
    
    Set rs = cn.OpenSchema(adSchemaProviderSpecific, , JET_SCHEMA_USERROSTER)
    
    fFound = False
    While Not rs.EOF And Not fFound
        ' rs.fields(1) is fixed length, the text is terminated by asc 0 and padded out with asc 32
        i = InStr(1, rs.Fields(1), Chr(0))
        strUser = Left$(rs.Fields(1), i - 1)
        
        If strLoggingOn = strUser Then
            GetUsergroup = GetUpdatePermissionGroup(strUser)
            fFound = True
        End If
    rs.MoveNext
    Wend
    
Exit_Handler:
    Set rs = Nothing
    Exit Function
    
Err_handler:
    MsgBox Err & " " & Err.Description
    Resume Exit_Handler
End Function

Private Function GetUpdatePermissionGroup(strUser As String) As Boolean
' Returns true if the User currently logged in
' is in the Update group
    Dim ws As Workspace, usr As User, grp As Group
On Error GoTo Err_handler
    Set ws = DBEngine.Workspaces(0)
     GetUpdatePermissionGroup = False
     
    For Each usr In ws.Users
    'Debug.Print "Users/strUser "; usr.Name, strUser
        If usr.Name = strUser Then
            For Each grp In usr.Groups
                'Debug.Print "groups "; grp.Name
                If grp.Name = UPDATE_PERMISSION_GROUP Then
                        GetUpdatePermissionGroup = True
                End If
            Next
       End If
    Next
    
Exit_Handler:
  
    Exit Function
    
Err_handler:
    MsgBox Err & " " & Err.Description
    Resume Exit_Handler
End Function

Private Function GetNoUpdateLogins() As Integer
' Loops through all logged on users and
' and counts up those in the Update group
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i As Integer
    Dim strUser As String
On Error GoTo Err_handler

Set cn = CurrentProject.Connection
    
    ' The user roster is exposed as a provider-specific schema rowset
    ' in the Jet 4.0 OLE DB provider. You have to use a GUID to
    ' reference the schema, as provider-specific schemas are not
    ' listed in ADO's type library for schema rowsets
    
    Set rs = cn.OpenSchema(adSchemaProviderSpecific, , JET_SCHEMA_USERROSTER)
    
    While Not rs.EOF
        ' rs.fields(1) is fixed length, the text is terminated by asc 0 and padded out with asc 32
        i = InStr(1, rs.Fields(1), Chr(0))
        strUser = Left$(rs.Fields(1), i - 1)
        If isUpdateGroup(strUser) Then
            GetNoUpdateLogins = GetNoUpdateLogins + 1
        End If
    rs.MoveNext
    Wend
    
Exit_Handler:
    Set rs = Nothing
    Exit Function
    
Err_handler:
    MsgBox Err & " " & Err.Description
    Resume Exit_Handler
End Function

Private Function isUpdateGroup(strUser As String) As Boolean
    ' checks if a user is in the Update group
    ' return True if so
    Dim ws As Workspace, usr As User, grp As Group
On Error GoTo Err_handler

    Set ws = DBEngine.Workspaces(0)
    
    For Each usr In ws.Users
    If usr.Name = strUser Then
        For Each grp In usr.Groups
            If grp.Name = UPDATE_PERMISSION_GROUP Then
                isUpdateGroup = True
           End If
        Next
    End If
    Next
    
Exit_Handler:
    Exit Function
    
Err_handler:
    MsgBox Err & " " & Err.Description
    Resume Exit_Handler
End Function
 

hardhitter06

Registered User.
Local time
Today, 07:14
Joined
Dec 21, 2006
Messages
600
where does this go, and can I copy and paste or do I need to change things around? Don't know that much about coding..

I don't need to set up groups or anything either. Just need this rule to apply to any user.
 
Last edited:

hardhitter06

Registered User.
Local time
Today, 07:14
Joined
Dec 21, 2006
Messages
600
The problem I discussed earlier for my users occured. On the continious form, I added another record, well I started to add it filling out half the fields, then opened it on another PC with the shared drive, completed a record, went back to the other one, wasn't showing the new one, completed the first one I started, and both entries went in fine. This is definately a problem!

I've tried using the Exclusive setting, I've tried using "Locked Records" and I tried taking off "Open Database using Record Locking". None of these worked...what if I put some permissions on the folder the database is in?
 

hardhitter06

Registered User.
Local time
Today, 07:14
Joined
Dec 21, 2006
Messages
600
I figured it out, what I did was I made the Microsoft Office Access Record-Locking Information a read-only so now when someone opens the database, it says file is already in use. SWEEEET
 

hannahle

New member
Local time
Today, 04:14
Joined
Apr 26, 2012
Messages
2
@hardhitter06: can you explain more how you set up to allow only 1 user to use DB or Make database read only if someone has it opened? I am having that trouble to do that too. thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:14
Joined
Feb 19, 2002
Messages
42,970
Hopefully hardhitter06 will answer but I can tell you that this will not work with A2007 or A2010 because they use ACE which is the replacement for Jet and this code is for the Jet engine.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:14
Joined
Sep 12, 2006
Messages
15,613
i know this is an old thread

the reason it is difficult to make a db single user is because access is designed to be multi user.

maybe do it in software. add code that records the current user in a table, and clears it on exit

interrogate that table when the next user tries to startup, and quit if the dbs is already in use.

you need some override facility, if the "active user" flag is left in place somehow.
 

Users who are viewing this thread

Top Bottom