Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    sunnyday is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    45

    Multi users in a network environment

    Hi,
    I have designed a database which is required to be used by more than 20 users simultaneously at a time. How do I make the userform of the database to update and add records simulateously by concurrent users. The Database has stored DB names with which users will log into as a means of security, the database does not track network or environ usernames. The Database will be shared via a shared network drive.
    How do I achieve this. Will the user get an error that another user is using the DB at the same time;i.e. one user has to close the DB for it to be used by another user?


    Thanks in advance


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Multi-user db should be split design. Backend with data on shared server. Frontend on each user local workstation. Review:

    http://forums.aspfree.com/microsoft-...tc-324677.html
    http://forums.aspfree.com/microsoft-...ue-323364.html

    An all-in-one db can sort of work with multiple users for data entry/edit but the chance for data corruption is compounded.

    Only one user can do design edits.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    As J7 notes - it is all in the correct installation. Access is inherently multi user - there is nothing for the developer to do except to install it correctly and that means the split design of front/back files. This also involves table linking between the 2 files as well as set up of the Trust locations. You want to get an Access text book for the edition you are using if you are going to be responsible for a 20 user database.

  4. #4
    sunnyday is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    45
    Thanks so much for the advice. Someone was complaining that since my multi users will be entering loads of data including file attachments and photos as well, I was wondering if the Database will slow down when running queries and Reports. Access limit is upto 2GB size. I have suggested that I will need to transfer back end of the DB to the corporate SQL server at the beginning (before giving the usesr to start entering data), since the DB has the potential to grow bigger in course of time. Did I say the correct answer? How do I make a few suspecting users trust that this DB will work and not have corruption isssues?The Corporate server should be able to handle large amounts of data, since it works for a huge govt organisation.

    Thanks,
    Last edited by sunnyday; 01-19-2015 at 11:24 PM.

  5. #5
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    It is an issue of pay load; Access is a pick up truck while SQL Server is an 18 wheeler. I always use the hyperlink field type to link to attachments/pics which are in folders outside the database. This way they do not add to the size of the database. A SQL Server carries a bigger load but is not necessarily faster - especially a server doing lots of other tasks. Speed is a variety of things including the LAN and in particular the RAM of the PCs with the front end file. The Access back end file can definitely be put on a Windows Server - but remain as an Access file type which is a lot more manageable for a novice. Managing a SQL Server requires additional training/know-how. You will know after some time period whether you are going to approach the 2G file size - and should have plenty of advance time to transition things to SQL Server - so it is not a choice that must be set in concrete today.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As NTC noted, best to keep files/images external from the db. Embedding lots of files will use up 2GB limit fast. Hyperlink is one way to work with external files. Some image files (bmp, jpg) can be dynamically displayed by using Image control ControlSource property. Even PDFs can be displayed in a WebBrowser control.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    sunnyday is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    45
    HI,
    I have a shared network drive( the shared network drive has already permissions set up for the Dept users to access any folder inside it )where I have created in a folder the back end of the database and also the front end of the database and also have relinked using the Linked Table manager. The problem is if one user goes into the front end file via the shared drive, another user who tries to open the front end , it does not open, takes time... but only opens when the latter user closes his front end. Does anyone have suggestions where and how can I fix it?

    Thanks

  8. #8
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    the shared network drive has already permissions are not proper. Give modify permission to all user coming through front end.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have a shared network drive( the shared network drive has already permissions set up for the Dept users to access any folder inside it )where I have created in a folder the back end of the database and also the front end of the database and also have relinked using the Linked Table manager.
    There should not be one copy of the FE on the server that everyone tries to open!!
    Each user should have a copy of the FE on their computer.

    Re-read Post #2 from June......

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Sunny,

    Further to what others have said, please see/read the material at these links re split database.
    http://www.fmsinc.com/microsoftacces...ter/Index.html
    http://www.fmsinc.com/microsoftacces...ddatabase.html

    Regarding photos/pics etc and Access ---they will "bloat" your database if stored within Access --- you should store them outside the database and use links to access them when needed.

    Good luck with your project.

  11. #11
    sunnyday is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    45
    Thanks Guyz,
    I have split the DB, kept the back end in the network shared drive, relinked it using Linked table manager, and have tested distributing the front end via email to the users and it seems to work! If problem occurs I shall keep updated and will need help.


  12. #12
    sunnyday is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    45
    Guyz,
    I have got a problem now. I have kept the back end of my DB in the shared drive and installed the front end of the DB in the user's computer and tried testing it both using the same DB simultaneously. Problem occurs--- when the user tries to put in information it lets and save as well. In the mean time I tried opening my front end and entered information. Now the back end is storing data from my computer only. From the user's computer its automatically putting in the same values as that of mine although in the user's computer different values had been inserted and saved. Now the result in the back end table is that two records of same primary ID have been saved! which should not have happened as that Table has its own primary key.

    So should I create lock for edited records in the Options for Access?
    My Advanced settings are-
    Default Open mode
    Shared
    Default record locking
    No locks

    Any advice much appreciated.
    Thanks

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How could there be two records with the same PK - is the field actually defined as a primary key?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    sunnyday is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    45
    Sorry for the confusion. My apologies. Problem here is that although I have set the Data entry property to Yes in the user form, it opens up with the same record for editing in both of our computers. Although I enter data and the other user enters different data , the back end table actually saves two identical records of the same Auto ID number. Should I make lock for edited record?it actually saved what I entered into that record and not the other user's data.
    It actually looks like this

    +15 blah blah
    +15 blah blah
    +16 xxxxx
    +16 xxxxx

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You didn't answer the question. See June's question in Post #13.

    the same Auto ID number.
    Is this field defined as a primary key?

    Post your dB or post a pic of the table in design view.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Split DB Question - Multi User Environment Launch
    By aussie92 in forum Database Design
    Replies: 6
    Last Post: 01-22-2014, 03:19 PM
  2. Replies: 3
    Last Post: 06-14-2012, 08:10 AM
  3. Record Locking Multi User Environment
    By praetorianprefect in forum Database Design
    Replies: 5
    Last Post: 04-16-2012, 02:31 PM
  4. Replies: 0
    Last Post: 03-14-2012, 07:23 AM
  5. Replies: 13
    Last Post: 05-18-2010, 01:12 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