Hi David. Thanks for the insights into dataverse on this and the other threads. If you were looking into developing a standalone, web-based database application 'from scratch', what platform / programming language would you start with?  I'm  happy with Access but find distributing new versions (across many users)  to be the primary drawback of a front-end / back-end solution. I'm surprised there isn't a more seamless way to migrate VBA-based forms and  functionality to an online environment.

Ask Richard about {Access to Web}'

Yeah, I'm working on something. Not ready for prime time yet though...

Update. I finally got Dataverse working after an IT issue was resolved. Suffice it to say that the backend (Admin) side takes a bit of learning to understand how to share DV tables and the permissions process involved. Another challenge is understanding the myriad 365 licenses. My IT guy said you almost need a PHD in Microsoft licensing to understand the platform. Also, since the platform is browser based, you might have to clear your browser on occasion.

Another challenge is protecting the DV tables from 365 users. It is not clear how to let users read and write to DV tables from an Access front end but to prohibit them from altering the DV tables in the Power Apps Tables environment. Again, not sure if Dataverse is a solid solution in this regard. (On a similar note, I build a SharePoint site for a team and gave the team leader Owner rights, so they could customize their site, folder and files. They then proceeded to delete the Site Pages folder and brought down the site. I was able to restore the site. However, this is my take on how some of these SharePoint vulnerabilities are analogous to the Dataverse environment. It is probable that I am missing something here, but, the complexity of SharePoint and Dataverse admin is way too IT heavy in my view.

Regarding the question of developing a standalone, web-based DB app from scatch, I don't have expertise in that area. If you are developing for a 365 environment, instead of distributing Access from end files, Dataverse for Teams may be a solution. Building tables with forms are fairly straightforward, but I have not dug deep into the process of generating queries in a Teams DV app. Apparently, you can use Dataflows created from an Excel of SharePoint list, but I am not sure if you can create queries directly from a Dataverse table without exporting to Excel. It looks complicated and very kluge.

For the time being I am sticking with developing in Access. Hopefully, Microsoft will develop a SaaP solution more in line with Access logic.

In any case, I am looking forward to seeing what Richard comes up with.

Instead of using Dataverse and Access, I used SQL Server and Power Apps and it was a fairly smooth process.  My trial license wouldn't allow me to use Access and Dataverse so I can't shed any light on that combo, unfortunately.  

You are not the first one who experienced headaches on this, David.  Daniel Pineault at Devhut.com wrote several posts on his struggles with linking DV tables in Access.  But when I link SQL Server tables in DV, I had very little trouble.  So maybe it is the Access/DV pairing that is the headache.

Regarding a solution "more in line with Access," they've already made it -- Power Apps.  It serves the exact same role as Access, a front-end application creator for relational databases, albeit for the online world.

If you hope MS would make Access work better with DV, the odds are against that, since Access hasn't got the most love from Microsoft in the past decade or so.  The other MS development tool, Visual Studio, is now LIGHT YEARS ahead of Access, just from looking at its fancy debugger.  Word and Excel were once only for local usages just like Access, and they are now well-suited in the online world.  Access has none of that, no online connectivity, no multi-platform access.

Regarding your tech saying "you need a PhD to learn all this," that kind of talk and mentality is meaningless, and it does nothing but holding you back.  *Everything* in the tech world is hard, including the beginner lessons in Richard's videos.  Ask Richard's students what they thought when they first started, and they would say it was hard.  That's no different from you saying this is hard when you're still at the starting line.  If I told my boss that I couldn't do something because it was "hard," I would hear snickers and the job would be given to someone else (I'm hinting something at you here).

If you are holding on to DV as you seem to be doing, you are going to have to learn Power Apps eventually, or you may continue seeing headaches and hassles.  Access is not going to get any love from MS in this regard, you can count on that.

Thank you all very much for your insights into DV / PowerApp's. One question I have is: if you're relatively new to coding, and learning any new platform is a major time investment, would you be better served beefing up on JavaScript and (even more) SQL and building your own web-based database application, rather than jumping from Access to PowerApp's to DV, etc?  Is the learning curve that much steeper on the broader languages that it's worth staying within the Microsoft suite for one's SMB databases? Thanks again!

