Results 1 to 5 of 5
  1. #1
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55

    Question Update Query with Multiple Database Users

    I have a database with the following setup.

    Backend database with all sensitive data saved as .mdb
    Frontend database with forms, reports and a few local tables saved as .mdb

    I am adding a feature that when a button is clicked all the dates in a field called "DateSent" are changed to today's date. My question is, since multiple users are using the database would an update query work without corrupting data?

    I had an old feature that updated data with a VBA Update query and it corrupted a record since I am pretty sure another user had that record open when the update query was run.



    What is the safest way to update every records "Date Sent" field with multiple users using the database?

    Thanks!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You can define how record locking behaves in a query object and I believe you can in a form, too.

    If you are using code, you can also add options to stuff like DAO recordsets and the Execute method of the currentdb object.

    So the simplest might be
    currentdb.execute "MySQLStatement", dbfailonerror

    Making sure each user has their own copy of the front end file will go a long way. Also, I would recommend updating the mdb to an accdb format.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes, it works fine.
    Usu you want individuals to edit/create a record locally on their FE copy, then update the main data. This keeps them out of the main data and frees it up for mass updates.
    If the users edit directly on the table, you may experience slow downs depending, but it updates fine.

  4. #4
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55
    So currently the form in the FE database locks the record the user is on. If the other user does a Update Query on all records (including the locked one), I should be ok?

    If not, and I unlock the record when users are on that record using the form, what potential issues might I run into?

    Thanks for all the help. I have saved in the accdb file format. We just got off XP and Access 2003 so I could finally make that adjustment.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Record locking is a huge and complex thing. This is the order I attack it.

    1. Plan on distributing copies of the FE file to the users.
    2. Develop the application around the business processes workflow in a way the application avoids conflicts (narrow tables based on business rules).
    3. Use the default record locking associated with forms.
    4. Don't create huge queries using SELECT * everywhere.
    5. Use properties in forms to control Allow Edits, Editions, etc.
    6. When edits are not needed, use Open Snapshot on smaller datasets and datasets that you know the user will review each row.
    7. Use error trapping to determine if an update was not successful.
    8. Use error trapping.


    The whole thing can get as complex as you want it to be. If your application is a simple 'forms over data' application, the intrinsic tools available under the Relationships Window, understanding different tasks at various workstations, and using copies of the front end should be more than enough.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple Users in the same Database
    By Vandacardoso in forum Access
    Replies: 20
    Last Post: 07-09-2015, 07:24 AM
  2. Database with Multiple USers
    By stryder09 in forum Access
    Replies: 5
    Last Post: 09-09-2014, 01:07 PM
  3. How to Design Database for Multiple Users
    By waqas in forum Database Design
    Replies: 3
    Last Post: 02-04-2013, 03:28 PM
  4. Using multiple users for a database
    By bigern87 in forum Access
    Replies: 3
    Last Post: 07-03-2012, 02:01 PM
  5. new database with multiple users
    By Inter-Tech Communications in forum Database Design
    Replies: 1
    Last Post: 04-19-2012, 04:43 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums