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.

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.

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

Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.