Cloud-based solutions (1 Viewer)

[Kayleigh]

Hi

I work on a team of software developers who specialise in MS Access database development with use of SQL Server.

We have noticed that it seems to be the expectation of current clients to have a fully hybrid solution which can be accessed on mobile/web apps seamlessly.
We are looking for a platform which is similar to the MS Access software to build complex applications without requiring code from scratch. Possibly with a SQL Server back end - as it is quite robust.

If anyone can share their thoughts on this topic and any experience in the field would be valuable.

Many thanks

 

[GPGeorge]

Hi

I work on a team of software developers who specialise in MS Access database development with use of SQL Server.

We have noticed that it seems to be the expectation of current clients to have a fully hybrid solution which can be accessed on mobile/web apps seamlessly.
We are looking for a platform which is similar to the MS Access software to build complex applications without requiring code from scratch. Possibly with a SQL Server back end - as it is quite robust.

If anyone can share their thoughts on this topic and any experience in the field would be valuable.

Many thanks

PowerApps might be one possibility. And watch this video here. And this playlist here.

The key to this approach is that the "cloud" side of the application need not be the full, robust production application you have in the Access application running on the desktop. Instead, you can implement a subset of functions in the cloud side of the hybrid; that subset would be only those functions (input from a remote location via a smart phone, for example, or live information on inventory status for sales reps on the road) which are more effective when used remotely.

 

[Lightwave]

For the last three years I have been using something called PHPRunner
Their website is here... Xlinesoft website .. they have a asp.net version and a php version - I have used both but preferred the php version.
If you are good with MS Access you will be good with this. Generated applications are responsive and work well on mobile phones
It certainly connects to MySQL , SQL Server and Postgres - I think it might connect to MS Access as well although I would test that before taking my word for it. I've been using it with MS Azure - Azure SQL connected to PHP Runner web app on Azure Web Service which works well. I also host an application using MySQL on a Linux Shared Hosting plan and that works just as well.. The Linux shared hosting plan is with InMotion.

Review on my personal blog

And here are reviews from others
PHP Runner reviews

It is a php generator and you create the application in a program and then build it / once built you publish it up to a server..

Unlike a lot of php generators they don't charge on a per user seat basis and the generated code can be edited in the raw and is straight PHP. You are free to publish it wherever you wish and it can serve an infinite number of users.

It connects well to SQL Server
Other good candidates are Scriptcase / PHPMaker / Radzen and Nubuilder The first three will connect to SQL Server I believe.
Another one I heard about but have no experience of is... PHPRad (I really have no experience with this one but seems very cheap)

These were my main considered options when looking at something similar - all except Nubuilder are responsive..
My initial observations...
Scriptcase has some kind of security on generated code so I didn't go for it..
PHP Maker doesn't allow you to have multi column forms which I found frustrating so didn't go with it..
Radzen is good but its ASP.NET and I have a linux shared hosting so wanted to keep to PHP
Nubuilder is limited to MySQL and it is not responsive but it is open source and so is completely free. I did get a basic system up and running on that but I needed responsiveness. It is definitely the outlier as its self contained in a mysql database which is quite cool. The non responsive nature does mean you can make truly complicated forms as well which coming from MS Access was appealing to me but in the end I needed to be able to connect to a greater variety of databases.

p.s. I am a fully paying unsponsored user of PHPRunner

Loads of how to videos from a user on PHPRunner here I expect he gets some support from PHPRunner
PHPRunner how to videos

 

[Pat Hartman]

We have noticed that it seems to be the expectation of current clients to have a fully hybrid solution which can be accessed on mobile/web apps seamlessly.

As others have said, it is rare that all users need all functionality to be usable on all platforms. Therefore, you need to figure out what you can support with Access assuming you want to stick with it and what you can't.

Once you have separated what functional pieces need to operate on which platforms, you have a better chance at making an efficient solution. It makes no sense to try to make all forms fit on a phone. That's just silly. Very little actual work can be done on a phone. You might want to be able to find small but quick answers on a phone such as a customer address and contact number or maybe his outstanding balance but trying to enter an order will be very frustrating. Even tablets have limited usefulness. Their screens are bigger but again, they don't have an actual keypad which makes data entry very difficult. So, they might need the "quick answers" app but they can also see more complex reports.

Occasionally, the problem will be that the customer wants to support off-site users. This was huge during COVID and many companies came up with emergency solutions but the best solution is Citrix or possibly Remote Desktop. Both can be hosted by the client which minimizes the cost. Seat licenses for RD are free if the OS is Windows Pro. Otherwise RD also requires seat licenses. The advantage of both solutions is that your Access FE and whatever BE you are using can run in a browser with essentially NO changes to either the FE or BE. The solution is very fast and frequently users report quicker response time than when they were on the LAN. That's because the Access FE and BE (when jet/ace) are both installed on the same server as Citrix or RD and so there is no lag that is typical even in a fast LAN. Usually if the BE is SQL Server, it will stay on the SQL Server so you will see the normal LAN response. Even though the Access FE will be inappropriate, you can create separate ones that include only the functionality needed by the phone or notepad users. They can have different forms designed to fit in smaller places. So, technically, you could support everyone with the same Access app with a little tweeks if you go with Citrix or RD because they both run in a browser.

 

[Cotswold]

Take a look at B4x.com - anywhere software. It is a version of basic and supports Android, Windows, IOS & Raspberry. Some versions are free. It does create a huge runtime copy and it is not as secure as Access with regard to code security. But nonetheless, it does everything you want without spending thousands a year on a development environment to replace Access.

It appears to have good support and a decent Forum. Not sure about its long term prospects or viability as a company. Worth a look though

 

[GPGeorge]

Spoiler: etc

As others have said, it is rare that all users need all functionality to be usable on all platforms. Therefore, you need to figure out what you can support with Access assuming you want to stick with it and what you can't.

Once you have separated what functional pieces need to operate on which platforms, you have a better chance at making an efficient solution. It makes no sense to try to make all forms fit on a phone. That's just silly.

Very little actual work can be done on a phone. You might want to be able to find small but quick answers on a phone such as a customer address and contact number or maybe his outstanding balance but trying to enter an order will be very frustrating. Even tablets have limited usefulness. Their screens are bigger but again, they don't have an actual keypad which makes data entry very difficult. So, they might need the "quick answers" app but they can also see more complex reports.

Spoiler: etc

Occasionally, the problem will be that the customer wants to support off-site users. This was huge during COVID and many companies came up with emergency solutions but the best solution is Citrix or possibly Remote Desktop. Both can be hosted by the client which minimizes the cost. Seat licenses for RD are free if the OS is Windows Pro. Otherwise RD also requires seat licenses. The advantage of both solutions is that your Access FE and whatever BE you are using can run in a browser with essentially NO changes to either the FE or BE. The solution is very fast and frequently users report quicker response time than when they were on the LAN. That's because the Access FE and BE (when jet/ace) are both installed on the same server as Citrix or RD and so there is no lag that is typical even in a fast LAN. Usually if the BE is SQL Server, it will stay on the SQL Server so you will see the normal LAN response. Even though the Access FE will be inappropriate, you can create separate ones that include only the functionality needed by the phone or notepad users. They can have different forms designed to fit in smaller places. So, technically, you could support everyone with the same Access app with a little tweeks if you go with Citrix or RD because they both run in a browser.

Here's an example of what Pat is describing. These are two screens from a PowerApps application, displayed in Landscape mode. Imagne working on the top form on a screen the size of an iPhone screen. The bottom screen is better, but it only does one thing: scans a barcode on a book and retrieves data about that book from the Google Books API. There is a button to save the retrieved data, which is only visible after the data is successfully retrieved.

Imagine having to work on the full input (top screen) cataloging 50 books! Scanning and pushing the "Save" button 50 times is actually not that difficult, but on an iPhone screen, it's not all that easy either. If you are in a warehouse, scanning books from a shipment of used books for the catalog, which would you prefer to use, knowing that once you get back to a desk with a computer connected to the internet, you can modify details about the book on a full-sized Access form, if necessary to complete the cataloging.

 

[Pat Hartman]

I have one client who uses scanners to record shipments. They go into a file. Then they have to upload the file when they can plug the scanner into the PC or if the bluetooth can connect, it can go in directly. Since that is variable due to the size of the warehouse, they prefer the consistency of using the batch file.. Scanners are cheaper than even the cheapest phones. Another client has a PC set up in receiving and so scans directly into the Access app.

 

[GPGeorge]

