Pau Lo
asked on
Access database apps migration file share to SharePoint online.
I don’t work directly as an Access developer but do some basic aspects of development and administration for an MSACCESS application with central accdb backend (currently based on Access 2016) that is located a Windows server file share.
Users access and update data in the system via their own front end accdb files from their own personal folders on the same \\server\share. There is a corporate project to migrate all our file share data (located on a standard windows 2016 file server VM) to SharePoint Online within our 365 tenancy, which will include the backend and frontend database files as well as thousands of other files.
The application has been developed over a number of years using multiple versions of Access. In your experience, are such apps ‘geared’ for an easy migration onto SharePoint online, or would they probably require a complete re-development in order for them to work? We may be able to put forward a business case for keeping those files on a traditional file server, based on your feedback on how painful an exercise this may be.
If you have been tasked with similar, what we the key amendments required in making the application (front and back end components) SharePoint online 'ready'? I appreciate it will be a case by case basis but any general thoughts and feedback would be interesting. The Microsoft planning documents for migrating files from file shares to SharePoint/OneDrive only really cover troublesome issues such as file extensions, characters in file paths, limits of file/folder characters etc), and no specifics really on access database applications and the work required to make them work on SharePoint online.
Users access and update data in the system via their own front end accdb files from their own personal folders on the same \\server\share. There is a corporate project to migrate all our file share data (located on a standard windows 2016 file server VM) to SharePoint Online within our 365 tenancy, which will include the backend and frontend database files as well as thousands of other files.
The application has been developed over a number of years using multiple versions of Access. In your experience, are such apps ‘geared’ for an easy migration onto SharePoint online, or would they probably require a complete re-development in order for them to work? We may be able to put forward a business case for keeping those files on a traditional file server, based on your feedback on how painful an exercise this may be.
If you have been tasked with similar, what we the key amendments required in making the application (front and back end components) SharePoint online 'ready'? I appreciate it will be a case by case basis but any general thoughts and feedback would be interesting. The Microsoft planning documents for migrating files from file shares to SharePoint/OneDrive only really cover troublesome issues such as file extensions, characters in file paths, limits of file/folder characters etc), and no specifics really on access database applications and the work required to make them work on SharePoint online.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Presumably if the data was migrated to MSSQL the FE could still be hosted on Sharepoint online and connect/update etc. Or do both components need to be exempt from SharePoint.
The FE should be hosted locally...on each's user workstation
You can have Sharepoint or whatever sharing solution to host a fresh copy that each time the user would get copied to their workstation (at logon for example)
You can have Sharepoint or whatever sharing solution to host a fresh copy that each time the user would get copied to their workstation (at logon for example)
You can use Sharepoint to house your front-end and then copy it locally for use, but that's a lot of trouble for no reason. If you go the route of MSSQL, do note your front-end will probably need reworking.
<<Presumably if the data was migrated to MSSQL the FE could still be hosted on Sharepoint online and connect/update etc. Or do both components need to be exempt from SharePoint. >>
No. As the others already said, you might keep the "master copy" of your app there, but you'd want to download it to use it. You would not want to run it from SharePoint.
With Access, from an operational viewpoint, the only thing you can do with SharePoint is use lists to store data, and that doesn't work very well except for the smallest of apps.
Access is a traditional desktop application, and it wants to run on a desktop with data being on the local LAN (assuming you are using .accdb's to store data).
You can stretch it to use SQL server and grab data remotely, but an app needs to be designed from the ground up with that in mind (just like any application would need to be). You can get reasonable performance with some work, but it takes some effort to get there. As much processing as possible needs to get pushed server side through the use of pass-through queries, views, stored procedures, and triggers.
With any Access app already written that is storing data in an Access database, you have two basic choices:
a. Leave it on a local LAN.
b. Run it on a RDS (Remote Desktop Server) . As far as Access is concerned, everything is still 'local' as it all runs on the server, but you can have your users remote. Only KVM (Keyboard, Video, and Mouse) runs across the connection.
While this works very well, it doesn't scale up well for a large number of users as once you get off a single server and need a farm (multiple RDS Servers), then it starts getting expensive. You are also still stuck ultimately with the 255 user limit.
Anything other than that will be a partial re-write, or a complete re-write in something else.
Jim.
No. As the others already said, you might keep the "master copy" of your app there, but you'd want to download it to use it. You would not want to run it from SharePoint.
With Access, from an operational viewpoint, the only thing you can do with SharePoint is use lists to store data, and that doesn't work very well except for the smallest of apps.
Access is a traditional desktop application, and it wants to run on a desktop with data being on the local LAN (assuming you are using .accdb's to store data).
You can stretch it to use SQL server and grab data remotely, but an app needs to be designed from the ground up with that in mind (just like any application would need to be). You can get reasonable performance with some work, but it takes some effort to get there. As much processing as possible needs to get pushed server side through the use of pass-through queries, views, stored procedures, and triggers.
With any Access app already written that is storing data in an Access database, you have two basic choices:
a. Leave it on a local LAN.
b. Run it on a RDS (Remote Desktop Server) . As far as Access is concerned, everything is still 'local' as it all runs on the server, but you can have your users remote. Only KVM (Keyboard, Video, and Mouse) runs across the connection.
While this works very well, it doesn't scale up well for a large number of users as once you get off a single server and need a farm (multiple RDS Servers), then it starts getting expensive. You are also still stuck ultimately with the 255 user limit.
Anything other than that will be a partial re-write, or a complete re-write in something else.
Jim.
Either the users will use RDS to have them Remote connected or a 3rd part cloud aware SQL engine will be involved as BE (MSSQL,MySQL,PostGre...)