Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Services for AWA's for On-Premises SharePoint _ Convert A Web App Form

Posted by: supmktg Jun 7 2017, 09:37 PM

I have built a hybrid application, which uses a desktop front end for all of the heavy lifting and a relatively simple Web App for data entry by field employees. I now need to create a Web application to replace the Web entry forms that were in the AWA. My strength is VBA, but I have a little experience with an ASP app I built about 10 years ago.

I'm looking for recommendations for which of the many platforms/languages i should consider learning in order to build my new web application before April when AWA is gone. Or, is there any chance that I can export the AWA in some format that I can easily convert to something usable?

Thanks in advance,

Posted by: GroverParkGeorge Jun 8 2017, 04:56 AM

Unfortunately, no you can't directly convert the AWA interface.

There are a number of alternative technologies to evaluate, including

Look for ZOHO Creator, for one. I have a short list on my blog.

Posted by: jleach Jun 8 2017, 05:14 AM

ZOHO looks pretty good as a design tool but I'm not sure offhand how you would integrate that data with a custom SQL database.

ASP.NET Web Forms is an aged technology but tries to bring the event-driven programming model into the web world by abstracting away the HTTP layer of things and giving you a sort of "click this, do this" kind of platform. Abstracting away HTTP is a BIG abstraction, so Web Forms is hardly perfect, but it is solid enough and makes for a relatively easy break into that end of things.

ASP.NET MVC offers much more control of the HTTP end of things and leaves it up to you to really build the web application. Much better results can be had, but also a much higher learning curve.

Maybe something on the LAMP stack if you're inclined... I don't generally recommend PHP as a language for anyone to use as its so ridiculously easy to shoot yourself in the foot with it, but it is a fairly simple scripting language, not too far off from classic ASP/VBScript, if you ignore all the frameworks on it that try to fix it.

Unfortunately not a lot of really good options to migrate to.

Posted by: GroverParkGeorge Jun 8 2017, 05:32 AM

Jack's right, of course. In order to preserve your data in a SQL Server database, or in SQL Azure, you'll have to select an interface tool that can work with those databases. (My excuse is that it's 3:30 in the morning here and I have insomnia.) has articles on migrating your data from the SQL Azure db behind your AWA to a standard SQL Server/SQL Azure database and other destinations.

Posted by: RobKoelmans Jun 8 2017, 07:56 AM

Hi sup,
We're working with PowerApps for a couple of months now and it's perfect. A simple AWA-like app can be done by starting off with one of the sample templates (don't think you can only use them and not to study techniques or copy functionality). You can migrate your data to Common Data Services. If necessary you can create your own WebAPI to common data services which is much simpler compared to Asp.Net and/or MVC. If you need to keep your database as is as much as possible, we can deploy it for you. The AWA-browser part will remain to work and we can get you a WebAPI with which you can integrate your future PowerApps. But - as said - you can completely do it yourself by migrating your database to CDM. I'm quite sure MS-Office i.c. MS-Access is or will be connecting with the common data services model from Office365. You won't pay anything more than you do now providing you have Office365 already. PowerApps supports all smartphones, tablets and pc's (really well). When you get to need more complex things, you'll find out that PowerApps is extremely powerful.
Hope this helps,

Posted by: supmktg Jun 8 2017, 03:16 PM

This is a project for a company that is Microsoft-centric, so using Zoho, Quickbase, Alpha, etc isn't an option. I'm sure that they would like me to do something in PowerApps, but my experience with AWA being limited to macros and unable to do a lot of what I expected prevents me from taking that leap until it is substantially more mature and proven.

I'm hoping this corner I am in will help me expand my horizons further into web and mobile apps. I think I'm going to go with / MVC if I can learn enough - fast enough.

I appreciate all of your feedback!

Posted by: RobKoelmans Jun 9 2017, 02:40 AM

Hi Sup,
There's danger of design flaw if you take that strategy. If you use Powerapps, the need of webservices comes naturally. If you're going to be using Asp .Net/MVC, be sure you build WepAPI2, Rest, Soap or Odata compliant services first. If you have those, you still can choose for every presentation layer development environment you want. So including Asp .Net/MVC, PowerApps, Flow, LogicApps, Powershell (for devices as client, batch processing. Also think of workflow events like documents being stored on filesystems, incoming messages, etc.)
Kind regards,

Posted by: johnpdmccall Jun 26 2017, 08:21 AM

Hi Rob,

Have I got this right:
1. Power apps can do all the AWA stuff and more
2. A desktop access database can connect to Common Data Services (How?)

I'm just asking so that I don't waste my time on learning how to work with Podwer Apps if it can't do what I need.
Seem to remember previous comments on UA about a lack of relational database capability in Power Apps?

Maybe we need a forum section on UA for "Working with Power Apps"

Posted by: jleach Jun 26 2017, 08:34 AM

I'm not convinced that PowerApps can do everything AWAs did. I don't have a comparison handy, but my impression was that PA was significantly limited in terms of UI controls and functionality.

Posted by: GroverParkGeorge Jun 26 2017, 08:52 AM

I have NO confidence that PowerApps will even achieve parity with AWAs in the short term, let alone surpass them in features and functionality.

While there are some positive things to say about PowerApps, look at Rob's comments on implementation: " If you're going to be using Asp .Net/MVC, be sure you build WepAPI2, Rest, Soap or Odata compliant services first. If you have those, you still can choose for every presentation layer development environment you want. " To me, that is like saying if you build out an application, you can tack a PowerApps piece on to it, not that PowerApps are a central component in and of themselves.

If anyone knows how to connect an accdb to the'd love to hear about it. Nothing I've seen suggests that, but I might have missed it. I frankly haven't "invested" a lot of time in PowerApps.

As an Access developer, you have to wonder just how PowerApps could be considered an extension of an Access application, or as a replacement for an AWA.

Granted, AWAs require moving the data from local Access tables into SQL Azure, so the remaining nexus is primarily through the common design interface (i.e. launch MS Access 2013-2016 to develop). But with PowerApps, the first two steps are:

1) Export your data into some repository other than an accdb.
2) Close Access.

