Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: Simultaneous edits to a table in Access

    I plan to populate a form with data from a table that is specific to a user (ie table contains data for 20 users, but form returns specific user's data), and then allow the user to update the data records from the form. However, I'm wondering about multiple users. If more than one user is editing their data at the same time, will all of the edits be captured? I'm thinking yes as the users would never be editing the same record at the same time, but instead editing different records at the same time. Does anybody know? I'm not a power user in that I do most of my Access work with WSIWYG so if you can help me, please remember to speak in layman's terms.

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Hi,

    The answer to your question depends rather on which Access version you are using. But a bit of background first. There are essentially three ways, in all Access versions before XP, that you can set an Access form up for multiple users in terms of data edits. You first have to think about what you wish to achieve. 1. Do you want users to be able to edit the same record at the same time? 2. Do you want users to be able to edit different records at the same time? 3. Or do you want one user only to be able to edit a table at any particular time? If the answer is 1, you don't have to do anything - this is the default behaviour. What happens is that if a record is being edited by two users simultaneously, the first person saves the record; any user trying to save their changes after that will be warned that the record has changed since they started editing it. They are given the option to save the changes that they have made (thus overwriting the first person's changes). This strategy is called "No Locks" .

    If the answer is 2, multiple users can edit multiple records at the same time, but there is a nasty catch. Access stores its data in 2K "pages". What this means is that if one person is editing a record, no other users will be able to edit records that exist in the same page. In terms of records, a "page" is 2K of data: depending on how many fields are in the table and what's in the fields, this could represent 20 or 30 or 40 records....it's impossible to say exactly how many without knowing the construction of the table. However, let's try to give you an example. Let's say that your records are numbered 1 to 100. A user starts to edit record 22. This immediately locks a page of data, which is 2K as we said. Other users will not be able to edit any of the records in the page. So other users might not be able to edit records 1 to 40, but will be able to edit (and save) records 41 onwards. When the first user saves the record, the page is unlocked again. This strategy is called "Edited Record".

    Lastly, if the answer is 3, then only one user at a time will be able to edit any of the records in the table. This is the least flexible of the three options, and is known as "All Records". It obviously negates, to a large extent, the whole point of having multiple users in the first place.

    To set any of these options in a form, choose the option from the pull-down list in the Form's "Record Locking" property. Note, therefore, that you can have different locking options in different forms.

    In Access XP (and the upcoming Access 11), the "Edited Record" option has been improved so that only one record is locked at a time instead of a whole data page; multiple users can therefore edit multiple records at the same time without any problems.

    This is all a bit of a simplification but I hope I have pointed you you in the right direction and given you some things to think about. Look up "Record Locking " in Access Help for more information.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Oct 2003
    Posts
    5

    Thanks Andy

    I'm looking to do the 2nd (users editing different records at the same time) in Access 2000 - looks like I'm going to have some problems. I can think of some work arounds - seperating the table, letting individuals edit, and then aggregating - but that seems kind of silly. Would you say that the functionality I am trying to do is really beyond Access 2000? Maybe I should look at storing the data on a SQL server and building an Access front end. Looks like whatever I do it's going to be more work than I had originally planned.

  4. #4
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    It rather depends on how much data you are dealing with. If you are dealing with millions of records, a move to SQL Server might be a good thing and would give you much greater flexibility and performance. I'd also suggest having a look at the MSDE - the Microsoft Desktop Engine. This is, essentially, a cut-down SQL server that runs on a desktop (although you can also put it in a network). You front-end it through Data Access Pages in Access. Your best option would be an upgrade to XP. Against all this, of course, you should weigh up the number of users - total and concurrent - you will have, the amount of data you will be storing and how often your users are likely to experience record locking problems. If, for example, these problems occur only once or twice a day then it might be easier to stick with A2000. If they are occurring so much as to severely affect the inserting and editing of data then you should look for another solutions. I hope this helps and good luck.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •