Open Exclusive

[alanij]

I have seen threads on this subject but not really seen a solution as such.

I have created an Access database that will be shared by 4 admin staff in an office at work. I am trying to set it up so that only one user can access it at a time to save double inputting issues of data entry. (Also they want it set up in this way.)

I have set it to 'Open Exclusive' and set the Advanced Options Default Open Mode to Exclusive, yet more than one user can access the database. I would have thought this would show an error message saying it is opened exclusively by another user?

I am obviously missing something here...... but what!?

Thanks in advance!

 

[NigelShaw]

Hi,

i dont know if there is a sure way of doing this but you could set a value to be checked. Lets say for example you have a table that holds 1 value only and this value = True

When your database is opened, an event can be fired to check this value

if the value is false or not present, open the database. if the value is true, dont open the database. As long as the code is on the front end of each user, the same code will be fired.

Public sub On_Startup_Value_Check()
    'MsgBox ("This is opened by " & Environ$("username"))
    Dim bolStrtChk As Boolean
    Dim strCurrUsrNam As String
    Dim strYourUsrNam As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    'set your username
    strYourUsrNam = Environ$("username")

    'collect the logged on details from the table
    bolStrtChk = Nz(DLookup("CurrentUse", "tblInUse"), False)
    strCurrUsrNam = Nz(DLookup("OpenBy", "tblInUse"), "")

    'check the value
    If bolStrtChk = True Then
        MsgBox ("This Database is currently in Use by " & strCurrUsrNam)
        'application.Quit
        Exit Sub

    Else
        'enter the values
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblInUse", dbOpenDynaset)

            With rs
                .MoveFirst
                .Edit
                !CurrentUse = True
                !OpenBy = strYourUsrNam
                .Update
            End With

            rs.Close
            db.Close

            Set rs = Nothing
            Set db = Nothing
        End If
        
        MsgBox (strYourUsrNam & " Has successfuly logged in")

what that should do is check the table for a value. if a value is found, supply a message to the user and close the database.

this code will clear the details in the table ready for the next user-

Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblInUse", dbOpenDynaset)

    If Not rs.BOF Then
        rs.MoveFirst

        With rs
            .Edit
            !CurrentUse = False
            !OpenBy = ""
            .Update
        End With


        rs.Close
        db.Close

        Set rs = Nothing
        Set db = Nothing
End If

MsgBox (strYourUsrNam & " Has successfuly logged out")

Im sure there are many ways of doing this but this would just be an example of what could be done.

HTH


Nigel

 

[NigelShaw]

Hi,

just wanted to check. did this help at all?


regs


Nigel

 

[alanij]

Hi

Not had a chance to look yet but will do and let you know.

Thanks for your post by the way

Cheers