Results 1 to 5 of 5
  1. #1
    thebigthing313 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2014
    Posts
    119

    Best way to approach multiuser database design when not always connected to server

    Hello all,



    I have experience using Access for multiuser purposes within one network in an office, but I am now curious as to what is the best approach for using Access when users travel and will not necessarily have access to the network a database back-end would be hosted on?

    I am using Access 2010, and currently all data inputted is first recorded onto paperwork which is then manually added on to the Access database back in the office, but we would like to explore the idea of data entry on-site (wherever a person goes), where they won't necessarily have Internet connection. It would be a complete hassle to have to keep a copy of the backend on all devices that has to be updated constantly once all new entries from all devices have been appended to a master copy of a database.

    We are a relatively small operation, and at most, 12 devices are to be taken out of the office to perform tasks, and we would like to find a way to enter data on those devices wherever they are, and then have them sync once they can connect to the office network.

    I have tried researching options such as hosting on a cloud, and using services such as Sharepoint and what not, but it's just an overwhelming amount of information that I can't sort out since I'm not sure on what direction to take in the first place.

    If anyone could suggest what my options are and include resources that I can read up on, that would be greatly appreciated.

    Thank you very much.


    EDIT: Suggestions with minimal cost would be preferred :P

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,684
    Bing: Access synchronization 2010

    http://social.msdn.microsoft.com/For...orum=accessdev
    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
    thebigthing313 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    119
    Thank you for the link. So it really does seem that hosted Sharepoint is the most efficient option for me.

    Few questions to see if I understand correctly:

    1. Would it be correct to use Sharepoint to be a back-end to store all the data and tables, and then use multiple copies of a front-end MS Access application distributed to each device?

    2. Linked to the above question, I am not really interested in the browser-application that comes with Access Services on SP since it seems like it doesn't allow for VBA. Does the approach in my first question bypass this problem? Or is there a benefit to designing the forms and reports using only macros as SP only allows for that.

    3. As for licensing, all the plans seem to be priced at a per-user/per month rate. However, do I really need to purchase multiple licenses for multiple users or can I just have all the Access front-ends link to the same SP account that stores the tables? And if so, would that allow for concurrent change of the backend data on SP?

    Thank you for your time.


    EDIT: I tried looking for an option to move my thread to a more appropriate part of the forum, but I assume only moderators can do that?

  4. #4
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    There is a way to do it which would take some serious time to code up.

    1. Add a date/time field which defaults to now() to every table on your BE.
    2. Make a copy of the BE to the local hard drive.
    3. Make a routine on your start up form which checks to see if it can locate the network BE, if it is not there default to the local HD version.
    4. Make a routine that if the network BE is found, compare the date/time stamp between the network BE and the local BE. Overwrite the later timestamped version of the record with the newer version.

    You will have to be careful about autonumbering fields. You may also need to have some checks to see WHO entered the data. A supervisor might need to have privilege over an employee.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,684
    I did build a database that is distributed to field offices that are off the network. They enter data and at end of season the file is submitted to main office and records are imported to main file. But these are all new records and no existing data in the main file are affected. I rejected autonumber as primary key because it made the import too complicated.
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-13-2013, 03:03 PM
  2. Replies: 6
    Last Post: 02-22-2013, 11:28 PM
  3. Looking for an alternative approach to my mdb design
    By Chuck55 in forum Database Design
    Replies: 4
    Last Post: 05-23-2012, 05:54 PM
  4. How to use database when not connected to network?
    By justhininabouti in forum Database Design
    Replies: 5
    Last Post: 03-21-2012, 02:49 PM
  5. multiuser for access database
    By cupidleomanoj in forum Access
    Replies: 4
    Last Post: 09-28-2010, 10:49 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
  •  
Tech Forums: Microsoft Office Forums