Web Forms with SQL Azure back-end and Access front-end (1 Viewer)

TJ01

New member
Local time
Tomorrow, 08:44
Joined
Sep 7, 2020
Messages
14
We are looking to build a solution that will allow us to capture data from online forms and to sync data both ways, more or less directly, to a database back-end.

The forms have to be web-based, with little or no coding, and the db preferably SQL Server on Azure.

It also has to be able to be white-labelled.

We have looked at a lot of no-code forms tools, like Jotform, but they don't have database connectivity, without using a connectivity tool such as Zapier.

I have setup a small SQL Azure db with ODBC connection through linked tables to Access front-end. Now I just need to tool do develop the forms.

So my question is, does anyone here have experience with this and can point me to the right web forms tool for doing this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:44
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you looked into Power Apps?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:44
Joined
Feb 19, 2013
Messages
16,553
not my area of expertise but suspect it really depends on what the web form is actually required to do in terms of syncing, form design, data validation requirements and who the user base is.

If jotform works with zapier, why not just use that?

And here is a list of similar products
 

TJ01

New member
Local time
Tomorrow, 08:44
Joined
Sep 7, 2020
Messages
14
Thanks for your reply. The forms are fairly straightforward questionnaires with a bit of branch logic where a certain response may lead to other questions. This is easy enough to do with Jotform and other RAD forms tools.

The issue with JF and others is that you can't dynamically populate a list of options (e.g. a changing list of employees) from a database table.

The Zapier/other tools certainly work to push the form responses to a back-end but we want to be less reliant on 3rd-party tools in packaging our solution.
 

TJ01

New member
Local time
Tomorrow, 08:44
Joined
Sep 7, 2020
Messages
14
Hi. Have you looked into Power Apps?
Yes I've used PowerApps and yes they could do the job but I want to be able to package a white-labelled solution and I don't think we can do that with PowerApps.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:44
Joined
Oct 29, 2018
Messages
21,358
Yes I've used PowerApps and yes they could do the job but I want to be able to package a white-labelled solution and I don't think we can do that with PowerApps.
Sorry, what's a "white-labelled solution?"
 

TJ01

New member
Local time
Tomorrow, 08:44
Joined
Sep 7, 2020
Messages
14
By "white-label" I mean we want to be able to package it with our branding, or our customer's, i.e. not Microsoft's or anyone else's. Some of these tools, e.g. Jotforms, allow for that but I doubt that MS would! I'll look into it anyway.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:44
Joined
Oct 29, 2018
Messages
21,358
By "white-label" I mean we want to be able to package it with our branding, or our customer's, i.e. not Microsoft's or anyone else's. Some of these tools, e.g. Jotforms, allow for that but I doubt that MS would! I'll look into it anyway.
Okay, thanks for the clarification. Please let us know what you find out. Good luck!

PS. I did a quick search and maybe this could be relevant?
 

TJ01

New member
Local time
Tomorrow, 08:44
Joined
Sep 7, 2020
Messages
14
Yes but from what I remember, PA users have to be licensed with MS and I'm sure MS would want to keep it that way too. That suggests that PA apps are designed for use within an enterprise rather than for commercial, non-enterprise.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:44
Joined
Oct 29, 2018
Messages
21,358
Yes but from what I remember, PA users have to be licensed with MS and I'm sure MS would want to keep it that way too. That suggests that PA apps are designed for use within an enterprise rather than for commercial, non-enterprise.
I see. Best of luck with your search. Please let us know what you eventually decide to use.
 

TJ01

New member
Local time
Tomorrow, 08:44
Joined
Sep 7, 2020
Messages
14
Will do. Thanks for your input.
 

TJ01

New member
Local time
Tomorrow, 08:44
Joined
Sep 7, 2020
Messages
14
Hey gang, 3 months later I thought I'd come back and share my experiences with this little project. Just to remind you, I was basically looking for a low-code/no-code solution that would work with a web front-end, to have users fill in forms through their browser and to have data syncing back-and-forth, as directly as possible, to a SQL database, preferably SQL Server. So this would be a de facto replacement for Access on the Web.

A couple of other restrictions: users would be scattered far and wide and not only be part of one enterprise and I wanted to keep it self-contained as much as possible, meaning not having to use any connector tools such as Zapier, Automate.io, REST API etc. And it had to be able to be white-labelled so that my client could package up their application to sell it under their own brand. And we needed to host it locally in Australia, either on-prem or on a local cloud hosting platform (due to privacy regulations), and preferably with local support within Australia. And of course it had to be as cheap as possible, especially during the start-up stage for my client.

I trialled a whole bunch of packaged solutions, which I will list here:
- Microsoft Forms - was too basic and required an MS license to develop
- Microsoft PowerApps - much more complex than Forms but requires a license for each user and an app to run
- Azure logic apps - was recommended by a MS partner dev house that I knew and it has built-in syncing with Azure SQL DB but the licensing and subscription costs would add up and possibly price me out
- JotForm - probably the most popular webforms solution, no code, with many APIs and sync options BUT no direct syncing to a SQL database (otherwise this would have been my choice)
- Appenate - another web-forms tool, similar to Jotform. Very powerful, lots of features and very good local support. But users had to install an app to run the forms so it's not available simply through a browser.
- Snapforms - similar to JotForms, Australian and browser-based but SQL connection was going to be expensive to setup
- Flowfinity - again very powerful, they said they could provide 2-way database syncing but could not show me an example of such a scenario
- Frevvo - very good product, had local support, no support for off-line forms which could be an issue in low-coverage areas, otherwise probably would have been my choice
- Alpha Anywhere and Alpha Transform - again a lot of power but quite expensive
- RackForms - a nice package with a complex but feature-rich IDE, built-in syncing to SQL Server or MySQL and VERY affordable. This is a low-code, very powerful tool that provides a lot or wizards and builders, and generates the PHP-coded pages for you. This was the solution that we chose.

(I also researched a lot of other options, including Wordpress with various plug-ins, ADOBE forms, FastField, Formstack, Paperform, Outsystems, Pabbly, Pronto Forms, Typeforms, Wufoo, Zoho but none of them ticked all the boxes for various reasons.)

The best features of RackForms are:
- integration with SQL Server and MySQL and a few others (including MS Access via ODBC) - I found their solution to this to be far above any others that I tried so this was the one killer feature. I decided on the MySQL back-end, at least for the trial, as it is free. But I could migrate to SQL Server later if I have the need.
- standard PHP scripting
- you can white-label a solution quite easily
- plenty of sample applications and a lot of documentation, although some of this is incomplete, also a lot of video tutorials
- although there are no support forums and I've only ever had contact with the chief developer/owner (Matt), he is very responsive and always got back to me within 24 hours, and his usual method of answering questions is to upload a YouTube video specifically addressing those questions. I also had a couple of video calls with him and he was very responsive and helpful.
- it is extremely cheap for what you get. They have 2 purchase options - either cloud-hosted by them at a cost of $USD12 per month and only $5 for the first month, which makes it very affordable to trial; or a self-hosted version for USD$110 once-off for life, for 2 domains, including all upgrades! This is almost unbelievably affordable.

The drawbacks are:
- a steep learning curve, with a lot of fields, attributes and features to learn (this would be easier if you already know some PHP)
- it really does help to know PHP, especially the debugging process. I am still learning this and it's a struggle, but again Matt has been very helpful with this.
- it also helps to know JavaScript as you can do a lot more customisation with this
- again reliance on one person for support and the timezone issue (especially from Australia) but again, I have to stress that Matt is very helpful and will hopefully stick around for eternity!

As far as I could find, there is definitely not a direct migration path from Access to Web/PHP. There is nuBuilder, which is an open source tool for developing web-based database apps but I found it to be an incomplete product with little documentation.

I've created a few forms now on the cloud-hosted platform and it has been going well, although the learning curve has meant it has taken much longer than I had hoped. But I now feel that I have what I want and I'm in the process now of migrating to self-hosted. This in itself has been a challenge as I have had to find a LAMP stack developer to get the Linux and MySQL servers configured and it's proven difficult find people who are willing to do such a small project and are located within Australia, as most of the work is done off-shore, I guess to keep the labour costs down.

