Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access for multi-users 2

    I have an Access 97 split database which I wish to be multi-user. I have the following settings on the front and back-end databases:-
    Default record locking - Edited record
    Default open mode - Shared
    Run permissions - Users
    Both the front and back-end reside on a server. The main form has an unbound combo box for filtering in queries relating to other forms which can be opened from the main form. The main form stays open. If one user opens the main form then another user can open it but the combo box cannot display any options. Any ideas?
    Rob

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access for multi-users 2

    Access databases are multi-user by default. Set your locking to No Locks. A lock will still be placed when the edit is written to the record. Also, make sure you're using CurrentDb rather than DBEngine(0)(0) when you reference the database.

    Your most critical step is to make sure that your users are not opening the database exclusive, which is perfectly possible even if you specified Shared as the default.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access for multi-users 2

    Thanks Charlotte. I don't know how you get time to answer these questions but I'm very grateful.

    I mainly use queries for everything in my databases and so I almost completely avoid code. My point is that I don't refer to CurrentDb or DBEngine(0)(0) anywhere in code (easier to maintain and upgrade). Are there any ramifications of your comment on CurrentDb anywhere else other than in code?

    Also I have been wondering if I should have put the front-end of the database on the clients rather than on the server but then I would have to automate updates of the front-end to the clients. Would I get any better performance/sharing by putting the front-end on the clients?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access for multi-users 2

    You might check with your system administrator and make sure that all your users have the right to create files in that particular folder on the server. Otherwise, only the first person who opens the database can access it other than read-only.

    You would see major performance improvements by putting the front-ends on the local machines. The problem of updating the front-ends is a perennial one, and everyone has to find a way that works in their particular situation.

    I don't quite understand what you mean by using queries for everything. Do you mean that you let your users see queries instead of using forms? If so, you certainly won't have to worry about keeping busy fixing the data errors and broken queries.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access for multi-users 2

    Unfortunately the buck stops here (I am the system administrator). I presume you mean the folder with the front-end in it? If so I will check that again.

    When I say I use queries I just mean that I don't access tables directly through code. I only use code for opening and closing forms and reports and for calculations within forms. So the question was are you only referring to an instance of CurrentDB within code or is there some other place from which it is referred ie settings or parameters.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access for multi-users 2

    The only place you can use CurrentDb is in code.
    Charlotte

  7. #7
    DaveShmave
    Guest

    Re: Access for multi-users 2

    Hi Charlotte,

    Just curious. Has it been your experience that No locks positively helps database performance over Edited record? Because I guess I don't understand the statement, "A lock will still be placed when the edit is written to the record".

    Does that mean that setting the Edited record option is meaningless?

    Please clarify for me.

    Thanks,

    Dave

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access for multi-users 2

    No, it isn't meaningless. It controls what type of lock it is and, to a certain extent, when it's placed. For instance, pessimistic locking happens as soon as you open the table, optimistic locking happens when you start to edit a record.

    If you use No Locks, you still get a lock on the edited record or page when you actually start to edit it unless you're using transactions. In transactions, the lock is placed when you hit the Update instruction rather then when you begin the edit.

    What you use and how are tuning and user-friendliness issues in Access. If you lock a record or page or table as soon as you enter it, you're probably going to annoy a lot of other users. On the other hand, if you're an administrator doing updates to reference tables, you probably do want to lock them while you're in them to avoid confusing your users.
    Charlotte

Posting Permissions

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