• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Access Forms

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.

  • 2
2 Solutions
Leigh PurvisDatabase DeveloperCommented:
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.
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
Leigh PurvisDatabase DeveloperCommented:
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)

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now