none
Multi user access RRS feed

  • Question

  • Hi all,

    I am new to programming in a multi user environment.

    Can anyone advise on how to achieve the following:

    More than 1 user can access a table and add records at the same time. Each record must be assigned a unique document number. This data must be saved in the table.

    I have tested this in a sample database. I have set the record locking to "Edited Record". I have written code to, as soon as a user opens the form, it goes to the last record and increment the document number by 1, then immediately writes this number to the table with a SQL statement. When the user clicks the "Save" button, another SQL statement then updates that specific record with the entered data.

    This seems to be working to a certain extend, but I still encounter clashes when 2 users enter the form at the same time. Sometimes the same number is allocated to both users, resulting in the data of the last user to exit the form, to overwrite the first save from the first user that exited the form. Or the document number is duplicated in the table.

    Is there maybe somewhere a small sample database which does this?

    Thanks

    Deon

    Tuesday, July 14, 2015 12:45 PM

Answers

  • You are currently using the DocNumber column as an encoding system.  This is not good relational database design as it violates Codd's Rule #2, The Guaranteed Access Rule:

    'Every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.'

    I'd strongly advise that you split the column into its three components.  As the first two represent date parts, if the table already includes a date column you won't need these at all as the year and month are easily extracted from the date.  I assume that the serial number begins with 1 per month per year.

    You'll find an example of a means of computing sequential numbers, both distinct and per group, in CustomNumber.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file caters for conflicts in a multi-user environment.  The option for sequential numbering by group does so per gender; in your case it would be per month per year.

    Whether you store the year and month in separate columns, or derive them from the value of a date column concatenating the three values into your desired format is a simple task in a computed column in a query or a computed control in a form or report.

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Thursday, July 16, 2015 12:14 PM Typo corrected.
    • Marked as answer by Deon SA Tuesday, July 21, 2015 1:58 PM
    Thursday, July 16, 2015 12:09 PM

All replies

  • The first thing you need to do is split the database into Front-end (FE) and Back-end (BE) files. Locate the BE file on a network shared drive, and give each user their own copy of the FE file.

    Tuesday, July 14, 2015 1:53 PM
  • Hi Deon,

    You may be working harder than you need to. Access has a lot of built-in features that you don't need to use code all the time. For example, try the following:

    1. Create a table and add a numeric ID field

    2. Set the data type to Autonumber, and make it a Primary key

    3. Add a couple more fields to the table for the users to enter data

    4. Create a form based in this table

    5. Use the Splitter Wizard  to split the database into a front end and a back end

    6. Give each user a copy of the front end

    7. If you store the backend on a server, make sure the front ends are still linked to it

    8. Have everyone try to enter some records



    • Edited by .theDBguy Tuesday, July 14, 2015 2:01 PM
    Tuesday, July 14, 2015 1:59 PM
  • Hi Lawrence,

    The database is split into a FE and BE. The BE resides on the server. Each user has the FE loaded on their PC.

    Tuesday, July 14, 2015 2:15 PM
  • Hi,

    I will try this way and let you know what the outcome is.

    Thanks

    Tuesday, July 14, 2015 2:16 PM
  • Hi,

    For this test, you can also use the Form Wizard to create the form for you. Just select the table from the Nav Pane and then click on the Create tab on the Ribbon and select Form. Let us know how it goes...

    Tuesday, July 14, 2015 2:35 PM
  • Hi all,

    I have created a table as suggested above. There are the following fields in the table:

    RecNumber - Autonumber

    DocNumber - Text (10) (format needs to be 99-99-999)

    Transporter - Text (50)

    More fields......

    Access increments the RecNumber.

    The DocNumber is determined by code: first 2 digits is the year, next 2 digits the month and last 3 digits is the next number in the sequence. Here is where my issue is. When a user enters the form, Access moves to the last record and reads the DocNumber, adds 1 to the last 3 digits and writes it back to the table. The reason why I am doing this, is so that when the second user enters the form, the same code applies. Access moves to the last record, adds 1 to the last 3 digits and writes the number to the table. When the users has entered all the required fields, the data is updated with a SQL statement to that specific record when the "Save" button is clicked.

    Most of the time, Access processes the transactions as I intend to, but sometimes Access creates empty records with only the RecNumber and DocNumber and nothing else. Sometimes Access creates duplicate records with different RecNumbers, but the same DocNumber with different data in each record. When users click on the "Undo" button, the record is kept, but also with no data in the fields.

    This is not a train smash, but the data looks untidy and unnecessary space is taken up. Every now and then I need to manually delete these records from the database, which takes time.

    Thanks

    Thursday, July 16, 2015 5:52 AM
  • So... are you using a bound form?  Generally Access handles the locking and concurrency issues for you.

    But it sounds like you are manually writing some data "under the covers" when the user begins editing a record? If your user chooses undo and navigates away before completing the edit, you will need to back out your updates manually or they will be left as orphans in the database. You likely can use form events to handle this gracefully, but there is always a risk in the event of a system or application error that your cleanup code won't run and you will have orphaned data.

    You could consider using transactions. However, this approach is much better applied when you use SQL to make multiple related changes within a defined scope of time, and not for an interactive editing session having an open ended duration. 


    • Edited by Gary Voth Thursday, July 16, 2015 6:25 AM
    Thursday, July 16, 2015 6:23 AM
  • You are currently using the DocNumber column as an encoding system.  This is not good relational database design as it violates Codd's Rule #2, The Guaranteed Access Rule:

    'Every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.'

    I'd strongly advise that you split the column into its three components.  As the first two represent date parts, if the table already includes a date column you won't need these at all as the year and month are easily extracted from the date.  I assume that the serial number begins with 1 per month per year.

    You'll find an example of a means of computing sequential numbers, both distinct and per group, in CustomNumber.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file caters for conflicts in a multi-user environment.  The option for sequential numbering by group does so per gender; in your case it would be per month per year.

    Whether you store the year and month in separate columns, or derive them from the value of a date column concatenating the three values into your desired format is a simple task in a computed column in a query or a computed control in a form or report.

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Thursday, July 16, 2015 12:14 PM Typo corrected.
    • Marked as answer by Deon SA Tuesday, July 21, 2015 1:58 PM
    Thursday, July 16, 2015 12:09 PM
  • Hi Deon,

    In my estimation, you are definitely working harder than you need to. Is your database split? When you save the docnumber to the table, you effectively create a blank record. However, you also said that you have some "required" fields. Are they set as "required" at the table level? If so, then the user shouldn't be able to save a blank record. But since you're able to save the docnumber using code, I am guessing that those "required" fields weren't set as required at the table level. If that's the case, you may need some data validation code in the Form's BeforeUpdate event to cancel out any blank record. To avoid all that, you might consider using a separate table for determining the sequence number. That way, you won't be saving a blank record every time you generate a sequence number.

    Just my 2 cents...

    Thursday, July 16, 2015 4:03 PM