I have one client who uses scanners to record shipments. They go into a file. Then they have to upload the file when they can plug the scanner into the PC or if the bluetooth can connect, it can go in directly. Since that is variable due to the size of the warehouse, they prefer the consistency of using the batch file.. Scanners are cheaper than even the cheapest phones. Another client has a PC set up in receiving and so scans directly into the Access app.

True, but if you have a phone, and most people do these days, there's no need to also buy a dedicated barcode scanner for that single purpose in that single location. You can install the app on the phone and use it with or without the availability of a dedicated bar code scanner. (Phones are probably easier to carry around than scanners, too, but that's a minor issue.)

Again, there is no need to put a PC in the receiving area, dedicated to that single purpose.

Realistically, it's more a matter of meeting clients' needs than pushing a particular approach, though.

 

[Pat Hartman]

So, are you OK with your company making you use your personal phone for work

A dedicated PC in receiving is cheaper than the cost of a single phone these days

 

[561414]

I will advice a strategy to learn web development instead of adapting to a new platform which will also require learning.

The stack: MERN
MySQL, the library for database functions
Express, the library for the API
React, the library for the frontend
NodeJS, the technology to work with the previous three

The work load:
Person 1 codes de backend with MySQL and Express
Person 2 codes de frontend with React
Person 3 in charge of making things live

The tools:
VSCode + Github Copilot for code (all members)
NodeJS runtime (all members)
phpmyadmin for database design (person 1)
Access for query building (person 1)

What comes first, the chicken or the egg? the answer is both. All members start right away, here's how:
Person 1 (backend) starts creating servers and some basic routes with express. Then advances installing some middleware and finally doing some queries to the database using the mysql library. In a very short amount of time, he will have a working api that will fetch data from a database on demand using the routes and returning JSON objects that will be read by the frontend.

Person 2 (frontend) starts with create-react-app, then installs and uses a component library like antd (super easy and well documented to avoid learning advanced html and css), then learns some react-router-dom and finally does some fetching from the api built by person 1. If said api is not ready yet, he can use a public api to test before the real api is ready. In a very short amount of time, the frontend will be able to receive data from the server and present it properly.

Person 3 (deployer) starts getting some domain names, sets them up and checks hosting servers that allow installing NodeJS. Then downloads projects and deploys them to these servers, deploying an api is similar to deploying the frontend, but will have to learn the differences. Basically, the api needs to be run from the server and depends of a few packages to remain live while the frontend just has to be uploaded. This guy will also check how to work with linux, to run things. Then after things are live, he will learn how to protect the server via SSH, how to remove the root user, how to work with the firewall, how to configure fail2ban, how to configure some mysql modes, automatic backup and how to automate the uploading. Since this is the easiest task, he should also learn Git and instruct the other team members.

Divide the tasks, and you will be providing web applications very shortly, in a matter of days.

 

[Pat Hartman]

I would never say that Access is the answer to all development requirements but you sure get a big bang for your buck. Given what I know that projects developed around me cost, Access probably averages 25% of the time and probably a lower % of the cost especially when you consider ongoing maintenance. What you should take away from that is, if Access will do the job, it will be done faster and cheaper and the ongoing maintenance will be lower as well. In the two projects where an app I created was rebuilt to run on the web (neither required anonymous logins and so could easily have used Citrix with no conversion whatsoever), the cost was more like 10-1 but in that case they used my schema and essentially mimicked my interface also. For the other, it was closer to 20-1 and the annual costs were twice my annual billing rate assuming I worked on no other project! They had a "better" idea and instead of using a properly normalized schema, they totally flattened it and turned what was a dozen tables into 100 tables. AND reduced the functionality!!!

 

[561414]

Oh Access is great. I love the program, it's easy and fast to develop with it. I always do my most complex SQL queries in Access. And it's great for local environments. But if clients need their frontend on the web, then I resort to web. My two most recent bigger scale projects started in Access, then I moved the database to web a few months later, then the whole thing. But it started in Access because I personally always suggest Access when they want something local. It's also great for reporting and manipulating other Office programs, so I agree that you get a big bang for your buck.

 

[The_Doc_Man]

Absolutely correct. You always want extra tools in the toolkit so that if one approach doesn't work, another one might. Having Access and some other approaches available is a good thing!

 

[Pat Hartman]

But if clients need their frontend on the web, then I resort to web

