Read Only When someone is already in DB

[hardhitter06]

Good Morning All,

Using Access 2003.

At work, I have 3 seperate little databases that my boss would like me to "join" if you will. This will make it easier on the users because they won't have multiple shortcuts on their desktop for each database. Bascially, I plan on creating a Menu Form...where you have 3 choices. Each choice is one of the databases. I plan on exporting all of the tables/queries/forms, into one central Access database.

The problem is, one of the databases must turn Read Only when someone else is in. I'm not really sure how to do this. I've tried messing with the Advanced Options (Default open mode and Default Record Locking) but it wouldn't help. What I then did to basically accomplish my goal was to change the additional file that opens when the database is opened (the one with the little lock) to read-only so that if someone had the database opened, if a second person tried to open it, it would say "This Database is Open" (or whatever that message is) and wouldn't let the person in at all until the other closed out.

So that was fine, but now if I plan on combining the three, I'm not going to be able to do this because 2/3 databases, people can be in at the same time and I don't want to take that away...does anyone know what I can do? Is there a way to make this database Read Only by putting a lock on its one and only table? In other words, don't allow Data Entry to the table if this database/table is open? Any ideas would be great.

Thank you for your time.

 

[Kafrin]

Bit of a guess this, but how about if you leave the table that has this requirement in a different database and then link the table into your new, combined database. You can use your current locking method on the one file but users will get to it from your main database.

I think the locking file would only be created when the data in the table is actually being accessed, so this shouldn't lock everyone out of the main database, it would just stop multiple people viewing/editing the extra table simultaneously.

I think there's probably a better way to do this, but I don't know what it is!

 

[gemma-the-husky]

out of interest, why MUST one of the databases become readonly when someone else is using it?

what possible harm would come from not making it read only?

 

[Dennisk]

use workgroup security, then you can assign read/write permissions to groups of users or individual users.

 

[hardhitter06]

I'm not worried about anyone harming anything, I just can't have new records added if someone else is in because it has to do with a 50,000 K Budget per account. So if the Account has 20 K left and someone is currently charing 15 K, and another user was to enter with a bill of 10 K...they both could enter it because the record wouldn't update until that Account number is searched again..by then it would be too late.

Dennisk,

It's not that I want certain people to have permission, I just can't allow Records to be inputted at the same time for the above reason...

 

[MSAccessRookie]

out of interest, why MUST one of the databases become readonly when someone else is using it?

what possible harm would come from not making it read only?

Based on the context clues listed below, I am guessing that the users are using two Split Databases and one Non-Split Database that exist on a shared server somewhere. The Op does not want two users opening the database that is not split for the obvious reasons.

• 3 separate databases
• multiple shortcuts
• 2/3 databases, people can be in at the same time

If this is true, then splitting the third database could remove most of his need for resolving this issue.

Note: IF the user database Front Ends are shared, then the Op should also consider having a private copy for each user.

 

[hardhitter06]

MSAccess said it in different words, but he is right...how do I get around this

 

[MSAccessRookie]

MSAccess said it in different words, but he is right...how do I get around this

I also provided a possible resolution for you to try (as long as approval is granted):

• If this is true, then splitting the third database could remove most of his need for resolving this issue.
• If the user database Front Ends are shared, then the Op should also consider having a private copy for each user.

Give these a try and see what happens.

 

[hardhitter06]

This database will be fed from all users so I wouldn't be able to give each user a private copy? Maybe I misunderstood you?

 

[hardhitter06]

Does anyone know if there is a way Under the Security-User And Group Permissions, or in that area, to lock up a table when a second user is Accessing it...better yet, only lock it up for the 2nd person accessing it and anyone else after until the original is out?

I see "Read", "Update", "Insert" data but I don't think these permissions will do what I want?

 

[MSAccessRookie]

This database will be fed from all users so I wouldn't be able to give each user a private copy? Maybe I misunderstood you?

