none
Access Desktop Database & SharePoint RRS feed

  • Question

  • Hi.....

    I would like to clarify the following with Access 2016, as part of office 365. My organisation is currently migrating to the cloud and soon will have no file servers to share Access databases. What options are available to share an Access desktop database amongst 5 individuals all in different locations. Would the following strategy work like a file server solution:

    1. Split the database
    2. Export the tables from the backend to SharePoint lists from my laptop
    3. Distribute the frontend to multiple users (only 5) to store on their laptop to enter data and run reports only

    Would appreciate any assistance, I have searched and searched but unable to find a solution I can understand

    Regards

    Geoff

    Saturday, July 27, 2019 4:44 AM

All replies

  • Hi Geoff

    I run MS Access 2016 in the cloud. Its a FE/BE database, (both FE and BE is in the cloud) The installation is on a Windows Server 2012. Via VPN and Remote Desktop users kan work from any PC or Mac via a standard Browser. You can run the database even from an iPhone or iPad after installing the free Microft Remote desktop client.

    Since the database physically is running on the Windows Server 2012 its fast and not depending on the users bandwith.

    I have customers running my app for craft breweries as described above.

    Been doing it for twp years now.

    Each user have their own login and their own copy of the FE.

    They share the drive where the BE database is.


    Cheers // Peter Forss Stockholm


    Saturday, July 27, 2019 12:27 PM
  • It's my understanding that SharePoint is a product that needs to be installed on a server. So you will need to retain at least one server. Here are the requirements:

    https://docs.microsoft.com/en-us/sharepoint/install/install-for-sharepoint-server-2019

    In addition, I have also heard that SharePoint lists cannot process VBA commands so all ACCESS projects need to be converted to use macros only. Also, does this discontinuation of support article apply to you:

    https://support.office.com/en-us/article/SharePoint-Online-Public-Websites-to-be-discontinued-e86bfd2f-5c7d-446f-a430-7cfcc0130916

    Sunday, July 28, 2019 2:36 PM
  • My experience is similar to the advice you have been given by both LE and PF.  I use Remote Desktop Server a lot as described by PF and it works very well.  It is a product from Microsoft (Remote Desktop Server) or a product from Citrix (Terminal Servers) who actually invented this space - - - or it is a service from Microsoft and lots of independent hosting companies....  because the technology is quite complex one would probably use a service provider that does it all the time.  I am a user of it, not a manager of it.

    When 365 / SharePoint list was first introduced I was quite interested in using it as a back end table holder for geographically separate users.  In the end I no longer do it.  It is viable but limited.  The tables need to be small and Access tends to hang when the WAN is in-between the front file and that back tables.

    As PF outlines with RDS - the front and back are co-located so it is very reliable and there are no limitations at all -- well except for the cost perhaps.....

    Wednesday, July 31, 2019 4:26 PM
  • Hi

    It is possible to have a remote desktop soluition, as I described it above, to a cost of 75 Euro per month , per user. Office Professional, Back-up and anti-virus included in that price. 


    Cheers // Peter Forss Stockholm

    Wednesday, July 31, 2019 7:18 PM
  • Hi All,

    Thanks for the contribution, with further investigation, I can only offer the following

    not sure if I'm on track:

    - A desktop access DB needs to be shared on a traditional file server

    - Support for Access Web DB ended in April 2018 (no use building this type web database)

    - Alternatives to Access is Power Apps, Power BI & Flow

    - I have had no success in replicating Access macro & module functionality in Power Apps

    - Power Apps is all about SharePoint lists, but how do you add functionality (in a no code solution...?)

    However highlighting these issues to our IT guys, I am led to believe a traditional access DB

    can be run on OneDrive for Business....🤷‍♂️

    Would appreciate all feedback

    Best Regards - Cheers

    Geoff

    Saturday, August 24, 2019 4:51 AM
  • I would state some of your points differently:

    1. - Alternatives to Access is Power Apps, Power BI & Flow

    *** rather I would say that possibly your requirement can be met by Power Apps/BI/Flow; but these are not alternate database applications like Access.

    2. - I am led to believe a traditional access DB can be run on OneDrive for Business 

    *** I don't think this is correct if you are saying the front end is on the user's PC and the back end is on OneDrive.  That puts the WAN in the middle which is generally not successful.  But you might try it and see.  I'm still not sure it is viable if both front end and back end are on OneDrive.

    I believe the only cloud enabled approach to a multi user Access application (or any classic Windows application) is the Remote Desktop Server deployment method as described above by PF.

    Saturday, August 24, 2019 4:16 PM
  • Hi.....

    Really appreciate the feedback....

    Not being an IT pro like many in this forum.....

    - I am concerned with the suck it and see approach with the FE and BE on OneDrive for Business

    - I have since had an IT guy in my organisation suggest citrix as a remote option....?

    - Does this mean my laptop becomes the primary home of the DB, or replaces the traditional file server

    and other users connect via citrix to the DB I store locally

    Hope I'm still on track here....!

    Cheers

    Geoff 

    Saturday, September 7, 2019 5:50 AM
  • Hi Geoff

    The way I suggested is a remote desktop solution.

    I means that the databases, both FE och BE, is running on the remote computer, the server.

    You can "access" the remote desktop via several ways.
    One is via your Internet Browser.
    Another via Microsoft Remote Desktop App

    https://www.microsoft.com/en-us/p/microsoft-remote-desktop/9wzdncrfj3ps?activetab=pivot:overviewtab

    https://www.microsoft.com/en-us/download/details.aspx?id=50042


    Cheers // Peter Forss Stockholm

    Saturday, September 7, 2019 6:32 AM
  • The easiest and most efficient way for limited number of users is to setup an RDS server ...setup Access and your application on it...each user will connect to it's own instance Via RDP..the performance would be stellar if you setup the tables as local and you will have nothing else to bother
    Saturday, September 7, 2019 7:41 AM
  • Citrix and RDP are the same technology, slightly different implementations.  The overall field is also sometimes referred to as Terminal Services.  Citrix invented the space and I think Microsoft bought/licensed their patents so they could develop RDP with the interfaces they want.

    What happens is both FE and BE are co-located with  each other.  So the WAN is not in between them as that is the problem.  Then the user remotes into the FE.  Each user must also have their own FE.

    There are companies that deploy their entire organization with Citrix/RDP where everybody has a virtual desktop for everything.  So it is mainstream inside IT departments.    Where I remote in as a user/developer - I have a full desktop i.e. Office, Acrobat, Outlook, etc.  ...but one can set up a more limited deployment I believe around a single application - - - 

    Saturday, September 7, 2019 12:21 PM
  • Hi,

    what you describe in step 1-3 could be the way to go if your situation and application fulfills some requirements.

    The support article about Access with SharePoint lists has been updated recently and is quite comprehensive now. You should especially read the parts that cover linking to SharePoint lists.

    Office 365 has SharePoint Online integrated. So if your admins have given you the according rights you should be able to use it. You should acquire some knowledge about the handling of SharePoint lists first, to know how to update table/list structures etc.

    I have done a few projects (e.g. this one) with this combination of local Access frontends in different locations with linked SharePoint lists on O365 as common backend. The performance isn't great and there are some other restrictions and weaknesses. However for small applications with a few thousands records, patient users etc. it can be a viable solution.


    Karl
    http://www.AccessDevCon.com 
    http://www.donkarl.com


    Saturday, September 7, 2019 3:37 PM