But what does that mean? Is the need akin to this website where you allow anonymous logins? IF so, then you absolutely cannot use Access. Or is the system closed so that only your staff use it but you just need remote access by some staff or you need very limited access to only a small number of functions by remote staff? I'm just not sure people think deeply enough about the various options available to run Access "in the cloud" before embarking on a complete web rewrite.

 

[561414]

But what does that mean? Is the need akin to this website where you allow anonymous logins? IF so, then you absolutely cannot use Access. Or is the system closed so that only your staff use it but you just need remote access by some staff or you need very limited access to only a small number of functions by remote staff? I'm just not sure people think deeply enough about the various options available to run Access "in the cloud" before embarking on a complete web rewrite.

It's part of a success story. The client at first only wanted to work in his office. He was saving time because of the Access solution provided, so he decided he could also work "outside" (He visits businesses) using his laptop. But then he hired another guy and databases needed to be in sync. I created a REST API for that. But then he was able to hire another other teams, and he wanted all members to sign documents in the field, have an advanced calendar, pretty charts, etc. And, most importantly, all of them would be using different devices. Access was no longer the option.

So that's the case many times, they don't really want to invest in a full web solution until they have checked it's what they actually want. Others will just want to have their stuff on the web, just because. So what do I do? force them to use Access? no. Do I want to embark in complex endeavours forcing myself to only use Access? no. Just set up an environment where Web Development is as easy as using Access, at least, sort of, with practice.

 

[Pat Hartman]

Access was no longer the option.

Why not? What was wrong with running Citrix or RDP?

Only when you really understand the objective can you come up with the right solution. If the client starts out with wanting a web solution because it's cool and sounds modern, you can offer a choice based on the feature set. The web app will be significantly more expensive but at least you don't have to throw away a working application. Both will take time to implement. Again, the web app will take longer but if you're competent in the tool, it won't be horrendous.

If an app is already built using Access, there is a lot of money being thrown away and it is not yours so what do you care? From your perspective, rebuilding using web tools is more money for you and more fun even when Citrix would have been relatively inexpensive using a third party host and up and running within a few days. Unless the client also wants major functional changes to the existing app, I would always try to talk him off the ledge. But, in the end, the client is always right so we do what will make him happy. I also include "at the best possible value" but not everyone sees consulting the same way.

 

[561414]

I respect that you like using those platforms. I personally like using firebase for my web databases, having google and facebook authentication in my apps, setting up virtual private servers on hostinger, coding with github copilot for rapid development, etc. But I know my preferences today can change tomorrow.

That said, I have chosen not to marry Access and its ecosystem of paid-for apps. Instead, I amplified my skillset towards something my clients have certainly appreciated. I believe those products are good, I also believe PowerApps must be good, but I can't confirm the pros and cons. I'm merely trying to encourage others to amplify their skillset here, like I did. I suppose you felt relieved to be able to offer web availability with Citrix or Remote Desktop at some point, well I felt the same when I saw my first app shining with its own url, available everywhere and powered by my code. I'd like other Access developers to not feel bound by the program and instead feel unbound and try new things.

Does it sound like I'm out of place? Like, why do I recommend non-Access things in an Access forum? Well, because Access needs them, they have made many things easy with Access but web availability is one huge thing they still haven't done, that and its archaic security.

 

[Pat Hartman]

I love it. You're posting in an Access forum and telling people they need to change their platform

 

[561414]

Am I wrong?

 

[GPGeorge]

Am I wrong?

It's not a matter of right or wrong. It's a matter of selling a solution based on assumptions which may or may not apply to a given situation. Access isn't the right choice for every application, but neither is a web app.

I heard an interesting comment in my most recent user group meeting that I'm going to repeat a lot going forward.

The gist of the statement was that for developers supporting small to medium sized businesses which need serious multi-featured business applications, Access, and especially Access with SQL Server (or another RDBM system like MySQL), remains the most appropriate solution.

For quick and easy "apps" that people need for tasks like scheduling use of meeting rooms, or perhaps the example I offered earlier, collecting information from an incoming shipment in a warehouse, web-based apps running on a smart device make sense.

It's not right or wrong in either case; it's a matter of being able to select the tools and solutions that best fit the situation.

Keep in mind that the goal is to satisfy the requirements set out by the client, not to satisfy the developer's sense of accomplishment.