GPGeorge
George Hepworth
- Local time
- Yesterday, 16:41
- Joined
- Nov 25, 2004
- Messages
- 2,962
Are you an experienced Access developer wondering how to take your skills mobile? I'd like to share with how I’m replicating the Northwind Developers Edition template from Access—step by step—in PowerApps.
Several months ago, I decided to start a project that had been in the back of my mind for a couple of years. I wanted to see whether I could replicate some, or even most, of the features and functions in the Northwind Developer Edition template as a PowerApps app. Spoiler: Not everything in the template is going to go to the cloud.
But that's not the end of the story.
Along the way, I found myself looking for ways to compensate for the limitations of the "Low Code" environment in PowerApps.
I found myself relying on SQL Server Stored Procedures to do the heavy lifting that VBA handles so easily in Access.
I was clumsy at first, so I leaned heavily on LLM AI's ChatGPT and Claude to learn how to better exploit SQL Server stored procedures. But over a few weeks, the light came on for me.
Finally, diving in with both feet, I developed an approach and a design pattern that largely divorces a PowerApps app from CRUD data operations and data validation. Think of it the way you would think of an Access/SQL Server FE and BE design, except that with PowerApps you don't have the power of bound forms and reports that you get with Access. What would you have to do to make that work?
My response was to move as much of the CRUD data management and data validation as I could into Stored Procedures in the database on the server.
With logic and data management removed, the PowerApps interface became light-weight, but also -- at least in my view -- much more approachable for Access developers at any level. You do need to know how to write basic interface oriented code in PowerApps. If, like me, you're more comfortable with SQL Server, though, the path is right there.
Another advantage I see in this design pattern is that, with the business logic largely contained within the SQL Server database, connecting virtually any interface to it should be possible. It could be an unbound Access FE or a web application invoking those same Stored procedures.
Over the weekend, Mike Wolfe has written a blog post about the debut presentation I gave early on. I think he does a much better job than I do in explaining it, so hop over if you'd like to learn more.
A Request for Help
So far, so good. I've published 6 videos so far on this project on my PowerApps 4 Access Devs with more to come.
What I need more than anything now is feedback.
For one thing, I'd appreciate help refining the Stored Procedures. I'm by no means a great coder. If you have SQL Server skills, and want to see how far this approach can be pushed, I'd love to hear from you.
The TSQL script to recreate current version of the Northwind Developer Database is on GitHub. Have at it.
I'll be putting code and samples from the PowerApps app there soon as well.
Summary
I'm not selling anything, nor am I asking for likes or views. What I am asking for is serious discussion and recommendations about the potential for PowerApps as a Front End as part of a desktop/cloud development environment for developers like you and me.
If you want to try it out, let me know. I'll see about getting you a copy of the most current version of the PowerApps interface to explore on your own M365 site.
There is a Microsoft licensing cost to use PowerApps and more to use SQL Server as the data source, so you'll need to be seriously interested to participate. I can't afford to provide licenses to anyone else, even though I would very much appreciate your participation. However, if you already have the required licensing, adding an additional app to your inventory won't be a problem. And I'd really appreciate the help.
Several months ago, I decided to start a project that had been in the back of my mind for a couple of years. I wanted to see whether I could replicate some, or even most, of the features and functions in the Northwind Developer Edition template as a PowerApps app. Spoiler: Not everything in the template is going to go to the cloud.
But that's not the end of the story.
Along the way, I found myself looking for ways to compensate for the limitations of the "Low Code" environment in PowerApps.
I found myself relying on SQL Server Stored Procedures to do the heavy lifting that VBA handles so easily in Access.
I was clumsy at first, so I leaned heavily on LLM AI's ChatGPT and Claude to learn how to better exploit SQL Server stored procedures. But over a few weeks, the light came on for me.
Finally, diving in with both feet, I developed an approach and a design pattern that largely divorces a PowerApps app from CRUD data operations and data validation. Think of it the way you would think of an Access/SQL Server FE and BE design, except that with PowerApps you don't have the power of bound forms and reports that you get with Access. What would you have to do to make that work?
My response was to move as much of the CRUD data management and data validation as I could into Stored Procedures in the database on the server.
With logic and data management removed, the PowerApps interface became light-weight, but also -- at least in my view -- much more approachable for Access developers at any level. You do need to know how to write basic interface oriented code in PowerApps. If, like me, you're more comfortable with SQL Server, though, the path is right there.
Another advantage I see in this design pattern is that, with the business logic largely contained within the SQL Server database, connecting virtually any interface to it should be possible. It could be an unbound Access FE or a web application invoking those same Stored procedures.
Over the weekend, Mike Wolfe has written a blog post about the debut presentation I gave early on. I think he does a much better job than I do in explaining it, so hop over if you'd like to learn more.
A Request for Help
So far, so good. I've published 6 videos so far on this project on my PowerApps 4 Access Devs with more to come.
What I need more than anything now is feedback.
For one thing, I'd appreciate help refining the Stored Procedures. I'm by no means a great coder. If you have SQL Server skills, and want to see how far this approach can be pushed, I'd love to hear from you.
The TSQL script to recreate current version of the Northwind Developer Database is on GitHub. Have at it.
I'll be putting code and samples from the PowerApps app there soon as well.
Summary
I'm not selling anything, nor am I asking for likes or views. What I am asking for is serious discussion and recommendations about the potential for PowerApps as a Front End as part of a desktop/cloud development environment for developers like you and me.
If you want to try it out, let me know. I'll see about getting you a copy of the most current version of the PowerApps interface to explore on your own M365 site.
There is a Microsoft licensing cost to use PowerApps and more to use SQL Server as the data source, so you'll need to be seriously interested to participate. I can't afford to provide licenses to anyone else, even though I would very much appreciate your participation. However, if you already have the required licensing, adding an additional app to your inventory won't be a problem. And I'd really appreciate the help.
