Access Forms

Posted on 2006-03-22
Last Modified: 2013-11-28
How can more than one user utilize a database when the file is stored on a network.  Obviously, with one person entering data, others would have read-only access.  My situation is that I need to warn the second person in that they have read-only access.  Most users of my database are PC novices and they just type away only for the info to be lost when they leave.

Also, there is some corrupted data in the main table.  Info has been entered in appropriately, but on the next day, chinese characters have replaced some data in certain fields.  The characters are corrupted on only a few records, not all.  And in other fields, data is replaced by duplicate data in previous fields.

I know this sounds ominous, but it is what is happening.  I've looked into the properties of each field and they are correct.  I am most perplexed as to why it corrupts data in some records and not all.

Question by:jwolford22
    LVL 44

    Accepted Solution

    You'll find loads of examples here (and elsewhere) about sharing an mdb file.
    Splitting it into a front end and back end.

    You're seeing corruption due to this - and it isn't going to get better until you implement some of the standard methods.
    LVL 39

    Assisted Solution

    As Leigh states, for a multiuse database, you need to split the database.  It is best not to share the forms, reports, etc. in a multiuser environment.  It can lead to corruption and problems such as you are describing.  It is far better to splt the database (tools, database utilities, database splitter); put the backend (the part with the tables) on a shared server and individual copies of the frontend (everything else).

    Access is a multiuser database but multiuser means sharing the data, not the forms, reports, etc.
    Think of it this way. You go to a dinner party. Seven people but there is lots of food - a great big turkey, stuffing, yams, green beans, pumpkin pie, lots of yummy stuff. But just one chair. Did I also mention - one fork.  And of course, no one wants to use someone else's fork unless it has been washed first so there are lots of trips to the kitchen sink.

    Food = backend
    chair = computer
    fork = frontend
    trips to the kitchen sink = network traffic (which could create an error)

    Get the picture?

    Note: The Microsoft references state you can share an unsplit database in multiuser environment.  Very few others say you can do this reliably.  Even MS says that using an unsplit database or sharing the front end of a split database increases network traffic and therby increases the risk of a problem.

    As far as read only access (file locking):
    One of the biggest concerns in a multiuser environment is what happens if two or more people try to change the same information at the same time.  The way that this is handled is through locking.  If you look up "lock" in Access and VBA help, you will see record locking, all record locking, page locking, optimistic locking, pessimistic locking.  99% of the time you want pessimistic record locking (editied record) or optimistic record locking (no locks).  Then the question becomes optimistic or pessimistic locking.

    The optimist says "It's unlikely that two people will be in the same record at the same time."  The pessimist says "Yah, right!  And most likely, it'll be my boss that's in the same record as me!!"  I our medical office, we have over 2000 patients in the database so you might think it is pretty unlikely that two people would be changing the same patient at the same time.  Until you realize that we are only seeing ten of those 2000+ on any given day.  Until I switched to pessimistic locking for the patient records, we were getting a lot of write conflicts.  I use optimistic locking for the rest of my db.

    If you use optimistic locking, you can reduce the risk of write conflicts by breaking your forms up into subforms or tabs.  Records are saved when you move to a subform or another tab.  You can also put in "Me.Dirty = False" in after update events for some or all controls to save the record.

    If you don't want the second person to be able to save changes to a record when someone else has not finished saving then that is called pessimistic locking.  The code below (thanks to Jim Horn if my memory serves) will notify the second person that the record is in use and by whom.  The biggest downside of pessimistic locking is the famous employee went to lunch without saving or logging off.  The computer goes to screen saver, locks and no one but the missing employee can save the record.  This can be resolved by 1: automatic logoff (of database or windows) after timed inactivity; 2: automatic save by setting the timer interval with the form's on dirty event and "Me.Dirty = False" in the timer; and 3: a reminder to save the record.

    Function IsRecordBusy( _
        rs As Recordset, _
        Optional UserName As String, _
        Optional MachineName As String, _
        Optional CreateMsg As Boolean = True) As Boolean
          ' Accepts: a recordset and two string variables
          ' Purpose: determines if the current record in the recordset is locked,
          '          and if so who has it locked.
          ' Returns: True if current record is locked (and sets UserName
          '          and MachineName to the user with the lock).  False if the
          '          record isn't locked.
          ' From: Building Applications Chapter 12
              Dim ErrorString As String
              Dim MachineNameStart As Integer
    10       On Error GoTo IsRecordBusy_Error

    20        IsRecordBusy = False
    40        rs.Edit                     'Try to edit the current record in the recordset.
    50        rs.MoveNext
    60        rs.MovePrevious
    70        Exit Function               'No error, so return False.

    80       On Error GoTo 0
    90       Exit Function

    100       If Err = 3260 Or Err = 3197 Or Err = 3188 Then   'Record is locked -- parse error string.
    110           If CreateMsg Then
    120               ErrorString = Error$
    130               UserName = Mid$(ErrorString, 45, InStr(45, ErrorString, "'") - 45)
    140               If UserName = "" Then UserName = "(unknown)"
    150               MachineNameStart = InStr(43, ErrorString, " on machine ") + 13
    160               MachineName = Mid$(ErrorString, MachineNameStart, Len(ErrorString) - MachineNameStart - 1)
    170               If MachineName = "" Or MachineNameStart = 0 Then MachineName = "(unknown)"
    180               MsgBox "This record is being used by " & UserName & " on station " _
                          & MachineName, vbExclamation, "Record Lock Warning"
    190           End If
    200       Else
    210           MsgBox "Error: " & Err.Number & ", " & Err.Description & ", IsRecordBusy, " & Erl
    220       End If

       On Error GoTo 0
    End Function

    Call the function with:
        Set rstForm = Me.RecordsetClone
        strBM = Me.Bookmark
        Set rstFormClone = rstForm.Clone()
        rstFormClone.Bookmark = strBM
        If IsRecordBusy(rstFormClone) Then
              Cancel = True
              Exit Sub
        End If
    LVL 44

    Expert Comment

    by:Leigh Purvis
    See I knew Nelson would be along and have looaaads of text on the subject for you.

    I really should do that - and I keep telling myself to.
    But will I listen to me?

    Shut up!
    What?  You shut up!
    Oh - wise guy huh?
    (etc etc)
    LVL 61

    Expert Comment


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now