From that point forward, there's nothing "Accessy" about it.

In other words, if you decide to pursue PowerApps, you're basically adopting something other than MS Access as your primary design environment. I have no doubt that is a good strategy in many ways. I'm sure as PowerApps continue to mature, people will figure out how to take advantage of them. In the process, Access will fade out of the picture, for the most part.

Now, I will say that there is a way that you can continue to share the data between a PowerApp and an accdb. That is, of course, to put the data into either the CDS or into a server database, such as SQL Azure or even a remotely hosted SQL Server instance. In some ways, I think, that makes sense to do. You have one datasource that is consumed in multiple ways. Within the limited interface capabilities of the PowerApp, that's viable.

And, of course, as Rob has pointed out, if you want to step over and work on the .net side, they can be an additional arrow in that quiver of tools as well.

Posted by: johnpdmccall Jun 26 2017, 10:38 AM

Yep, I've been faffing about with Power Apps for a few days and can't see how an app can be put together and use a relational database. Maybe I'm just missing it.
Ideally I'd like to use the same data either in common data services or in a SQL to connect to a accdb front end and a Power App but don't want to invest time learning until I'm sure that Power Apps can be used to build a basic phone or tablet App that will work with related data.
So far I've only been able to build an app that handles data from a single table very nicely but I can't get anything else.
If someone can tell me it can be done then I'm happy to put in whatever effort it takes.

Maybe .net is the way to go.

Posted by: RobKoelmans Jun 27 2017, 03:59 AM

Hi Sup,
We have a strategy for offline documents/checklists in PowerApps. To maintain those documents, we're building a simple database app with CDM at this moment. A coworker is looking into test-driving those apps on O365. If you want to be using tablets a.t.l. with all their resources (camera/graphs/gps/recorder or want to use IOS/Android/WindowsMobile devices anyway) .Net development will require a huge investment in skills and production.

What we do for the simple database, is create an app for one cdm-table. Then you copy the three screens that have been generated, attach another table with the cdm-connecter (that gets a relationship with the first), apply the three replica sheets on that table, have a extra navigate button in the galery of the detail sheet of the first and filter on the first-galerey.selected in the replica sheet. For drill down navigation, you parse a variable in the navigation that has been set to the galery-object of the sheet you navigate from. Use that variable with the .default property to land on the proper record. If you've done it a couple of times, the next one will only take you a couple of minutes. Have a look on the in depth look from greg lindhorst in the Powerapp blog for more information on PowerApps in general.

