Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Multiple users (Access 2007)

    On our network server, we have Access linked to an SQL database. Two or three users often open Access at the same time, and most of the time this works fine, but occasionally a user gets the message "The database has been placed in a state by user 'Admin' that prevents it from being opened or locked." when they attempt to open Access while another user is already using it. The problem can be worked around by getting the first user to close and reopen Access, after which the second user can then open it also without getting the message.

    To avoid this issue, I gather that it is possible to create multiple copies of the Access .mdb file (containing the queries, forms and reports) as a "front end", so that each user can open a different copy, with all copies linked to the common SQL database (containing the data tables) as a "back end". Would this work OK?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Multiple users (Access 2007)

    Yes that works Ok, and is pretty much standard practice. Each person has their own copy of the front-end, which can be kept on the local hard drive.
    You just need to make sure that any updates to the front-end are distributed to everyone. I add a version number so I have a way of checking whether a particular copy is up todate.
    Regards
    John



  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Multiple users (Access 2007)

    Absolutely this will work. This is what all Access professionals recommend.

    I keep a master copy of the Frontend on the server, which is linked to the backend (Access or SQL backend). Then distribute copies of the Frontend (which are already linked) to each workstation.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Multiple users (Access 2007)

    Thanks for this information. We were thinking of placing the copies of the Access .mdb file on the server itself, to make it easier to ensure that all versions are updated together.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Multiple users (Access 2007)

    Your performance would be better if the individual frontends were sitting on the workstations, rather than the server. This greatly reduces your network traffic.

    I generally have a small .bat file on the server which copies the "master" Frontend down to the local drive, and then provide a link to this .bat file in each user's Startup folder so the workstation always gets the latest FE at boot-up. I also have a Version Number in both the Frontend and Backend. When the user opens the FE, it checks the Version Number against the BE version, and tells the user they must download latest FE if they don't match.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple users (Access 2007)

    Hi Mark, do you have a table in the backend storing the version number of the frontend?
    Can you post your .bat file?

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Multiple users (Access 2007)

    My BE table is tsysControl, and it has 3 fields:

    ID - (PK, value=0, even though there is only 1 record)
    Version - (data type is up to you, I use a date/time field)
    AllUsersExit - (boolean, used to signal/force users to exit - this is something else I have, not related to this issue)


    The .bat file is very simple; of course you must substitute you own folder names:

    md "c:somefolder"
    copy "serverFolderOnServermydb.mdb" "c:somefolder"


    The "md" is the Make Directory command. It will make this directory if it doesn't already exist.

    I encased the folder and filenames in quotes, but this is only necessary if you have a path that includes a blank. Notice that I also used the URL of the database on the server; this avoids any issues with users having different mapped drives.

    I may also have other lines in the .bat file to copy any other files I want locally. For example, maybe a .jpg file that contains the company logo, various .wav files I may play for specific user warnings, etc.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple users (Access 2007)

    Thank you Mark
    So, every time the user's pc starts, the frontend gets copied even if it's unchanged?
    The user just receives a warning it's time to update the frontend but it's the user who has to copy the updated frontend from c:somefolder, right?

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Multiple users (Access 2007)

    The "automatic" copy at boot-up is one way to go. It does ensure the user always gets the latest version. If the FE is very large and/or not updated too frequently, I may have a more manual way to trigger the .bat file rather than putting it in Startup; often this may be just a shortcut on the desktop.

    The warning is there in case the user hasn't rebooted since the latest update was installed on the server. Depending on the situation, the user would have to know how to trigger the copydown; in most cases, where we've set it up that way, they just know to reboot (it's easy to remember).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple users (Access 2007)

    Oh ok, I thought C:somefolder was not the folder containing the frontend, while it is <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Multiple users (Access 2007)

    Thanks for this information. Our users are in different locations and log on to the server using remote desktop over the Internet, so the copies of the Access .mdb would all be on the server.

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple users (Access 2007)

    Isn't remote desktop a type of software which lets users operate one PC while using another?

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Multiple users (Access 2007)

    Yes. But a server can be set-up to host multiple sessions at the same time. I think the standard Server software license allows 2 simultaneous connections, and you need additional licenses to have more; but I could be wrong here.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Multiple users (Access 2007)

    As our users are in different locations and log on to the server using remote desktop over the Internet, the copies of the Access .mdb would all be on the server. If different users were to open different copies of the Access .mdb either by clicking on a designated copy or by selecting it from within Access, presumably this would work OK. However, our users normally open Access via shortcuts, to specific forms and reports, that are set up on their desktops. The shortcuts are created by dragging a form or report icon from the Access menu to the desktop. If each user's shortcuts are created by dragging icons from their designated copy of Access, will this associate the shortcut with the designated copy of Access, or will this need to be configured specially somehow?

  15. #15
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Multiple users (Access 2007)

    I don't know for sure, but I've got to assume it is through their designated copy of Access.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Page 1 of 2 12 LastLast

Posting Permissions

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