In a Split Database Scenario, each user gets their own copy of the Front End Application, and they share the Back End which contains the data. I believe that design will meet your requirements as stated.

 

[hardhitter06]

How does one set up a Front End and Back End...this is new to me?

 

[hardhitter06]

And basically, 2 people won't be able to add to the same table if one of them has it opened before the other? Because that is what I'm lookin for..

 

[gemma-the-husky]

hardhitter

i still dont understand this issue about your concern about dual updates. You are worrying about this unduly and unnecessarily.

if you have an account with a balance of 20K, and two users try to process orders of say another 15K and another 25K

you will not get a situation where both users users appear to be adding to a balance of 20k. access will recheck the balance before writing and will warn you what is happening, and let you recover. (to be fair, this may depend on how you are handling the transaction total)

actually the likelihood of 2 users updating simultaneously is quite remote - but even if it happens, access will manage it for you.

you can have psecific record locking - but this is isnt necessary because the default of no locking (actually OPTIMISTIC locking) will still preserve the integrity of your data

try it - have two sessions active, and both try to update, and see what happens.

 

[hardhitter06]

I have tried before....for example. I have 2 PCs access the same account number by a search. Both PCs display 20 K left.

I have the first one enter in 21 K...receives and error, I have that person change it to 19 K leaving 1 K to spend.

On PC2, I'm allowed to enter in 10 K or whatever. IT goes in fine.

I then research this account number and the balance is -9K.

I would think it would stop me too but it doesn't. This is probably because I just use the sum of the Amounts from the form in an extra textbox.

"=Sum([CommAmt])"

Should I be doing this a different way?

 

[hardhitter06]

Also, when I am adding another amount to an account...I search for it and the records are displayed on an continious form line by line and I'm totaling the amounts.

The calculation is strictly drawn from this form display so that is probably why I am able to enter on the 2nd PC an amount over the budget because the record from the first PC is not showing on this continious form...unless I searched (queried) this account number again...does that make more sense?

 

[gemma-the-husky]

ok i see

i'm not sure how you are storing the outstanding balance. it sounds like it is a calcualte figure, in which case access wouldn't automatically intercept the probelm

one way to do this then, to protect the exposure would be to retest the exposure in the forms before update event, before saving the latest change.

so although you started with 20k available, when you retest it you find that because another user has used 19k of this balance, there is now only 1k available, and you can rejext the change.

this is far easier to do than to make the whole dbs read only. In most cases, you wont get a clash - user B may be processing a different account to user A.

 

[Dennisk]

I have code to check when the first user logs in.

here are a number of functions to count the number of users logged in and to check which group they belong to

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]

Gemma,

I'm going to attach my database. You think you could take a look at it and help me fix my problem with your solution since I'm unsure exactly what I need to do to fix this (plus I'm not a great coder so you could help me get going in the right direction)?

If you can help, to get to the calculation:

Go to the "SplitOrdering Database" and type "12312312" for the search. I stripped it down so that this is the only record and there is 25 K left to spend.

I appreciate all of your help.

PS Thank you Dennisk for your help with your code.

 

[hardhitter06]

I'm looking at my code for the beforeupdate for the actual field...

I have this:

Private Sub CommAmt_BeforeUpdate(Cancel As Integer)
If CommAmt.Value + Text13.Value > (50000) Then
MsgBox ("Commodity Amount excedes limit!")
Cancel = True
End If
End Sub

That is checking the forms Amount values, but it's almost like I need to refer to the table directly? If that is the case though, that is a problem because I am displaying records withing one year of today's date so a UNSPSC code might have 15 records, but only 2 might be active within the year's span..and this is how this process works (you get 50 k within a years date from today). To further explain that, say I have a record that is one year from today for 10 K...tomorrow though, that record will not count towards the budget because it doesn't fall within a years date from tomorrows date...therefore this account is free'd up 10 K...hope that is a good enough explination