Access on Sharepoint - any alternatives?

[uyuni]

A while ago I wrote an a multiuser Access application split into the Back End (data only) and Front Ends (everything else). The volume of data is relatively small and the number of users is 3-4 max.

The client is migrating to Sharepoint and I have read a number of posts that caution that it is not a good environment for a multiuser Access application.

What are my options? One idea I had is to have a hosted DB (Azure?) environment and use Access FEs on user machines. Does anyone have experience with this design? Are there pitfalls I should be aware of?

Any other options?

All advice is greatly appreciated.

 

[Pat Hartman]

If the app will never have more than a few thousand records, just convert it to SharePoint. You still may need to make some changes to the FE. Just don't try to run the FE from a SharePoint folder. Each user needs his own personal copy of the FE installed on his desktop. You can use the SharePoint folder to hold a master copy to distribute but you CANNOT all open that same copy.

We need to know more about the whole set up. Is the client going to use Azure desktops? If so, that works like Citrix and so you can continue to use the db as it is. Or, you can convert the BE to Azure and use linked Azure tables. This may require some changes to the App if you are currently using forms bound to tables and using filters to allow the user to select data. This is most inefficient method when the BE is a RDBMS. It is far better to use forms bound to queries that use arguments to filter the data. This allows the server to do the heavy lifting and means you aren't downloading every single record from the BE table. You only retrieve the specific record the user wants to work with.

 

[MajP]

I have done Sharepoint BEs with multi user Access Front ends pretty successfully. They were built as Access split databases that operated for a while on a shared server then migrated the BE to SP. Pretty seamless.
The only big problem is there is not really a good way to enforce referential integrity without using Lookup fields and this IMO can be problematic. So I do all the data integrity on the FE. This includes cascade updates and deletes. I found as long as I do this, there is not much of a difference. I do not create lookup fields in the Sharepoint lists. This works fine if all data entry is from Access. If you are going to allow a combination of Access and SP entry you may have issues requiring use of lookups. I have never built a working app using Lookup fields because I always done it on the Access FE.

A lot of people are getting rid of the Access part all together and using Power Apps for the forms.

 

[GPGeorge]

A while ago I wrote an a multiuser Access application split into the Back End (data only) and Front Ends (everything else). The volume of data is relatively small and the number of users is 3-4 max.

The client is migrating to Sharepoint and I have read a number of posts that caution that it is not a good environment for a multiuser Access application.

What are my options? One idea I had is to have a hosted DB (Azure?) environment and use Access FEs on user machines. Does anyone have experience with this design? Are there pitfalls I should be aware of?

Any other options?

All advice is greatly appreciated.

You can migrate the data from Access tables to SharePoint lists.

Perhaps the cautions you read pertained more to moving the accdb itself to SharePoint, which is not advisable.

A few years ago, Albert Kallal gave a presentation to our Access User Group on SharePoint with Access. You should find it useful

I love PowerApps as part of a hybrid application, not as a replacement for Access front ends. Here's why.

PowerApps run on tablets and smart phones, as well as in the browser. However, they are lightweight in comparison to Access.

If your organization requires a mobile interface to do data entry into a shareable back end, then PowerApps are an excellent tool. But it generally would make little sense to try to move the entire application to PowerApps.

Licensing costs are a major consideration for PowerApps, as well.

I have built one or two PowerApps applications with SharePoint lists, but I find Azure SQL, or hosted SQL Server, to be head and shoulders above the usability of SharePoint lists. The main advantage of SP lists over Azure SQL is licensing costs.

 

[uyuni]

I am leaning towards Azure SQL, but am a little worried about what it is I don't know, i.e. surprises that I may encounter.

The application itself is quite small in size, the tables do not have any referential integrity (it is in the app). There is very complicated calculation logic written in VBA and a bunch of reports.

is it as simple as creating a database in Azure SQL and change the front end linked tables to point to Azure? Or are there some gotchas that I need to be aware of?

Thank you everyone for your help regarding this.

 

[MajP]

The application itself is quite small in size, the tables do not have any referential integrity (it is in the app). There is very complicated calculation logic written in VBA and a bunch of reports.

Those descriptions would suggest SP would be an OK option if you do not with Azure or something else. Small, simple, limited ref integrity, heavy lifting in vba.

 

[GPGeorge]

I am leaning towards Azure SQL, but am a little worried about what it is I don't know, i.e. surprises that I may encounter.

The application itself is quite small in size, the tables do not have any referential integrity (it is in the app). There is very complicated calculation logic written in VBA and a bunch of reports.

is it as simple as creating a database in Azure SQL and change the front end linked tables to point to Azure? Or are there some gotchas that I need to be aware of?

Thank you everyone for your help regarding this.

Given that scenario, I agree with @MajP then.

Azure SQL might be overkill for this particular situation.

On the other hand, Azure SQL is essentially a version of SQL Server "in the cloud", so whatever factors would apply to SQL Server would be more or less applicable to Azure SQL.

In either case, you'll need to design the Access interface to support remote tables. For example, forms and reports should never load more records than the minimum number required for the task at hand. One common "Access" approach to forms is to bind a form to a table and open the form. Then the form's recordset is filtered to display a record. Bad performance is inevitable if you do that with a remote back end (SP or Azure SQL). Instead, the recordsource needs to be parameterized so it never loads more than the required record or records.

So, no, it's not as simple as migrating the data and relinking to the new data source. That part can be pretty straightforward. The complexity is in the interface design for remotely hosted tables.

One other consideration is that SharePoint lists have a load of overhead in the form of meta data fields generated by SharePoint. That may or may not impact performance, but it certainly is messier behind the scenes. Of course, SQL Server tables have only the fields you define.

 

[Pat Hartman]

the tables do not have any referential integrity (it is in the app).

Poor choice. What do you think the database engine is for? Your code cannot enforce RI more efficiently than the db engine can.