Access Local Table, with multiple users, who click exact same time issue RRS feed

  • Question

  • I have a legacy 2007 mdb set up for multiple users.  It is not a split mdb.  There is a function that adds rows to a local table.  A button-click fires off this function.  If two users happen to click at the exact same time, one of the users gets an error message that "The changes you requested to the table were not successful because they would create duplicate values...".  If they click at separate times, this error does not occur.

    Currently the code to open the table is:  SET rst = db.OpenRecordset("tablename", dbOpenDynaset)

    I do not know enough about Access to know what recordset options to use to keep this error from happening.

    I read about ADO recordsets and changed the code to open the table to this:

    rst.Open "tablename", CurrentProject.Connection, adOpenDynamic, adLockPessimistic

    This did not solve the issue.

    What recordset options would allow this function to run if two users happen to click at the same time?


    • Edited by em_rdh Monday, January 6, 2020 6:14 PM
    Monday, January 6, 2020 6:08 PM

All replies

  • None.

    Rather, this condition should be anticipated with a good error handler; one that if certain Err.Number values occur indicating multiuser conflicts, goes to sleep a random amount of time and tries again. Repeat for a certain number of times. Only then pop a message to the user if she wants to wait more, or exit without saving.

    Your configuration with a single database at a server location is a REALLY BAD IDEA. It should be split.

    -Tom. Microsoft Access MVP

    Monday, January 6, 2020 6:17 PM
  • I tried RESUME after checking the error number.  An ODBC call failed message displays now.  That seems odd to me since the table is local, not linked.  However a linked table is used as the source to create this local table.

    Is this the type of coding for an error handler that you're referring to?

    Monday, January 6, 2020 8:18 PM
  • I could not get the error trapping to work correctly.  I ended up creating a new table based on the current user's name.  It's probably a terrible idea but it works, so far.

    Yes, I agree with your comment about the database needing to be split.  

    Thanks for taking the time to reply.

    Monday, January 6, 2020 9:48 PM