Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Unanswered: Multiuser DB setup

    I'm setting up a multi-user (5 stations) access db. Users will be allowed to add records via a form and VBA, but no one will be allowed to delete records. I have a few questions about it, though.

    1 - Do I need a special multiuser setup?

    2 - Some people are using A2003, some are using A2007. Does this pose a problem?

    3 - Do I need to use a record-locking scheme?

    4 - Are there any other concerns I should be aware of?

    Thanks for the assistance,

    SL

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    1) No, unless you define setting up a front end & back end as a "special multi user setup". The back end contains the data should be placed on a network accessable location the front end contains all the user interface eg the forms, reports. you woudl be sensible to let each user have their own copy of the front end (the user interface). there are tools int he code bank to help you maintain the front end
    2) I don't use 2007 but in principle I dont' see why it should be a major issue. however the general rule is that the data backend should be the lowest Access version so that all clients can read the data. personally I think its a smart idea that the developer should ALSO be using the lowest version it stops them doing tricks with the new version that cannot be used by all users, however its quite possible that you could create the db in 2007 JET which 2003 JET shoudl be able to understand, its less likeley that a form or reprot developed in 2007 is guaranteed to work in earlier versions
    3) from A2000 on there is an inbuilt locking scheme which offers no, page or row locking so its inbuilt. however you may well need to ensure its turne don on each client machine. forget if you can do that programatically or not
    4) depends on how you see the app developing. if its 5 users and only going to stay 5 ysers then you can get away with standard Access. if you think the app may be used by say 10...20 or more users then you may be better off using a server back end and design all forms and reports using unbound controls. if thats the case then you may want to use unbound controls now to make the migration to a server backend easier in the medium to long term
    if you are going down the unbound or server route then consider getting a good book for the access you are using. AFAIK there is no 'Access Developer Handbook' for 2007.. there is for 2003/XP and most of what they refer to there should work fine in 2007
    I think it would be smart to find out from somone who has used 2007 how reliably it runs 2003 forms and reports or whether you should standardise on the one version
    think about security from day one, 2007 doesn't use the same workgroup security concepts as 2003.
    trawl through the code bank and see if theres other stuff that may be of use (such as getting the network logon)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Sam Landy View Post
    I'm setting up a multi-user (5 stations) access db. Users will be allowed to add records via a form and VBA, but no one will be allowed to delete records. I have a few questions about it, though.

    1 - Do I need a special multiuser setup?
    You need to split the database into a front end and back end. Each user will need to have their own copy of the front end that is not shared.

    This may help: Auto FE Updater

    Also see: Split your Access database into application and data

    2 - Some people are using A2003, some are using A2007. Does this pose a problem?
    It is not a problem as long as the front end is not shared. See above answer.

    You do not want Access 2007 and Access 2003 opening the same database at the same time. I have seen this cause lots of issues.

    3 - Do I need to use a record-locking scheme?
    Probably not. You will need to do some testing with multi users in the split database just to be sure.

    4 - Are there any other concerns I should be aware of?
    Might want to use a UNC path when linking to the back end to avoid mapped drive issues.
    Last edited by HiTechCoach; 06-25-10 at 14:00.
    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

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Thank you to both of you. You have given me a basis for how to design the DB.

    SL

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I agree with the Jaded Developer's advice about developing to the lowest possible denominator. This should also extend to the screen resolution on the development machine if you've got users with varying resolutions. There are a number of hacks for adjusting the form sizes according to the screen resolution of a given machine and they all have one thing in common, they work best when re-sizing the forms up as opposed to re-sizing them down. So use the lowest possible screen resolution for the development that you're apt to run into.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Thanks, I'll keep that in mind, although I think we all use the same resolution here at work.

    SL

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Machines will be replaced, and new machines frequently mean higher default resolutions. As long as it's the end users' machines you're OK. As I said, they general rule i to develop on the lowest used resolution. But if you, as the developer, should get a new machine with higher resolution than the slobs (oops! meant to say end users!) have, you could have a problem. Just something to keep in mind!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Smile

    I wouldn't call them slobs. they're actually good at their jobs. They just don't know how to program in Access.

    Hey, I was demoted from my job as branch manager because of my poor (read non-existent) customer-service/salesperson skills. I came across to customers (potential customers?) more like the locomotive than the dining car. I often tell people that I would have hard time selling a dying man a glass of water in the Sahara Desert (although maybe in today's economy I should change the allegory).

    SL

Posting Permissions

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