Anyway, if you have read this far, I'm guessing that you are probably facing similar challenges to me so I hope this has helped. Definitely let me know in the comments if this information does help or if you have found any other options that do the job!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:44
Joined
Oct 29, 2018
Messages
21,358
Hi. Thanks for the update. Good luck with your project.
 

zeroaccess

Active member
Local time
Today, 16:44
Joined
Jan 30, 2020
Messages
671
@TJ01 thank you for the update. I am going through this process myself per my recent post: https://www.access-programmers.co.uk/forums/threads/is-access-the-right-solution.315123/

So if I can change a couple of variables:
  1. If you were not willing or able to spend the time to learn PHP, and
    1. If you do not mind installing an app and had a MS 365 license, would you recommend PowerApps?
    2. If you do not mind installing an app but did not have a MS 365 license, would you recommend getting one or choosing another solution?
    3. If you prefer a browser-based solution, which would you recommend looking at?
Thanks!
 

zeroaccess

Active member
Local time
Today, 16:44
Joined
Jan 30, 2020
Messages
671
Hey gang, 3 months later I thought I'd come back and share my experiences with this little project. Just to remind you, I was basically looking for a low-code/no-code solution that would work with a web front-end, to have users fill in forms through their browser and to have data syncing back-and-forth, as directly as possible, to a SQL database, preferably SQL Server. So this would be a de facto replacement for Access on the Web.
Please do share your thoughts on the above question. Thanks.
 

TJ01

New member
Local time
Tomorrow, 08:44
Joined
Sep 7, 2020
Messages
14
Hi zeroaccess

Sorry for taking so long to reply but I was away on holiday when you posted and I've only just checked back in here today. I'm also not sure why I didn't get an email notification that you had replied here, which I had assumed I would get.

Have you settled on a solution yet?

Anyway, I've read through your other post and to answer your questions:

1. I am no expert in PowerApps but in my previous job I did use it to build a couple of apps that collected data and pushed them through to SQL Server, as well as doing lookups to the same SQL db. I would say if you have MS licensing then PA is the place to start because it's good to stay within the MS framework as much as possible and it is relatively quick to learn and is powerful. Just to clarify thought, I think you need to purchase extra licenses per user @ a few dollars per month, on top of any M365 licenses.

2. Again with my MS background I would probably stick with the PA and buy the licenses. I'm sure a lot of people would disagree if they have experience with PHP or other languages that lend themselves to web or mobile dev.

3. From my research I don't think you will find a worthy browser-based solution that is not based on PHP. You can do a certain amount with products like RackForms without knowing a lot of PHP but it certainly helps to learn some, or to have a good resource that you can lean on to help you along the way. The next best option would be Frevvo but I didn't do enough with it to know how you would go without that PHP knowledge.
 

Reshmi mohankumar

Registered User.
Local time
Tomorrow, 03:14
Joined
Dec 5, 2017
Messages
101
Hey gang, 3 months later I thought I'd come back and share my experiences with this little project. Just to remind you, I was basically looking for a low-code/no-code solution that would work with a web front-end, to have users fill in forms through their browser and to have data syncing back-and-forth, as directly as possible, to a SQL database, preferably SQL Server. So this would be a de facto replacement for Access on the Web.

A couple of other restrictions: users would be scattered far and wide and not only be part of one enterprise and I wanted to keep it self-contained as much as possible, meaning not having to use any connector tools such as Zapier, Automate.io, REST API etc. And it had to be able to be white-labelled so that my client could package up their application to sell it under their own brand. And we needed to host it locally in Australia, either on-prem or on a local cloud hosting platform (due to privacy regulations), and preferably with local support within Australia. And of course it had to be as cheap as possible, especially during the start-up stage for my client.

