Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    11

    Unanswered: simplest way to enable simultaneous inserts from multiple users?

    I'd like to have 20+ users simultaneously inserting rows into an Access table; they don't need to be able to modify their data once it's been inserted. I'm trying to figure out the best way to accomplish this. Problems with each option:

    Central back end .accdb populated by each user's local copy of front end: Access performs poorly with more than 5-10 users.

    SQL Server (or other non-Access DB server) populated by each user's local copy of front end .accdb via ODBC: bureaucratic difficulties with getting DB server set up

    Data collection via email: Each user can only respond once to an email, so I'd have to email all users repeatedly. Also, no validation at the user interface level, so users could potentially send a lot of garbage data.

    PHP webpage with ODBC update of table: bureaucratic difficulties with creating webpages on our company intranet

    So here's my plan. Users drop template-based Excel files into a shared directory. Access VBA script regularly checks the directory for new spreadsheets (checking them against a table of existing filenames), imports them, and inserts their data into the target table. Drawback is that this system will only operate as long as I have Access open on my workstation.

    Is there a better solution to this problem?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    central back end and separate user front end, shouldn't be having problems with 5 to 10 users, unless your network trolls are playing silly beggars and they screwed up the network

    even if it is causing problems then resdesign your UI so that you use unbound controls, yes you have to do more work but it will support many many coincurrent users, espelcailly if its 'just' data entry.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2011
    Posts
    11
    shouldn't be having problems with 5 to 10 users
    Right, but I'm probably going to have 20 or more users updating simultaneously. Has that been ok in your experience?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the precise point where an Access applications talking to a JET database (the default set up) is tricky. historically its somewhere round 15..30 users. however as you have stated your applications is data entry then I doubt you will have any problems irrespective or whether you use bound or unbound controls.

    for a multi user application requiring to support many (read 30+ users) then you almost certainly ought to be using unbound controls, disconnected recordsets and so on. below 10..15 then you can use plain vanilla Access and use bound controls However you are right smack in the middle of the chanegover point.

    there are those who argue you should always use unbound controls.

    given that you state the application will be used by around 20 concurrent users for data entry I'm certain you will be fine with plain vanilla access. if in practice you hit problems then a careful redesign by reducing the amount of data pumped up and down the network, that means being very careful with what records you show, carefull use of combo and lost boxes
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2010
    Posts
    601
    I agree with healdem.

    If your app is pulling more data that needed then this will can a lot of performance issues. Switching to a SQL server back end will not help this. It could even make it worse.

    Are your forms opening with a record source that includes all the records in the table?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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