Access Front End with Azure SQL Backend?

[AccessProgrammer142]

Does anyone know if this is possible? Customer has MS Access in Office 365 account. Customer also has an Azure account. Will I be able to have the MS Access front end communicate with the Azure SQL backend?

 

[Isaac]

Yes, and what a beauty I wish I could do that at my current job

 

[Minty]

Yes - works very well, just ensure you try and keep the amount of data being moved around to the minimum.
Lots of tips but e.g don't load a form with a complete table as a record source etc.

 

[AccessProgrammer142]

OK, then as a follow-up question: How? I am trying to figure out the connection between Azure and O365. ODBC connection? Some other mechanism?

 

[Pat Hartman]

Azure is just an ODBC connection. It works the same way other ODBC connections work. The easiest way is to start by creating a DSN. You'll need the URL of the server and the server must be set up to accept connections from Access.

 

[Isaac]

ODBC connection

Yes. google the subject "access linked tables"

 

[Thales750]

Resurrecting this thread.
Have all hosted SQL Servers become capable of connecting to MS Access Front Ends, or is this unique to Azure?
I

 

[cheekybuddha]

Have all hosted SQL Servers become capable of connecting to MS Access Front Ends

Well, it's the Access frontends that connect to the SQL Servers.

No, they have not all become capable of connecting - they always have been able!

 

[Thales750]

Well, it's the Access frontends that connect to the SQL Servers.

No, they have not all become capable of connecting - they always have been able!

Oh you're a pretty funny guy.
Semantics, and the need to massage your ego, aside. Have the latency problems been overcome on other web servers, of has Microsoft done something different?

 

[Minty]

If you are connecting to an Azure site from access there will always be some degree of delay, it is over the WAN after all.

What latency issues are you describing?

If you have a good connection and a relatively local Azure location then Access works very well, assuming you have designed it to work with a remote backend.

 

[Thales750]

If you are connecting to an Azure site from access there will always be some degree of delay, it is over the WAN after all.

What latency issues are you describing?

If you have a good connection and a relatively local Azure location then Access works very well, assuming you have designed it to work with a remote backend.

There have been dozens of these thread over the last 20 years or so asking about connecting to Cloud databases. Pretty much the consensus by all the folks that it doesn't work. Access, as you know it pretty persnickety when it comes to persistent connections. A couple of bumps and it freezes or you could loose some data.

How has that been solved?

Thank you

 

[Thales750]

I don't know about a remote backend but they work great with SQL Backends. All the local datasets from the core tables is queried. Even the lookup tables are mostly through queries, so that part is good.

 

[Minty]

There have been dozens of these thread over the last 20 years or so asking about connecting to Cloud databases. Pretty much the consensus by all the folks that it doesn't work. Access, as you know it pretty persnickety when it comes to persistent connections. A couple of bumps and it freezes or you could loose some data.

How has that been solved?

Thank you

We develop almost exclusively with Azure backends these days.

The only issue you might get depending on the setup is if someone in say, India connects, to a UK based Azure SQL instance. On a poor connection. Then you are in for some poor quality connectivity.

There are as usual a load of caveats - don't load 25000 records into a form, don't connect local tables to azure tables in a query and expect it to work quickly, keep local copies of common lookup lists, yada yada...

But if you design with the obvious constraints in place, and make the server do all the heavy lifting it's very workable.

We also have some bespoke IP address whitelisting security processes which make it very easy to manage if you have road warriors, but if the client uses Active Directory you can get around some of those more "interesting" issues.

 

[pbaldy]

Echoing what others have said, using an Azure SQL Server can work just fine. I got pushed off an on-premise SQL Server to a cloud server late last year. I certainly had to smooth some bumps out, but on the whole all my apps run just fine. Most of the bumps were cases of me being lazy because I could get away with it on a local server. My DSNless code on startup went from a couple of seconds for about 50 tables to 20 seconds. Another developer went the way of adding a "loading" form, I worked around it by only relinking a table if the connection string differed. Back to a couple of seconds.

 

[Isaac]

all hosted SQL Servers

To which products do you refer? Examples?

 

[Thales750]

To which products do you refer? Examples?

