Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    Question What is the easiest way to use Access DB simultaneously on multiple PCs?

    Hello,



    I am no expert in databases nor MS Access, but over the years, I have created a rather complex company database for my business. It was a lot of googling, DIY, trials and errors and so on. But I managed to get it working quite well on a single PC.

    The problem is when I try to make it possible to control simultaneously on multiple computers.

    Every employee has their own Office 365 Business Premium license. The database itself is stored on my OneDrive account. The folder is shared with the employees. They can access it with no problems but if multiple people open it at the same time, a local copy is stored on each PC, OneDrive states that the file was not synchronized and we now have 2 copies of it. The sync is lost.

    I tried several options:
    - split the database to front and back end and upload the backend on our SharePoint, but I couldn't make it work (it's probably too complex for that - a lot of VBA)
    - make it open in exclusive mode (that way only single person could open it but at least the others could not and there would be no desync) - didn't work either, for some reason, the other people can still open the database even if I have it opened in exclusive mode.


    I would be very happy if you could point me in the right direction here. The ideal thing would be if everyone had their own front end connected with server based back end. The database is still under construction so the design is changed very often. But I would just push front end updates to the employees.


    Thanks very much!
    Tomas

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you must split the db , the tables (Backend) will remain in a network folder (or sharepoint)
    then every person gets their own copy of the front end db.

    it works just fine.
    i have all users FE in their own network folder , so I can issue updates with the click of a button.

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If your Access database is being shared/used by multiple users at the same time, it should ABSOLUTELY be split into a single back-end, and multiple front-ends (each user has their own copy of the front-end).
    Not splitting your Access database and sharing it among many users using it at the same time is just asking for trouble!

    The amount of VBA should really have no impact on splitting the databases. Quite simply, the back-end contains all the data tables, and the front-end contains all the Queries, Forms, Reports, and VBA code.

    Here are a few articles for you to read on this:
    https://support.office.com/en-us/art...3-51b1d73498cc
    https://support.microsoft.com/en-us/...crosoft-access

  4. #4
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Thank you guys, I figured that I would eventually need to do that.

    Would the following scenario work?

    I split the database to front end and back end. I store the back end to my OneDrive folder and share that folder with my employees. They will have their front end file stored on a HDD (or their OneDrive). Will they be able to connect their front end to the back end stored on my OneDrive?

    Thanks very much!

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I do not use One Drive, so really cannot address any questions specifically on that. But, basically, the back-end needs to be stored in a location that all your users have access to. Do your users have access to the files on your One Drive folder? Is it read-only permission, or can they update files in your One Drive folder (they would need to be able to do that to update the data).

  6. #6
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by JoeM View Post
    I do not use One Drive, so really cannot address any questions specifically on that. But, basically, the back-end needs to be stored in a location that all your users have access to. Do your users have access to the files on your One Drive folder? Is it read-only permission, or can they update files in your One Drive folder (they would need to be able to do that to update the data).
    Sure, I can set whatever permission I want. But there may be a problem that there is no specific path that can be set (for example C://Database). It's just OneDrive, accessible via the file explorer, but I don't think that it has a specific visible path.

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I found this thread, which seems to state that you cannot use One-Drive to store back-end Access databases and have others connect to it.
    https://answers.microsoft.com/en-us/...1-78095629109f

  8. #8
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by JoeM View Post
    I found this thread, which seems to state that you cannot use One-Drive to store back-end Access databases and have others connect to it.
    https://answers.microsoft.com/en-us/...1-78095629109f
    Thanks for digging into this. What would you recommend me then? What solution to use? Thanks.

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you do not have a Network folder that all have access to that you can put it on, check out some of the recommendations made in that thread.

  10. #10
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by JoeM View Post
    If you do not have a Network folder that all have access to that you can put it on, check out some of the recommendations made in that thread.
    Would a typical FTP server work?

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    wouldn't think so, as advised in the links provided, you need a client based solution. Sounds like your users are not on the same network and perhaps distributed across a country/continents. In this situation I use terminal server/remote desktop. Other options include sql azure but I'm just guessing what is right for you. I recommend you outline your actual requirement, rather than us guessing at possible solutions. How do your users connect? where from? what type of devices do they use? How frequently do they connect? to do what? input data? lookup data? run reports? what is the state of your/their broadband? what is the state of your network? etc

  12. #12
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by Ajax View Post
    wouldn't think so, as advised in the links provided, you need a client based solution. Sounds like your users are not on the same network and perhaps distributed across a country/continents. In this situation I use terminal server/remote desktop. Other options include sql azure but I'm just guessing what is right for you. I recommend you outline your actual requirement, rather than us guessing at possible solutions. How do your users connect? where from? what type of devices do they use? How frequently do they connect? to do what? input data? lookup data? run reports? what is the state of your/their broadband? what is the state of your network? etc
    Okay, I will try to explain as detailed as possible...

    It's not a lot of users. Let's say it's 5 users. I need them to access the database through the internet, it's not (always) the same LAN. The idea is that the front end would be stored in each user's PC locally and the back end somewhere on the internet. The users will simply double click on their front end file and it would automatically connect to the back end. They use PC only, no mobile devices (for this purpose anyway). The database is meant for everyday use. I'm the only one that will be editing the front end. The users will only lookup and input data. Maybe even run some reports in the future. Not sure what you mean by the state of broadband and networks. The office has something like 50/20 mbps internet connection.

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    are the users in the office or working from home/cafés/hotel/other clients guest networks whatever? If all in the office, do they share other data (spreadsheets/word docs/etc) and if so where is this stored?

    Not worried about editing the front end, that should always be separate - it is users inputting/editing data that matters.

    Re state of broadband/networks - I was referring to speed, frequency and period of connectivity interruptions

    Also, what is your budget? maintaining databases on the internet is not a cheap option, particularly when you take into account the security implications. And how much time are you prepared to invest in modifying your app to work off an internet based back end (i.e. not client based). If not much, then I suspect terminal server/citrix is your only option.

  14. #14
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by Ajax View Post
    are the users in the office or working from home/cafés/hotel/other clients guest networks whatever? If all in the office, do they share other data (spreadsheets/word docs/etc) and if so where is this stored?

    Not worried about editing the front end, that should always be separate - it is users inputting/editing data that matters.

    Re state of broadband/networks - I was referring to speed, frequency and period of connectivity interruptions

    Also, what is your budget? maintaining databases on the internet is not a cheap option, particularly when you take into account the security implications. And how much time are you prepared to invest in modifying your app to work off an internet based back end (i.e. not client based). If not much, then I suspect terminal server/citrix is your only option.
    Sometimes from home as well. Yes they do share other data through OneDrive, but I don't see how that's relevant.

    I need to clarify - it's a small business and I just want a simple solution how a few people can use the database simultaneously. There is no budget, I don't plan on investing anything into this. I'm looking for a free solution. The database is only a smart part of the company, everybody does much more and I can't afford to work on a database the entire day.

    This terminal server - how would that work regarding the database?

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't plan on investing anything into this. I'm looking for a free solution.
    Regret you will be disappointed. Simply there are no free options available. Onedrive and its like are about sharing files and much more appropriate to the consumer market rather than the business market, even if it does come as part of a business package. It is a completely different proposition to sharing data - it doesn't, it shares files. e.g. two users open an excel file, user 1 makes a change. user 2 is not aware. user 1 saves the changes, user 2 is still not aware, user 2 makes some changes and save the file - under a different file name. Or if they successfully save as the same file name, then user 1's changes are lost.

    Re terminal server, find a local (i.e. same country) supplier - some are global and will have servers in many countries. They are basically set up like a normal network server, but connect via the internet. I'm not in the business of recommending suppliers, who was good/cheap/flexible last year will almost certainly be different next year and individual requirements differ. I would google something like 'best terminal server supplier' or similar, you will find a number of review sites.

    Each user has their own 'space' (desktop/documents/pictures/etc) and each has their own copy of Office on the server. They also have access to shared folders (under your management). For Access, you would put the back end in a shared folder and a front end for each user on their individual terminal server desktops. Performance is usually good since it is only screen refreshes going one way and keyboard and mouse movements the other to take up bandwidth across the WAN. And other than relinking there should not be a need to modify your application - however your db should be designed for multiple users, if it hasn't you may find you will need to make some changes. The other benefit is that if the connection is lost, since everything is running on the server there is no risk of partially completed transactions leading to corruption.

    Users would need to log on to the server using remote desktop to use their front end - much the same as windows login to their computer.

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

Similar Threads

  1. Multiple recordings simultaneously ?
    By bip54 in forum Access
    Replies: 2
    Last Post: 04-12-2017, 07:28 AM
  2. Replies: 6
    Last Post: 03-09-2016, 06:30 AM
  3. Replies: 6
    Last Post: 02-21-2014, 04:58 PM
  4. Replies: 4
    Last Post: 07-13-2012, 12:47 PM
  5. Run Multiple Queries Simultaneously
    By Shatterday in forum Programming
    Replies: 1
    Last Post: 03-09-2012, 08:54 AM

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