Can you describe your AWA a little (how many records etc), so I can estimate whether it can be done in PowerApps? We're also looking in getting our custom connectors to SQL-Server registered. They work along with OnCreate/Update/Delete triggers that you may have in your AWA-database.
Hope this helps.

Posted by: supmktg Jul 6 2017, 11:10 AM

Hi Rob,

The AWA is a tiny but integral portion of a complex hybrid application. The backend SQL db is pretty large with many related tables. The AWA is used by remote employees to enter requests which are entered into a temp table for later processing using the Access desktop portion of the app. The AWA looks up records in 3 or 4 tables and then ultimately writes the selected/entered data to the temp table. The AWA also currently writes to a login table and creates an audit trail to track responses to notification emails sent by the AWA that entries have been made. Though the AWA has only one main page and a couple of pop ups, there are quite a few macros involved.


Posted by: RobKoelmans Jul 6 2017, 11:15 PM

Hi all,
Sorry for responding so late. For some reason I missed there were new responses on this discussion thread. I'm not saying PowerApps is a good alternative to AWA but it does complement AWA quite well. We have no intention of quitting with AWA and were forced to go off O365 with AWA's anyway because of several technical issues that we had and Microsoft refused to help us with. CDM is totally stupid. You can't use nested tables with CDM. Furthermore, you have two kinds of table relationships that should have been one. Being two (relationship and picklist), you can hardly use either of them becaulse you always need a lookup as a one-to-many in the opposite direction.

