Access Front End with Azure SQL Backend? (1 Viewer)

[Thales750]

This is good news. Thanks all.

 

[Thales750]

"...Literally, every old timer-regular poster has said dozens of times.. it won't work. "

One of the reasons I shy away from terms like "every", "all", "always" and "never" is that inevitably someone comes along and points out that whatever is being claimed is not true. So, here we are. No, this old-timer (I am on the shady side of 25 years in the Access development trade), doesn't say it won't work. As a matter of fact, one of the Access/SQL Azure databases I use nearly every day is, well, an Access/SQL Azure database that began life as an Access/Access relational database application which migrated to SQL Azure soon thereafter.

That said, beware of going too far in the other direction,"So that server setup in the cloud, would work. nothing is needed other that what can be done in SSMS." The architecture for an Access/SQL Azure relational database application needs to be very solid and designed specifically with that in mind. It may not take much, depending on where the starting line is, but it's not going to be nothing.

Perhaps you should look into PowerApps as the mobile extension for your field people. It's a good option and low-code to boot.

Here are some videos on my adventures adding PowerApps to a desktop relational database application, although I started out with SharePoint lists. In future, I'll either go to SQL Azure or Dataverse.

Yeah me too, one of the old timers. I have grown tired of the constant learning needed to be at the tip. This information shines a new light, maybe even a spark.

 

[Pat Hartman]

George, the application I tried the conversion with WAS optimized. It contained hundreds of thousands of rows and worked just fine on the LAN with 50+ concurrent users. I even recreated the views and stored procedures where they were used. With one user over the internet, It was like watching paint dry.

 

[GPGeorge]

Spoiler: ETC

George, the application I tried the conversion with WAS optimized. It contained hundreds of thousands of rows and worked just fine on the LAN with 50+ concurrent users. I even recreated the views and stored procedures where they were used. With one user over the internet, It was like watching paint dry.

I don't doubt that you did fully optimize the database application regardless. However, my experience tells me that it's possible to get decent performance, but there are certainly other situations where that would not be the case.

I was asked one time to move an Access accdb to Azure because two partners were moving to different states. One in the Michigan and one in Florida. It was a dog, but the main form loaded three or four subforms simultaneously, each with up to 5 or 6 combo boxes. That's a known source of performance problems. I managed to squeeze some improvement out of it by lazy loading subforms and combo boxes on subforms, but it really never was acceptable.

 

[Pat Hartman]

You say you've had success. I don't doubt it. Was the client running his own Azure cloud or was he using a third party?

 

[GPGeorge]

You say you've had success. I don't doubt it. Was the client running his own Azure cloud or was he using a third party?

In that case, it was actually an Access Web App SQL Azure database, which was a limited version of SQL Azure. However, it only cost ~$5.00 a month. If I had it to do again, I would have argued for a full Azure database that could have been scaled up, but sometimes people are reluctant to invest more than they have to . You get what you pay for, slower performance lower cost tend to be positively correlated.

 

[Pat Hartman]

So, we are not comparing apples to apples. Web pages work very differently from ODBC and therein lies the problem.

$5,000 per month to run an Access application sounds pretty expensive. What does it do to save the company that amount of money?

 

[Minty]

We use Microsoft hosted Azure SQL databases, the costs are nothing like $5000 a month. George wrote $5
Even on an expensive high-performance plan, they are only about £100 a month, and normally less.
Everything connects via ODBC.

If you need a Managed SQL instance that is a more expensive option.

Considering the inbuilt back-up and uptime that is 99.99% (We've never had an outage in 5 years) I think it's cheap.

 

[GPGeorge]

We use Microsoft hosted Azure SQL databases, the costs are nothing like $5000 a month. George wrote $5
Even on an expensive high-performance plan, they are only about £100 a month, and normally less.
Everything connects via ODBC.

If you need a Managed SQL instance that is a more expensive option.

Considering the inbuilt back-up and uptime that is 99.99% (We've never had an outage in 5 years) I think it's cheap.

That $5.00 a month was a now-deprecated offering supporting the Access Web App which was dropped in 2016. I have long suspected that one of the reasons for that was people could invest a few dollars a month and create multiple small SQL Azure databases!

 

[Minty]

@GPGeorge I did think that was cheap!

We are about to up our elastic pool to the next tier but that gives you a lot of computing power, and is shared between about 25 databases.
Some are tiny, some have 100,000's of records. The cost per database is relatively small.

We manage it for our clients, but they have complete access to the data if they want it.