multiuser issue

[iori]

I have split my db and distributed the FE to client pcs while BE is on the network. To demonstrate it i have 2 users , userA who has read/write rights on the shared folder where BE is while userB only has read rights on the shared BE folder. When userA opens the FE he can get in(writing userinfo in the table on the OnLoad event) but when userB gets in the FE opens fine EXCEPT he gets the error that there was an error on the OnLoad event, which i am fine with because userB doesnt have right access to the db yet. But now if the FE is closed by both users and this time when userB logs(read rights only) in before userA(read/write rights) he gets in with no error on the onLoad event but now if userA logs in he is shown the same error on the onLoad event!!!
i am totally lost as to what i have missed, can someome help? this is my code on the onLoad event in the FE:

Private Sub Form_Load()
On Error GoTo formload_errhandler
    Me.InsideHeight = 5000
    Me.InsideWidth = 10000
    DoCmd.Maximize
    Me.picBox.Height = Me.InsideHeight
    'authenticate the logged user
    Dim userName As String
    userName = Environ("Username")
    
    'check to see if username is in the SECURITY table
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    
    With objRS
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .Open "SELECT * FROM USER_SECURITY WHERE userID='" & userName & "'", CurrentProject.Connection
        .ActiveConnection = Nothing
    End With
    
    If objRS.EOF Then
        MsgBox (userName & ", access denied")
        userAuthenticated = False
        DoCmd.Quit
    Else
        userAuthenticated = True
    End If
    
    'at this point userAuthenticated is true
    'log the user in USER_LOG table updating userID,dateEntered fields
    Dim objCommand As ADODB.Command
    Set objCommand = New ADODB.Command
    Dim strSQL As String
    
    strSQL = "INSERT INTO USER_LOG (userID,dateEntered) VALUES" & _
             "('" & Environ("Username") & "',#" & Now() & "#)"
             
    'save userEntered so userExit can be updated to the right record
    'this will be used to query the right record when the user exits
    dateEntered = Now()
        
    Set objCommand.ActiveConnection = CurrentProject.Connection
    objCommand.CommandText = strSQL
    objCommand.Execute
    
    Set objCommand = Nothing
    
formload_errhandler:
  MsgBox ("Application has encountered an expected error")
  Resume Next
  
End Sub

 

[Brian1960]

Multi Users

Hi,

Each user needs read and write on the database BE folder. This is because Access creates a lock file and to do the the user needs access to write it.
If the first user (read only) opens the system then this file does not get created and by default the system will open as user Admin and this opens the Db as Exclusive.

You are better to user a workgroup file with two groups a)Users - with full read write access b)Reader - with read only access. Locate this workgroup in the same directory as the BE and give full control to it.

You can then join users to the appropriate groups.

B

 

[The_Doc_Man]

If I read this correctly, you cannot do security this way.

All users of the MDB must have MODIFY access to the folder holding the MDB. Giving read-only access to a user locks the file because the Windows FILE SYSTEM (not ACCESS) takes out a lock. Even users who cannot write to the database MUST be able to write to the LDB file to show that they have a particular record open. Otherwise, a writer can come along behind the reader and invalidate the stuff being read.

The only "real" way to do this right is to set up workgroup security to restrict what a user can do inside the database.

Search this forum for the topic "Windows Security" to see threads on the correct settings needed for a folder holding a database.

 

[The_Doc_Man]

Brian and I posted at about the same time. We agree. Use workgroups to do this, not folder permissions.

 

[iori]

thanks Brian and DocMan for the quick reply. I am not sure if i will get time to setup the workgroup file you guys mentioned but what if i have both the users get read+write rights to the BE folder? will it solve the issue?

As for the workgroup security issue:
i havent done workgroup security before but i have read alot about it and i still have questions on it. if i m not wrong the workgroup file (.mdw) has to be on the server or the drive where the BE is right? so what if someone removes that .mdw file, will it not let users to bypass it and lose that security? also even if i setup the mdw file i still need to give those users BE folder write permissions right? i appreciate your feedback

 

[The_Doc_Man]

It is permissible to individually mark the WKG file as to disallow delete permissions to users. You are right that the WKG file should be in the same place as the MDB file. The name should match the MDB name, the only differences being the file type. Your users must remember to join the WKG. If you secure the MDB according to instructions posted MANY times in this forum, you will be able to restrict what your users can do very cleanly. Now, to answer an implied question that many new Access users have: You use Workgroup Security because (a) You don't want to re-invent perfectly good wheels and (b) Any other type of security (except, perhaps, third-party add-ins obtained for money) will not be "at the right place." Workgroup security was designed into Office products to do exactly what you just described. It is in the right place to do the required job. I.e. it is ALREADY an intrinsic element of Office code.


In general, you need every permission except the ones that allow a user to set permissions or change file ownership. You MUST be able to create and delete files because if you the first user in / last user out, you have to manage the LDB file. (Well, ... Access has to manage it.) You need read, write, update on the LDB and MDB files. Again, you could set it so that nobody except the DB admin has delete privs on the MDB, but whoever does the repair & compaction will, as a matter of course, delete the MDB file.

You need update on the folder itself because when you compact the file, the way Access does this is it creates a working db, then compacts the old db by reading only the "good stuff" into the working db, then it deletes the old db and renames the working db to the original name.

 

[iori]

thanks for the explanation docman