The nested tables in PowerApps would have been very nice, be it that literally always (even when you assign a collection to a gallery object) replicates data. The whole concept of referencing to a variable, control or collection does not exist (except when you assign a unnested collection to a gallery object, but you never want to do that), you have to reassign everything to update something that made use of data. You can make this happen automatically because of the Excel like event-driven structure of PowerApps. RAM-usage can totally explode because of this. There is no scalability whatsoever. Nested tables are crippled because of this missing referencing. You can only use childtables that contain references on the developer level (you have childtables contain a reference value of which you know to what other collection it refers. Nested tables are perfect for downstream data from WebAPI calls to an AWA-database (having datamacros in there that produce JSON-strings).

The fact remains, that PowerApps enables you offline (don't ask how) usage of smartphones and tablets and that it integrates camera, graphing, recordings, gps, gyroscope, (pdf) documents with AWA-databases. Also, you can use AWA-datamacros by invoking them in onCreate triggers from tables that consist of a column for each datamacro parameter (this doesn't work with the standard SQL-Server connector). This support of media and documents is extremely good due to tight integration with onedrive that serves as connectionless distributed cache.

At first I thought PowerApps was nothing, a second look made me think it's quite neat but when you start working things out, you discover some totally bizarre aspects from PowerApps. Just create a collection with 300, 500 bytes in there and ram-usage goes from 350 to 420 MegaBytes and that's in an almost empty app. PowerApps Studio gets extremely slow at times, takes more than a GigaByte of RAM in minutes and crashes if things take too long. What on earth happened to Microsoft if you compare this with the first versions of MS-Access? Anyway, we can use it to replace lacking continued support and new versons of MS-Access AWA and MIcrosoft is still the only company I dare to invest my time and money in.

Posted by: RobKoelmans Jul 7 2017, 06:55 AM

Hi Sup,
Regarding your application, I think the AWA-part can be done quite well in PowerApps. You can even use the CDM for that. I haven't checked yet but I'm quite sure you can modify your existing SQL database using CDM by use of two connectors in LogicApps or even Flow.
Kind regards,

Posted by: RobKoelmans Jul 8 2017, 05:51 AM

In addition, I just learned Flow has a far better connector to SQL-Server than PowerApps has. It even supports stored procedures. So Flow is the way to go between PowerApps and SQL-Server. I haven't tested yet.

Posted by: RobKoelmans Jul 8 2017, 08:00 AM

The nice thing about combining AWA's and PowerApps through a WebAPI is that the .Net part of development can remain simple and onetime only. You just have it put a request message on a parser table, have a trigger using other datamacros create a response message. You only have to write a tally tabled ForEach function that produces very simple and straightforward JSON. That's coming in as nested collections that you can assign directly to cascading listboxes, dropdown boxes and galleries in PowerApps.

The WebAPI we created, does a lot more than this. The nested table collections that are being parsed by the PowerApp with the WebAPI call, are coming in as JSON data objects in C#. Those are converted to XML and parsed to a service that disassembles the XML into strings with length- and depth-values in front of each tag/value/attribute string part. This way, we don't need nested iterations in the AWA-trigger (but a single depth tally iteration that puts each element part onto a helper table with a trigger that interprets each element on behalf of the response message build up).

If ever we create a simple version, I'd be happy to make it available to everyone if people can use that. Unfortunately, we don't have much experience with NuGet, CodeProject a.t.l. but we can learn.

Posted by: GroverParkGeorge Jul 8 2017, 10:01 AM


I don't doubt there is a whole host of opportunities in what you are now exploring.

Unfortunately, other than "AWA" and "Data Macro", and probably to some extent "tally table" and "ForEach" function, there just isn't much there that will seem familiar to the Access developer. And that's the problem, as I see it.

We are no longer talking about tools for Access-related projects, are we? To me, it seems like we're talking about a different kind of development. One that offers a lot of promise for those wishing to go in that direction. But the goal of a direct way for desktop Access developers to expand their environment just no longer exists with the coming demise of the AWA experience for Office 365. Those who are fortunate enough to have an available SharePoint installation on site or available as a hosted site can, indeed, step onto that development path. For the majority of us, unfortunately, that's no longer a viable path forward. Microsoft's vision for PowerApps simply doesn't include MS Access databases. For the "typical" Access developer, it's an interesting but largely irrelevant choice.

I've done a small amount of work with C#, JSON and even some small amount of research around WebAPIs. I think I understand the concepts you're suggesting, but I can't see how I'd incorporate them into any one of my existing Access or Access/SQL Server projects at the moment. And that's the really discouraging aspect of it all. Unless we're willing to step away from Access or Access/SQL Server as our primary tool, we're not going to be able to follow you. Perhaps that's looking too much backwards instead of forwards, but that's how it seems to me.

That's how I see it at the moment. I can be convinced otherwise by future developments.

Posted by: RobKoelmans Jul 9 2017, 04:36 AM

Hi George,
I see AWA's as having 3-tier development combined really (although that's impossible by definition). A physical datamodel in SQL-server, an information model/business layer in (stored) procedures/user defined functions/views in SQL-server and - thirdly - a not so good presentation layer in webbrowsers. With PowerApps/Flow we get another presentation layer development environment to AWA.

The other way I look at AWA's is sort of a desktop application in the cloud (which as also defying its own definition a bit) just as Microsoft did with Outlook Web Access and Office Web Apps. Not making AWA part of Office Online (Outlook Online, Word Online, Excel Online) in Februari 2014 isolated AWA in the organization. If it had been Access Online (with also a web version of the Access Development Environment) it would have been seen as useful. They likely wouldn't have killed it off because PowerApps wouldn't have been regarded as a replacement (which it isn't at all). PowerApps benefit would benefit enormously from Access Online if that had gotten a proper service layer that you could use just as easily from ASP .Net/MVC webbrowser applications as from the auto-generated browser application it now partly is.

We intend to continue with AWA's even more so because of PowerApps/Flow. So in my view, we're talking about access related projects exclusively. PowerApps only provide support for the smartphone/tablet which is completely lacking in the presentation layer part of AWA. What triggered all this, was perhaps that AWA's left a trail of unused databases in SQL-Azure that couldn't be cleaned out safely.

I think it would be quite simple nowadays for Microsoft to create an online version of MS-Access development environment. They can't permit themselves to kill off Access Desktop anyway.
Kind regards,

Posted by: IdaHodgson May 14 2019, 12:49 PM

How much time did you spend?

Posted by: RobKoelmans May 24 2019, 12:22 PM

A lot. But it pays off well. We have a dozen or so organizations on our platform and they come up with things they did for themselves and things they want us to do for them.