Results 1 to 3 of 3
  1. #1
    snoopy2003 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    72

    Design multi user database - avoid simultaneity transactions

    Hello,



    I am writing a multi user application.
    Will access auto protect records from simultaneity updates
    or should I take care for that ?

    If I should, what is the right way to to it ?

    Can I run queries in VBA by using Docmd method or
    I should use something like dbs.Execute sql, dbFailOnError

    Thank you for your help

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The best MultiUser article I've found: http://www.accessmvp.com/TWickerath/.../multiuser.htm

  3. #3
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    This may be more info than you want but for me and multi-user applications, I make the decision of using MSAccess for the backend tables (always splitting to a frontend/backend) or to instead use SQL Server based on the following:

    (Note: Good MSAccess Form design and table design/relational setup/indexing is always essential for maintaining non-problematic updating of data regardless of backend.)

    1. Critical backing up of the data (SQL Server can do tran-log backing up for point-in-time restores (to the point of restoring data to the last minute)). This is a huge selling point for using SQL Server. Note that SQL Server Express is free although I'd really recommend SQL Server 2008 for any critical data.
    2. Complexity of the relational structure (although I have gotten quite complex with some MSAccess structures).
    3. Speed of the network drive. (if the network drive tends to be slow or problematic, SQL Server will work much more efficiently versus using an mdb as the backend on that network drive.) For me, this is a big consideration for using SQL Server versus MSAccess. I've rarely seen a non-problematic network (without a problem at some point or another) with 100% uptime 24x7.
    4. FrontEnd MSAccess version users. I found having multiple users with different MSAccess versions can be problematic for MSAccess backend tables (especially for our 2010 MSAccess users.)
    5. Connection speed of external users. (again, the less traffic to an MSAccess mdb on the network drive the better for extremely slow or problematic external connections.) If you have slow user connections, consider also designing the frontend MSAccess forms as "unbound" forms. Unbound forms will especially help with transaction update problems for slow connections. Unbound forms will also make slow connection users happier since they will rarely experience 'hiccups' when updating data and the data forms will perform much faster. If any external users have a slow connection, definitely consider SQL Server.
    6. Back-tracing any transactions that took place. If there is a critical need to find out who updated what and when (such as our medical data), I'll use SQL Server and a transaction logging program.
    7. Recordset size. If over 100,000 I'll typically use SQL Server. I believe the max for MSAccess is a 2 gig mdb file (not 2 gig of data).

    For the larger applications, I'll either copy the frontend to each user's desktop (which has SQL Server linked tables in it) or I'll use this script on the network drive: https://www.accessforums.net/code-re...sier-7572.html (which makes updating code much easier - after there's no more updating of code, I'll then consider moving to the user's desktop depending on any problems with the network drive).

    MSAccess does a pretty good job of maintaing transaction updates for multiple users providing there's nothing causing problems for the backend mdb file itself. If there is a problem such as a network hiccup, MSAccess usually prevents things such as orphaned records (for good relational structures.) What's especially nice though is MSAccess's upsizing wizard to upsize the MSAccess tables to SQL Server. Rarely have I had a problem with this and I'll usually design my table structure in MSAccess first and then upsize.

    In my opinion, the ideal situation being an MSAccess Frontend on the user's desktop with linked SQL Server tables in it (and thus avoiding 'any' type of bottle-neck connections to a network drive.) - using ODBC for the linked tables. And also using 'Unbound' MSAccess forms for the Frontend to minimize/eliminate slow or problematic connections to the linked SQL Server tables and maintain extremely fast working forms for data entry/updating. (External users would ideally connect to our system via vpn or citrix and then open the frontend on their desktop.) I found this type of setup worked ideal for the Energy Star db's which had 5+ million records, 500 being updated daily by external users, some with fairly slow modem connections. It's also been working ideally at the hospital and all the different types of applications.

    There are also some coding techniques/miscellanous MSAccess options that will affect performance of the mdb and possible data updating. See Allan's link for other info. Above are just some of the important things I personally consider.

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

Similar Threads

  1. Multi users and Split Database
    By cohrsman in forum Access
    Replies: 4
    Last Post: 08-01-2013, 09:13 AM
  2. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM
  3. Replies: 5
    Last Post: 12-19-2010, 04:40 PM
  4. Replies: 13
    Last Post: 05-18-2010, 01:12 PM
  5. Transactions over multiple subroutines
    By jp2access in forum Programming
    Replies: 0
    Last Post: 08-30-2009, 10:34 PM

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