I trialled a whole bunch of packaged solutions, which I will list here:
- Microsoft Forms - was too basic and required an MS license to develop
- Microsoft PowerApps - much more complex than Forms but requires a license for each user and an app to run
- Azure logic apps - was recommended by a MS partner dev house that I knew and it has built-in syncing with Azure SQL DB but the licensing and subscription costs would add up and possibly price me out
- JotForm - probably the most popular webforms solution, no code, with many APIs and sync options BUT no direct syncing to a SQL database (otherwise this would have been my choice)
- Appenate - another web-forms tool, similar to Jotform. Very powerful, lots of features and very good local support. But users had to install an app to run the forms so it's not available simply through a browser.
- Snapforms - similar to JotForms, Australian and browser-based but SQL connection was going to be expensive to setup
- Flowfinity - again very powerful, they said they could provide 2-way database syncing but could not show me an example of such a scenario
- Frevvo - very good product, had local support, no support for off-line forms which could be an issue in low-coverage areas, otherwise probably would have been my choice
- Alpha Anywhere and Alpha Transform - again a lot of power but quite expensive
- RackForms - a nice package with a complex but feature-rich IDE, built-in syncing to SQL Server or MySQL and VERY affordable. This is a low-code, very powerful tool that provides a lot or wizards and builders, and generates the PHP-coded pages for you. This was the solution that we chose.

(I also researched a lot of other options, including Wordpress with various plug-ins, ADOBE forms, FastField, Formstack, Paperform, Outsystems, Pabbly, Pronto Forms, Typeforms, Wufoo, Zoho but none of them ticked all the boxes for various reasons.)

The best features of RackForms are:
- integration with SQL Server and MySQL and a few others (including MS Access via ODBC) - I found their solution to this to be far above any others that I tried so this was the one killer feature. I decided on the MySQL back-end, at least for the trial, as it is free. But I could migrate to SQL Server later if I have the need.
- standard PHP scripting
- you can white-label a solution quite easily
- plenty of sample applications and a lot of documentation, although some of this is incomplete, also a lot of video tutorials
- although there are no support forums and I've only ever had contact with the chief developer/owner (Matt), he is very responsive and always got back to me within 24 hours, and his usual method of answering questions is to upload a YouTube video specifically addressing those questions. I also had a couple of video calls with him and he was very responsive and helpful.
- it is extremely cheap for what you get. They have 2 purchase options - either cloud-hosted by them at a cost of $USD12 per month and only $5 for the first month, which makes it very affordable to trial; or a self-hosted version for USD$110 once-off for life, for 2 domains, including all upgrades! This is almost unbelievably affordable.

The drawbacks are:
- a steep learning curve, with a lot of fields, attributes and features to learn (this would be easier if you already know some PHP)
- it really does help to know PHP, especially the debugging process. I am still learning this and it's a struggle, but again Matt has been very helpful with this.
- it also helps to know JavaScript as you can do a lot more customisation with this
- again reliance on one person for support and the timezone issue (especially from Australia) but again, I have to stress that Matt is very helpful and will hopefully stick around for eternity!

As far as I could find, there is definitely not a direct migration path from Access to Web/PHP. There is nuBuilder, which is an open source tool for developing web-based database apps but I found it to be an incomplete product with little documentation.

I've created a few forms now on the cloud-hosted platform and it has been going well, although the learning curve has meant it has taken much longer than I had hoped. But I now feel that I have what I want and I'm in the process now of migrating to self-hosted. This in itself has been a challenge as I have had to find a LAMP stack developer to get the Linux and MySQL servers configured and it's proven difficult find people who are willing to do such a small project and are located within Australia, as most of the work is done off-shore, I guess to keep the labour costs down.

Anyway, if you have read this far, I'm guessing that you are probably facing similar challenges to me so I hope this has helped. Definitely let me know in the comments if this information does help or if you have found any other options that do the job!
As like you , me also using PHP sites(im also learing) for client/customers queries. directly they can check their order status online without second sync to any other location from actual DB. Data posting in to MS access which is in intranet and a local hosted system has PHP server with ODBC connection. Working fine. But needs much expertise(Self) to implement more.
 

zeroaccess

Active member
Local time
Today, 16:44
Joined
Jan 30, 2020
Messages
671
- Microsoft PowerApps - much more complex than Forms but requires a license for each user and an app to run
To circle back on this -- this is included with a MS 365 Business plan (pricing is per user), so it's a much better deal than subscribing a la carte. Also, while you can build a standalone app, you can also put it right in Teams if you're using that:


This is some really impressive stuff MS has created.

And us Access developers will see some familiar things in the app builder.
 

Users who are viewing this thread

Top Bottom