Access Database next steps (without breaking the bank)

[Todd Lane]

Hi,

I have been tasked with updating an Access Database that whilst it has some limitations, has been developed and refined for more than 10 years and is working well. Most tables are number based, there is lots of calculations and a gazillion relationships. There all sorts of calculated fields that's numbers will change by simply tabbing off the field and before any save or add button is pressed. Database size is about 700mb, Access Front End is about 200mb in size. Number of users is around 10.

My approach was to use PowerApps with a Sharepoint back end to contain costs.

Simply creating a table with say multiple related items that have fields that change as described above had been quite difficult and required a lot of work (with variables) to come even close.

Im beginning to wonder if my approach has been the best solution?

What is the best practice progression path of upgrading and connecting (from anywhere) and Access Database?

Would love to hear any thoughts

Regards

Todd

[#2]

My approach was to use PowerApps with a Sharepoint back end to contain costs.
Do you want to query data on web browser rather than Access app client?
In other words,you want to run sql on a smart phone or Mac Os?

[#3]

Using SharePoint lists is probably the hardest path with PowerApps. I would use either SQL Azure or hosted SQL Server as the first choice. And perhaps consider Dataverse.

Licensing costs are the biggest constraint, IMO. SQL Azure and SQL Server are premium services, which carry higher costs. 

The other consideration is, of course, why you needed to go this route. PowerApps are best positioned for remote deployment, i.e. on a mobile device such as a smart phone or tablet.

[#4]

Require access to the data from browser or any mobile device from anywhere, not just in the Office on the LAN

[#5]

Hi Todd,
I understand you have a complex Access application, and now require access to the data from browser or mobile device.
My first question would be: all data? Or just a small portion - think the traveling salesman who only wants to look up existing orders and enter a new one.

If all data: that would be a total rewrite using .NET web technologies and a SQL Server BE in the cloud (if staying with MSFT technology stack), or any number of other web development tools I am not qualified to recommend.
This would mean outsourcing the project, since you don't (seem to) have the skills.

If a small portion: create a hybrid application, perhaps PowerApps or a small web app - possibly self-hosted for the remote users, and keep the Access FE for the office workers (possibly using Remote Desktop from home).

[#6]

@Todd Lane 

I agree completely with Tom vS.

I have created both kinds of PowerApps over the last couple of years. Only one of them  is intended to replicate most of the functions of a small Access application. It is deployed in a very unusual environment where it is the least difficult choice.

All of the rest are hybrids. The main application remains in Access; one data collection function can be split off into a PowerApps application.

Here's a link to a YouTube video demonstrating the Stock Take function for Northwind Developer using...

With regard to my library app, the one which attempts to provide a more complete application, I found that I can replicate data collection tasks you'd normally include in an Access application, along with basic data management tasks (screens to maintain short lookup tables, for example). However, the way the coding function works in the Power Platform environment, which includes PowerApps, means that things we take for granted in the VBA environment are difficult, at best. 

Take, for example, the ability to create a Public module for Public functions used more than once in your Access application. That is not possible in a PowerApps application. If you are used to using Macros in Access for everything, and have an application so basic that this isn't a factor, PowerApps are probably fine. They'll be a lot like what you do with Access Macros, but not VBA.

It's a big hassle, at best, because code to complete a task as simple as refreshing a Collection has to be written over and over in any place in the app where that needs to be done. You can't write the code in one place and call it from other places because that's not how the PowerApps environment works.

Well, that's not 100% true.  I have discovered a workaround, but it's ugly and resource intensive. It involves putting the code on a control on a helper screen which is loaded in memory but not displayed, and then executing that code. On a small app with a handful of screens and very limited data, the additional resource load that entails might not be a show-stopper. In an app with many screens, a lot of data and complex processing, it is just ugly.

To sum up, I'm a fan of PowerApps in the appropriate environment. They are a complement to Access, but not really a replacement. Think of it this way:

Data Collection: PowerApps +1

Data Management, Data Reporting: Access +10

[#7]

It just dawned on me that I referred to Collections. In case someone wonders what I'm on about, Collections in PowerApps are similar to VBA collections in some ways, but not the same thing in other ways. Moreover they are used differently.

[#8]

Good.
I prefer asp and php to write dynamic web pages which can generate report by sql like (http://e.anyoupin.cn/
and
http://anyoupin.cn/bsbm/stu60/sqlDao/sqlEditor_dao.html)

You can have a try.