Yes, I love Microsoft, but I will agree that their licensing is incredibly difficult. Even just understanding the different flavors of the software that they have available can be difficult, and knowing which subscription type you need.

It's all one big confusing ball of "stuff."

But with regards to what I'm working on, I've already built a proof of concept. It's essentially a Microsoft Access database that can take forms and upload the form as an HTML/ASP page, and allow the user to basically just take what he's built in Access and put it right on the web with a click. I've got it connected and working with SQL Server, and I think I'm going to make a simple version that will connect directly to a back-end Access file. This way, people who don't have any SQL Server knowledge can still work with it.

But... it's still in the very early prototype stages, and I've only got it working with the most basic of Access features. But it does work. So one of these days, I'm going to post a short video of what I've got and see if there's any interest. It would eliminate the need for any kind of fancy back end or non-Access front-end work.

Hi Gordon, the short answer is that in the tech world, you have to keep learning, because the tech world keeps changing.  What's more, you'll end up using *everything* you know by the time you retire (I'm retired and I can attest to that).  You can't have the mentality of the original poster of this thread: "Oh, this is so complicated, don't waste your time."  Everything in the tech world is complicated.  No software is perfect.  Richard has a long list "Evil Access Stuff" ( https://599cd.com/blog/display-article.asp?ID=1718 ), but he would never say Access is a waste of time.

The long answer:

HTML, ASP Classic, ASP.Net, Javascript, etc., are industry standards that won't go away any time soon, so they are definitely on the "to learn" list.  BASIC was invented in the 1960s and we are still using flavors of it.  Power Apps, on the other hand, is new and not industry standard.  It only runs on Microsoft servers.  Even though it runs on the web, it is not like traditional web apps that are publicly accessible.  Only paid Power Apps users can use it in their own private environment.  So the concern is: what if Microsoft pulls the plug on this?  Some companies, like Google, have a habit of shutting down services prematurely (see here: https://killedbygoogle.com/ ).  Microsoft probably won't do that, but that is a big assumption if I'm in charge of mission-critical decisions that my boss depends on.  But it wouldn't hurt to get a free trial, which I did recently.

Microsoft has killed apps before with little to no warning. Remember Access Web Apps in 2010/2013? I stopped recording my lessons on Macros because I was planning on showing how to use those in Web Apps. I'm glad I didn't waste more time on it. Remember FrontPage Web Extensions? Even SharePoint I think might go this route.

This is one of the reasons why I'm not an early adopter of brand new tech. Yes, I'll learn it and play around with it, but I won't TEACH IT or recommend it as a viable business solution until it's been around for a while and has a wide following.

Hi Richard, that kind of changes happen fairly frequently, even to good products, because technology changes all the time, and everything has an "end of life."  You just hope the "transition" wouldn't be so disruptive that it would affect your work.  Also, sometimes "staying the same," as in the case of Access, is not a good thing.  In my career, I transitioned from dBase, to Alpha, to FoxPro, to Access, to SQL Server.  Also, when a tech is about to end (or become irrelevant or useless), there is usually handwriting on the wall.  The key to "survive" these changes is to be proactive and quit on a technology at the right time rather than letting it quit on you at an inopportune time.

The issue with Power Apps is that it resides entirely online.  If Microsoft pulled the plug, that would be it.  You really have to put a tremendous amount of trust on the publishers -- just like Kindle customers who have to trust Amazon, and those who buy digital copies of movies and games who have to trust their respective publishers.  Google's most recent disaster was Stadia, an online seller of games that lasted only 3 years.  People who bought games there lost all their games, just like that.  Google reimbursed everyone, but you wouldn't want to count on that.

Hi Kevin, thanks for the feedback. Much to digest here. I work for a non-profit organization in the human services sector that has limited resources, including a part time IT person who I quoted on the "PHD in Microsoft Licensing" thing. Non-profits, including those in the space of human services, have limited funding budgets to serve people per their mission statement. Unfortunately, many grant programs and donors don't understand how technology can be leveraged improve services and reduce costs. My point is that guys like me have limited, in some cases, very little resources to build and deploy apps, E.G., I currently don't have a budget approved for an SQL server solution. Hence the reason I am gravitating, like it or not, towards Power Platform.

Trying to learn a new language while immersed in 'getting something done now reality' is painful, to say the least. My criticism of Power Apps, and I am not alone on this, is that it, despite claims by Microsoft and beyond creating an app from a simple SharePoint list, is far from a low code/ no code platform. According to a report by AIIM, two thirds of the organizations using SharePoint report a low adoption rate due to complexity of using the platform. Yes, when I am done deploying Access apps using DV as the backend, I will dive into Power Apps - or should I say, tread into the environment.

It would be beneficial if Microsoft developed a true no code/low code platform designed for citizen developers that is easy to maintain and use. Because if they don't, some innovative company will. And I imagine the backbone will be AI. (and yes, I suspect that MS Co-pilot will begin to fill this space)

On a related note, I built a several apps for a few organizations in a collaborative cloud platform in just over a month. The app is called Kintone. Check it out.

Hi David, we can't help you with your financial hurdle, since we can only help with your "knowledge hurdle," which is the hurdle you need to overcome whether you have enough budget or not.  If you prefer Kintone, you may try convincing your company to use it.  But not everything is like Kintone, and some have steeper learning curve, and that is the reality.  If you have a good knowledge of Access, you SHOULD have no trouble learning Power Apps.  VBA is probably twice as hard as Power Fx codes.  If you know Access, it should also be not difficult to learn SQL Server (which has a free version).  The hard stuff is when you want to make web apps, which require HTML, ASP, ASP.Net, VB.Net, VBScript, and probably a few more.  Among all these options, I have to say Power Apps is actually the easiest in terms of coding and design.  But if I were your consultant, I would suggest you stay away from Power Apps because from what you've posted so far you just don't seem comfortable with the Power Platform, at least not right now, so even if you had a budget for it, you might not make the most of it.  If you are going to be the one developing the apps, you HAVE to be comfortable with the environment you use.

Kevin: Yeah, I guess you could say that's one of the good things about Access. Even if Microsoft pulled the plug on it today, you could still use it, and it wouldn't kill all the applications that have been out there for decades. Because it's software that runs locally on your computer, these web-based services that just suddenly go up and die don't do anyone any favors.

David: I have to echo what Kevin just said. As far as cost goes with SQL Server, not only do you have a free version that runs locally that you could make available online (although I wouldn't recommend it), but you can also get SQL Server hosting from someone like WinHost for about $5 a month. Every organization can afford that, and there you go, your data is now online. If people need to use it, you just give them a copy of your Access front-end, and you can build a web-based site in something like ASP relatively easily as you learn how.

Kevin, Richard, thanks for the feedback. I had a discussion with my organization about setting up and SQL server. Things move slowly here so I will continue to use Dataverse as the backend until I can transition to an SQL server. The thing that concerns me about Dataverse is protecting the tables in that environment. Not sure how to do that with our staff who all have access to Power Apps and DV Tables enviroments.

I found that developing tables natively in Access and then exporting them to Dataverse is the easiest way to work. However, I still have not found a way to export Access tables to a Dataverse Solution, nor am I sure that is even possible. '

Kevin, as for learning Power Apps & Power FX, I have not found a clear learning path to do so. Many of the videos I've seen show random examples of Power Fx but not it's underlying logic. I would like to take a video course on Power Fx but can't seem to find a comprehensive resource. Any resources that you know of would be much appreciated. And then there is the question, as Richard posits, if Power Apps will be here in the future.

I may have mentioned this before, working on a database app on a Mobile phone is not, in my view and that of my organization, an optimal user interface. Most everyone in my organization has laptops and access to 5g hot spots on their mobile phones. This is where smart phones play a role, not for data entry but to connect a laptop to our company network via 5G. This approach, in my humble opinion, renders mobile Power Apps less necessary. Yes, I have to distribute Access front end files, but that takes a fraction of the time to do compared to learning Power Apps along with Power BI/Query. We don't have the full license for Power BI, which is another reason I am sticking with Access. It is all about the queries!

In any case, I set up a free test account for Microsoft Azure SQL, but it seems complicated. How does Winhost compare? Any thoughts on that would be much appreciated.

Thanks.

Hi David, PowerFx codes comprise (mostly) of functions separated by semicolons -- Function1(a,b); Function2(c,d,e); Function3(f,g); -- structurally it is a lot simpler than VBA.  That's why I said it was twice as easy as VBA.  When I learned PowerFx, I used mostly Microsoft documentation of all the functions:     https://learn.microsoft.com/en-us/power-platform/power-fx/formula-reference  .  It's good to learn straight from the horse's mouth.  Many of the functions do similar things as their counterparts in VBA, and some even share the same names.  For instance, if you already know what DLookup() in Access does, you have a much easier time learning its counterpart in PowerFx.

The advantage of a multi-platform tool like Power Apps is that you develop an app JUST ONCE and it is usable on any device.  If you use Access, you develop apps that can only be used in Access on Windows PCs.  Later if you need web interface with SQL Server and ASP, you have to develop a SECOND time, because the coding and design are all different.  In the future if you need another platform, you'll have to develop for that platform -- a third time.  A cost-conscious company like yours needs to be aware of this.  Windows used to be the dominant platform, but not anymore.  Many cheap laptops today also don't use Windows.  Many use Chrome OS, or "stripped-down" versions of Windows that can't run some programs.

I realized this in the early 2000s when I made the move from Access to SQL Server + web interface.  The web app I made then would still be usable today, and would save us much time and money.

What exactly is "optimal user interface"?  If you mean "Windows-like" interface, I understand many people prefer that, but look around you.  Hardly any software interface you see today -- bank ATMs, restaurant kiosks, bus ticket vending machines, check-in terminals, interface on smart TV and streaming devices, etc. -- looks like Windows interface anymore.  Most people today are used to using software that does not have the "Windows-style" interface.

Hi Kevin. The not an "optimal user interface" I referred to is the small size of a mobile phone. Because of the complexity of the data they are viewing and editing, my users want larger screen sizes - desktop computers to work on - perfect for Access. I may look further into Power Apps but I have my doubts about the platform. For example, I built a Model Driven app from a Dataverse table, but, unfortunately, the platform does not allow me to rearrange / reorder the DV columns in the app. Yes, there is a tool to reorder columns in the EDIT COLUMNS tab tool, but it is vaporware. Doesn't work, which why I feel that the Power Apps platform is half backed and not fully developed. I am going to try to convince my organization to scale down there 365 Subscription and look into Kintone, which, as I may have mentioned before, is light years ahead of Microsoft Power Apps platform. In the meantime, it's good ole Access as I brush off the cobwebs since I have not used it for a few years.

To have more freedom in designing the layout, you need a canvas app, not model driven app.  Again, I agree with you that you need to pick a platform you are most comfortable with.  But it's a two-way street.  Your management may also prefer a platform of their choice for whatever reasons.  Both things can happen, which I can attest to in my own working experience.  My company let me use Access and SQL Server (which cost $600 at the time, not free), but they were also adamant in keeping an old, outdated system that I wanted to get rid of because they were "comfortable" with it.  So for 20+ years I worked with that system, maintained it, improved it, etc.  Our business thrived and we got the jobs done, and that was all that mattered.  So sometimes we don't get what we want and have to make do with whatever we have.  I wish you good luck with your company.

Hi Kevin, I have dabbled a bit Canvas apps, so hopefully I will have more time to learn after I complete my Access project. As I previously mentioned, I tried creating a Model Driven app. Unfortunately, the environment violates the basic practices of good database design. When I mentioned half baked, I was referring to the Model Driven app environment. It's pretty awful.