I have no idea, not believing this was possible until I stumbled on this thread. Literally, every old timer-regular poster has said dozens of times. it won't work. The solution has always been some form of Remote Desktop or Console. I'm still bewildered.

My shortcomings overlooked for a bit,
I have two major databases that have been the bases for all of my systems, they have about 50K lines of code each. The first one runs chemical plants, and runs perfectly smooth with dozens of remote users, all of them on VPNs, utilizing a local SQL Server. So that server setup in the cloud, would work. nothing is needed other that what can be done in SSMS.

Here's the opportunity: I have a Homebuilding system that has become completely obsolete having zero cloud value. Actually, not quite zero. It does have some limited cloud value, it talks to Outlook and sends calendar events to mobile devices, it interfaces with cell phones and makes phone calls including call data, it saves photos and other attachments on incoming email, and it sends and receives text messages.

All that is ok, but a nightmare to administer. It really needs to be an app or a data driven site. The rub is how comprehensive the main user interfaces are, their first iterations was in the late 90s. The idea is to have the current front ends connect to cloud based SQL database and start building mobile connections for the field people. These would probably start out as PHP and then maybe aps next, haven't though it out that far.

So maybe you could tell me what services I would need.
Thanks for your interest.

 

[Isaac]

I have no idea, not believing this was possible until I stumbled on this thread. Literally, every old timer-regular poster has said dozens of times. it won't work. The solution has always been some form of Remote Desktop or Console. I'm still bewildered.

My shortcomings overlooked for a bit,
I have two major databases that have been the bases for all of my systems, they have about 50K lines of code each. The first one runs chemical plants, and runs perfectly smooth with dozens of remote users, all of them on VPNs, utilizing a local SQL Server. So that server setup in the cloud, would work. nothing is needed other that what can be done in SSMS.

Here's the opportunity: I have a Homebuilding system that has become completely obsolete having zero cloud value. Actually, not quite zero. It does have some limited cloud value, it talks to Outlook and sends calendar events to mobile devices, it interfaces with cell phones and makes phone calls including call data, it saves photos and other attachments on incoming email, and it send text messages.

All that is ok, but a nightmare to administer. It really need to be an ap or a data driven site. The rub is how comprehensive the main user interfaces are. The idea is to have the current front ends connect to cloud based SQL database and start building mobile connections for the field people. These would probably start out as PHP and then maybe aps next, haven't though it out that far.

So maybe you could tell me what services I would need.
Thanks for your interest.

Ok, I understand. I was just curious - and trust me, I'm probably even just as ignorant or more because I thought the only hosted SQL Server database in existence was Azure - but honestly don't know as I use neither.

That's actually a pretty good testimony that you have users with VPN and local FE's in remote places using SQL server!

I'll bow out as I know nothing about building web or mobile apps other than having had some good luck with AppSheet, but that's definitely boilerplate stuff and not something you can custom code so probably wouldn't replace the elaborate setup you have
good luck

 

[GPGeorge]

"...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.

 

[Pat Hartman]

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

That is not quite what they say. They say -- it is like watching paint dry. So far, the people who have had success are those who managed their own Azure cloud. When I tried it a few years ago with two different providers, it "worked" but it was like watching paint dry. I converted a database that already had a substantial SQL Server BE and had been operational for several years on a LAN with no problems. I'm getting the impression that things are improving slowly and MS has a new offering which is pretty much Remote Desktop hosted by them as a solution for Access in the cloud. It sounds promising.

 

[GPGeorge]

Spoiler: ETC.

That is not quite what they say. They say -- it is like watching paint dry. So far, the people who have had success are those who managed their own Azure cloud. When I tried it a few years ago with two different providers, it "worked" but it was like watching paint dry. I converted a database that already had a substantial SQL Server BE and had been operational for several years on a LAN with no problems. I'm getting the impression that things are improving slowly and MS has a new offering which is pretty much Remote Desktop hosted by them as a solution for Access in the cloud. It sounds promising.

I think performance of an Azure back end depends a lot on the design of the Access Front End. Optimize it, as you say, and things are acceptable. But pretend that "all one has to do is link the Azure tables", and things are going to assume the pace of slow-drying pastels on wallboard.