Tuesday, September 25, 2018

Did I Mention I Have a Thing Against Lookup Fields in Tables?

Where do Lookup Fields in Tables Come From Anyway?

Whether you consider them to be a useful idea or not, lookup fields in Access tables came from somewhere and we have to deal with them. My candidate for their dubious origin is SharePoint, but I have only a hunch. I know they were already in Access 2003, but I no longer have earlier versions of Access available and can't recall prior to that(I'd appreciate being set straight on this point, if anyone knows more about it.) Suffice it say they made their way past the door wardens and are now firmly ensconced in MS Access tables, alongside the other dodgy characters cadging drinks from the regular patrons and whistling at the wait staff as they squeeze past to deliver the next round to the table behind.

We're not talking about combo boxes, or drop downs, on forms

I want to be clear before we get into this in depth.

We are talking about lookup fields in tables. Combo boxes on forms work almost exactly the same way in some regards. They can also be abused in the some of the same ways. It's just that the more pernicious implementations I've seen were born in tables.

 What do Lookup Fields in Tables Do?

The basic concept of a lookup field sounds pretty good -- at first look. Make it easy for users to see the values, not the foreign keys, for fields. You have probably had the experience of opening a table and seeing columns of foreign key values in one or more fields. Trying to equate what you see there, e.g. CompanyID of 210, with the value to which that key value relates, i.e. "The Big Box Company", which is CompanyID 210 in tblCompany.

By converting the foreign key field to a lookup field, you magically allow users to see "The Big Box Company" even though the table stores Foreign Key 210.

Done right, and fully understood, they can be benign and even handy for a developer. So far, so good. But like so many of life's little problems, it doesn't end there.


 What Could POSSIBLY GO Wrong?

Actually, more than you might expect.

Here's an example of one of my least favorite attempts to use Look up fields in tables.

Storing the Value, not the Key. 

This is the most common mistake I see. The best way to get to the point is with some images. So, here's a typical Static Lookup Table. This table has a Primary Key and a Value field. Many Lookup Tables are like this.
Lookup Table of Months

It is a Static List; there are twelve months in a year. Many Lookup Tables are more dynamic. Consider a list of approved vendors, for example. Vendors are added and removed from the list as the organization's needs and priorities change. Such dynamic lists might have a number of other fields and be used in other ways, in their own right. In fact, many history tables can double as lookup tables. Here's another example of a query (based on a table with a Date field), in which all of the previously entered dates serves as a lookup list for events in a combo box on a form. You could do this in a lookup field in a table too, I suppose, but, please, please, please, don't

SELECT DateValue([WD].[WorkDate]) AS WD
FROM tblWorkDetail AS WD
GROUP BY DateValue([WD].[WorkDate])
ORDER BY DateValue([WD].[WorkDate]) DESC;


tblWorkDetail contains several other fields, including the start and stop times for the work, a description of the work, and the WorkID itself to which that detail relates. To select all work by date completed for an invoice, this list needs only the relevant dates, grouped on Date. Selecting one date from the combo box adds all of the work for that date to an invoice. Double duty. But I digress. Back to lookup tables which have been infected with Lookup Fields.

Different considerations apply to static and dynamic lookup tables. With a Static list, like Months, it's fine to dispense with the surrogate key. I've seen many tables like this that work just fine.


Static Lookup Table with a Single field


Because it's static, you won't have to revisit the values in this table. You can also count on any of these values stored as a related field in a different table being stable. Many developers follow this practice with static lookups, despite the prevalence of Surrogates (i.e AutoNumbers) as Primary Keys in most tables.

With a Dynamic list, like Vendors, the issues are different. What happens, for example, if you misspell a name as "The Doctar Company" instead of "The Doctor Company". After a week of data entry someone finally notices the misspelling. By now there are multiple purchases made from the company. How hard is it going to be to correct the spelling mistake?

It depends.

If your Vendors Lookup table is like the first example, of course, all of those related records were stored in the purchase table under a surrogate key, (i.e. the Primary Key of the Vendor table.)  No problem, then, correct the Value field in the vendor table and you're done. The related records are not impacted at all.
Surrogate Primary Key to Foreign Key 


If your Vendors table is like the second example, it gets a bit stickier. First, you may or may not have defined the parent-child relationship with Cascade Updates. If so, changing the spelling in the lookup table should cascade the correction to all of the related records child records. Quite doable and safe enough, if not really ideal.



Cascade Update Saves The Day With a Single Value Lookup Table
If you neglected to check the Cascade Update Related Fields property, though, Access won't let you correct the spelling because changing the Primary Key would orphan any child table records with the original value as the Foreign Key. This is not an end of the world kind of problem for an experienced developer, but for the newcomer, who doesn't know how to work around the problem, it could be pretty upsetting. (There are a couple of ways around this problem, neither of which I want to show you. I'd rather you stayed on the straight and narrow to begin with, eh?) 

The long way round to the point, but here we are at last

With that background on the basics of relationships and lookup tables, let's go back to the one in our original table. It looks like this in design view.
Defining a Lookup Field in a Table

 And it looks like this in datasheet view.
Resulting "Lookup List

The Foreign Keys for the Month names are hidden and the user sees only the Month names. Many newcomers are pleased by this, but unaware of the trap they just laid for themselves.

In fact, on the surface, there's no way to differentiate the two in datasheet view. Consider these four screenshots.
Datasheet View of Lookup fields in tables, closed and dropped
 Can you spot the difference? I can't, not without looking at them in design new. (No fair using the table names as a clue.)

It's not surprising then, that many times newcomers are confused when they try to work with such tables in their queries. "Why do I see the Names when I look at the table, but when I use them in a query,  get the numbers instead?"


 So, now, here are some queries based on these tables.
Query Using Tables with Lookup Field based on Surrogate Key
 Access very helpfully includes the lookup field, hiding the fact that ParentTableLookupID is, in fact, a number. Not clear? Try this.
Valid Filtering on Lookup Field using Foreign Key

 Now try this.
Invalid Filtering on On lookup Key using visible, Value Field

 Is it any wonder that so many newcomers flounder when they find that these sweet, sweet lookup fields in their tables are laden with hidden pitfalls? 

And it gets worse from here. I promise.

Come back later for a followup on other problems I've seen.




Thursday, September 6, 2018

Dry Cleaners, Canoes, and Pigs in a Cadillac


I have a handful of analogies that explain my database design philosophy. Some came from colleagues and mentors, some are my own. Here are three of my favorites.


Three Functions of a Dry Cleaners — Three Functions of a Database Application

A properly designed, three tier Access database application bears a remarkable resemblance to a dry cleaners.

First you need a pleasant, efficiently laid out, user friendly interface. In a dry cleaners, that is the reception area and front counter. Customers are welcomed in by chrome, glass and potted plants. They interact with the counter person, dropping off new batches of dirty items and picking up clean ones. They usually get a receipt for their transaction, and use it again later to identify their dry cleaning for the cleaners to get it back.

That's pretty much how an Access interface works too. It facilitates data entry and reporting, i.e. the interactions with the data in the back end.

In the back of the dry cleaners you find the equipment and storage tubs, baskets, and  bins and the noisy, dirty cleaning machines you never want your customers to see.

And that's exactly how the tables and queries in an Access database work as well. You never want users to have to see them, but nothing works without them.

And between them, you find a transitional area where each customers items are grouped and sorted and moved from one container to another according to the rules established to manage it all.

In a well-designed Access database application, that's the job of the logic layer—the VBA and macros.

Yes, I know. It's not an ideal scaffold on which to hang the complexities of a properly designed database application. It is, nonetheless, a reasonably colorful picture of an Access database properly split into a Front End and Back End--with the preview of the logic layer that makes it all work.

So, if you will, an Access database application has a lot in common with a dry cleaners.

Paddling vs Floating in a Canoe

I got the canoe analogy from a fellow MS Access MVP. It’s a good way to explain why it's so important to do things "the Access Way". One of the most common problems we run across with Access is the misguided application of Excel spreadsheet experience to relational databases like Access.

Access is remarkably flexible and forgiving. It’s possible, for better or worse, to make it perform amazingly complex feats using “spreadsheet style” tables and sticky wads of Macros or VBA. Things like Repeating Groups of fields in a table, or even multiple tables containing segmented data (e.g. “tblSales2017”, “tblSales2018”, “tblSales2019”) are not only possible, but even, with enough effort and ingenuity, quite workable.

As the saying goes, just because you can do something, that doesn't mean you should do it. And that leads to the analogy of a trip in a canoe.

If you go upstream in a canoe, against the current, you’ll spend all of our time paddling. 

If you go downstream in a canoe, with the current, you only need the paddle to steer. 

Access, of course, is the canoe in this analogy and the development tools—tables, queries, VBA and reports—are the paddles.  If you want to work less, you’ll learn and follow the best design principles. Normalized relational tables, forms with subforms, and so on. Use them to steer, not to paddle against the current.

Pigs in a Cadillac

One of my favorite stories concerns pigs, Cadillacs and the surprising rarity of common sense. And not just in the design of Access database applications. This story goes back to the very start of my career with Access.

The original version of this story involved the wisdom of buying a Cadillac to transport pigs.

I was a member of a team tasked with evaluating software applications for a large financial enterprise.  Two main contenders emerged in the search. One was a modest Windows based package that came with a mid-five figure license fee. The other one had, as my friend Armen likes to say, an additional zero on the right end of the price tag. One of the analysts responsible for the evaluation feared we were going to choose the Cadillac version, so she offered this little story to encourage the common sense choice.

Here's the story.

You have raised a herd of pigs which you need to get to market. It's time to acquire a vehicle to haul them there. A visit to the local auto dealer, though, presents a bit of a puzzle. On the dealer's lot you find two vehicles big enough to do the job. One is a used pickup truck with a stake bed suited almost perfectly to hauling farm animals. The other is a brand-new full size Cadillac Escalade with plenty of room for a handful of pigs—after a few modifications of course. The price tags of those two vehicle options also differ by a zero, as you probably already guessed.


So, the question for you: Do you want to have the prestige that goes with being able to haul pigs in the back of a brand-new Cadillac? Or should you humble yourself and buy a used pickup truck because it's better suited to the job (and cheaper to boot)?

Well, in that particular situation, the enterprise took ownership of a very nice Cadillac, and had it retrofitted with an appropriate pig holding enclosure, which came at a substantial additional premium over the original license fee 😁.

Unfortunately, in that case, common sense did not prevail.

Over the years, I've had more than one occasion to apply the moral of that story to other situations. Despite the temptation to haul my own pigs in a Cadillac, I have made a concerted effort to stick to the common sense choice as much as possible. It’s saved me a lot of embarrassment and effort, not to mention money.

Lately, answering questions on UtterAccess, I've been thinking about Pigs in a Cadillac a lot. It’s seductively easy to look for a clever way to write wads of code to do something that would be dead-simple, but boring, if you do it “the Access Way”. And that leads me to my final thought. There’s nothing heroic about writing wads of code to compensate for a poorly designed table schema or an elaborate interface.


Saturday, March 17, 2018

Your Soon-to-be-Late Access Web Apps

Two weeks to go.

On or before Monday, April 2nd, 2018 all existing Access Web Apps hosted on Office 365 will become Read-Only.

That means you can then:

  1. Continue to use the Azure SQL database behind that AWA as a read-only reporting database for a short while longer.
  2. Rescue your data from  the Azure SQL database behind that AWA database  using any one of the methods I've previously blogged about for a short while longer. 
The exact length of that extended data-rescue period is not clear.  But it is clear that it won't be forever and that you can't allow a working database to go into read-only status.

So, if you are currently using an Access Web App on O365, and you don't plan to install an On-Premises instance of SharePoint to support it, what is YOUR transition plan?

The Alternatives


In the online Access User Group Access Web Apps that I lead we've had a good run this year. In meeting after meeting we've had presentations from various presenters about their favorite Web App development tools. We consider all of them as potential alternatives to the Soon-to-be-Late AWAs.

Access Desktop (accdb) with a SQL Server/SQL Azure database.

Obviously, this approach won't work with the Azure SQL database currently deployed on O365 for your AWA. However, if you obtain a standard Azure account, you can migrate the database to it quite easily. I told you about that last year as part of a series on the issue.

We had a few presentations on this approach in our AUG AWA Meetings. You can watch the videos on YouTube. You'll find other videos mentioned in this blog at both places.

Boutique Development Tools

There's really one serious contender in this space, IMO, Alpha AnyWhere. I would recommend  anyone looking at Web App development as a profession, or as a full-time role within your organization, at least take a look. In my opinion, though, it probably serves a different market from that targeted originally by AWAs. That said, it offers features, power and scalability that many others don't have quite yet. The main drawback would be the total investment required to implement this solution.

Microsoft Tools

Again, one primary offering from Microsoft exists at the moment, with a second one rapidly catching up.

We learned about using the ASP.Net approach, based on MVCs, to rapidly scaffold out a working web app rapidly.

This approach, quite naturally, requires a back end basebase, like SQL Azure. It can also be connected to an on-premises or remotely hosted SQL Server instance. I would imagine the most  practical solution would be SQL Azure or a hosted SQL Server to which you can connect over the internet.

If you are looking to make a relatively easy transition from AWAs to .net, this is the solution for you. And, if you also want a "grow up path", the fact that the same tools which you use to create the scaffolded, basic MVC app are right there in Visual Studio to take you as far as you want to go.

We haven't really seen PowerApps this year in our AUG AWA group meetings, but there has been a parallel series of presentations from the AUG PowerApps group.

In my opinion, PowerApps are still a year or two from being mature enough to be serious contenders. That said, the pace of development for PowerApps is so rapid that I wouldn't be surprised if they don't come to dominate the market for mobile app development in the very near future, The potential is there.

Other Cloud Platforms

Most of the remaining contenders are in this category. My favorite is ScriptCase, with ZOHO Creator a close second. Both are browser-based development environments. The primary difference, from what I can see, is that ScriptCase is able to connect to a wider variety of back ends, whereas ZOHO seems to be tied to its own data store.

We didn't look at all of the possibilities. To be honest I mostly selected development tools I'd previously seen, or for which I could find a presenter who actually used the tool.

A Hosted Environment for your existing AWA.

 Not surprisingly, there are still ways to run your existing Access Web Apps, even if you can no longer do so on Office 365. I know of at least two different options. One is offered by Rob Koelman's group MetaMicro Automatisering BV in Europe. I've found Rob to be one of the more knowledgeable persons around with regard to AWAs. The other is Apps 4 Rent. I know very little about them, although I am aware of AWAs being moved there.

Moving On

So, that's it. We've had a good year, learned a lot, and come to terms with the impending transition.

If you'd like to join us to see "what's next", please feel free to drop in, or catch a video.

George Hepworth
Chapter Present
Online Access User Groups -- Web Apps



Wednesday, July 19, 2017

"Access SQL Editor" --A Fine Tool For Access Developers

Sometimes, in our pursuit of excellence, we lose sight of the simple things. I was reminded of that again today by a post at UtterAccess.com.The question had to do with a gnarly SQL syntax which, on the surface, appeared to be a UNION query, but Access wasn't recognizing it as such. Several of us were stumped at first.

However, when I popped the SQL in the one of my favorite Access add-ins, the truth was revealed immediately. Consider the following:

Starting on the 7th line in this formatted view, we have an inline subquery. Formatted this way, that's fairly clear. When viewed in the undifferentiated mass within the native Access query editor, that just wasn't clear at all.

SELECT [Reporting Codes Master].[Reporting Code]
    ,[Reporting Codes Master].[Reporting Code Description]
    ,FINAL.[No of Claims]
    ,FINAL.[Revised Debited Amountx]
    ,FINAL.[Original Debited Amountx] AS [Debit Amountx]
FROM [Reporting Codes Master]
LEFT JOIN (
    SELECT REV.[Reporting Code]
        ,REV.[No of Claims]
        ,REV.[Revised Debited Amountx]
        ,ORIG.[Original Debited Amountx]
    FROM (
        SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
            ,COUNT([Pull List].[VIN]) AS [No of Claims]
            ,sum([Pull List].[Mediated Amount]) AS [Revised Debited Amountx]
        FROM [Pull List]
        LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code F] = [Deviation Code Master].[Deviation Code]
        WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
            AND [Pull List].[Deviation Code F] <> "82"
            AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
            AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
        GROUP BY [Deviation Code Master].[Reporting Code]
        HAVING [Deviation Code Master].[Reporting Code] NOT IN (
                ""
                ,"P"
                )
        ) AS REV
    INNER JOIN (
        SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
            ,sum([Pull List].[Debit Amount]) AS [Original Debited Amountx]
        FROM [Pull List]
        LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code] = [Deviation Code Master].[Deviation Code]
        WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
            AND [Pull List].[Deviation Code] <> "82"
            AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
            AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
        GROUP BY [Deviation Code Master].[Reporting Code]
        HAVING [Deviation Code Master].[Reporting Code] NOT IN (
                ""
                ,"P"
                )
        ) AS ORIG ON REV.[Reporting Code] = ORIG.[Reporting Code]
   
    UNION ALL
   
    SELECT REV.[Reporting Code]
        ,REV.[No of Claims]
        ,REV.[Revised Debited Amountx]
        ,ORIG.[Original Debited Amountx]
    FROM (
        SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
            ,COUNT([Pull List].[VIN]) AS [No of Claims]
            ,sum([Pull List].[Mediated Amount]) AS [Revised Debited Amountx]
        FROM [Pull List]
        LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code F] = [Deviation Code Master].[Deviation Code]
        WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
            AND [Pull List].[Deviation Code F] <> "82"
            AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
            AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
        GROUP BY [Deviation Code Master].[Reporting Code]
        HAVING [Deviation Code Master].[Reporting Code] NOT IN (
                ""
                ,"P"
                )
        ) AS REV
    LEFT JOIN (
        SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
            ,sum([Pull List].[Debit Amount]) AS [Original Debited Amountx]
        FROM [Pull List]
        LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code] = [Deviation Code Master].[Deviation Code]
        WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
            AND [Pull List].[Deviation Code] <> "82"
            AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
            AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
        GROUP BY [Deviation Code Master].[Reporting Code]
        HAVING [Deviation Code Master].[Reporting Code] NOT IN (
                ""
                ,"P"
                )
        ) AS ORIG ON REV.[Reporting Code] = ORIG.[Reporting Code]
    WHERE ORIG.[Reporting Code] IS NULL
   
    UNION ALL
   
    SELECT ORIG.[Reporting Code]
        ,0 AS [No of Claims]
        ,0 AS [Revised Debited Amountx]
        ,ORIG.[Original Debited Amountx]
    FROM (
        SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
            ,sum([Pull List].[Debit Amount]) AS [Original Debited Amountx]
        FROM [Pull List]
        LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code] = [Deviation Code Master].[Deviation Code]
        WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
            AND [Pull List].[Deviation Code] <> "82"
            AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
            AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
        GROUP BY [Deviation Code Master].[Reporting Code]
        HAVING [Deviation Code Master].[Reporting Code] NOT IN (
                ""
                ,"P"
                )
        ) AS ORIG
    LEFT JOIN (
        SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
            ,COUNT([Pull List].[VIN]) AS [No of Claims]
            ,sum([Pull List].[Mediated Amount]) AS [Revised Debited Amountx]
        FROM [Pull List]
        LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code F] = [Deviation Code Master].[Deviation Code]
        WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
            AND [Pull List].[Deviation Code F] <> "82"
            AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
            AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
        GROUP BY [Deviation Code Master].[Reporting Code]
        HAVING [Deviation Code Master].[Reporting Code] NOT IN (
                ""
                ,"P"
                )
        ) AS REV ON ORIG.[Reporting Code] = REV.[Reporting Code]
    WHERE REV.[Reporting Code] IS NULL
    ) AS FINAL ON FINAL.[Reporting Code] = [Reporting Codes Master].[Reporting Code]
WHERE [Reporting Codes Master].[Reporting Code] NOT IN (
        ""
        ,"P"
        )
    AND [Reporting Codes Master].LANGUAGE = Forms!AuditResults!cmbLanguage
ORDER BY [Reporting Codes Master].[Reporting Code];

Leaving aside the question of how well this SQL works, whether it can be better optimized, and so on, the ability to apply "pretty formatting" to it, paid off handsomely in helping the poster past a nasty conundrum.

The tool?

It's called "Access SQL Editor", from Field Effect, LLC.

When I bought it, the license was only $5.00 (US) and it's paid for itself over and over and over in situations just like this.

I can't recommend it highly enough.
Access SQL Editor "About" dialog

You can find it here: Field Effect LLC website


Sunday, June 4, 2017

Sometimes You Just Have to Scratch Your Head....

It was a cloudy Saturday afternoon; I was out walking in the woods near my house when the call came. I let it go to voicemail; nothing good comes of such calls I've found. If it's a friend or family member, I hate trying to talk and walk at the same time, especially on uneven surfaces. If it's a client, of course, there's not much I can do until I get home again anyway. And if a client calls on a weekend, that's a particularly unpleasant call.

This one was a client. Their Access database application had stopped working. This particular business is particularly busy on weekends, due to the nature of the services they provide. It is actually an Access/SQL Azure "hybrid" application. That means the interface is an Access accdb. The database is in SQL Azure, out there in the cloud. But not just any SQL Azure database. We'd set it up using the Access Web App environment as the quickest, least costly way to migrate their data "to the cloud". That allows them to expand operations into two different states, hundreds of miles apart, without having to incur the cost of a re-write to the interface. Worked okay for months, although performance sometimes left a little to be desired.

Yesterday afternoon, it started failing with an ODBC connection failure error. The accdb couldn't connect to the data. Bad juju. No data, no schedule of activities for the next two days.

It turns out that sometime Friday night, the 3rd of June, or Saturday morning, the 4th, Microsoft had MOVED the database from one SQL Azure instance to another. No advance notice to clients, no transition. Just slam-bam, over and done with. New server, new database, new credentials needed to connect.

What that meant, of course, is that existing external connections using the prior Server, Database, UID and PWD were no longer valid.

For me, the solution was fairly straightforward. For clients, it falls somewhere between "Damn, this is inconvenient." and "OMG, what am I supposed to do now?" And fortunately for them, I DID return the voicemail after I got home, showered, and sat down at my desk.


--------------------------------
It occurred to me after I had posted this article that maybe I ought to mention the solution to this particular problem.


Open the accdw file which controls the Access Web App.


Click the File tab on the ribbon to get to the back stage.Here you can see that I've already re-enabled connections for this AWA. You'll see a different message if yours does not have connections enabled.


Connections must be enabled to use the SQL Azure database with accdb's, xlsx's, PowerBI and other external data consumers


 Click on "Connections". This opens the external connections dialog. You'll need to make sure two settings are correct here. Select "From Any Location", to allow external connections to your SQL Azure db from, well, from any location. Then select "Enable Read-Write Connection".



Allow connections from any location and enable Read-Write connections



Now, click on "View Read-Write Connection Information" to get the name of the new server, the name of the new database, the new external writer UserName (UID) and new password.
Use the Connection Information here to relink your client application to the SQL Azure tables



Use that connection information to relink your accdb, xlsx, PowerBI or other data consumer application to use the new database. The data is still the same, only the location was changed.


-------------------------------------------


Another follow up. I've now heard from AWA users in Sweden, Italy and the UK, as well as US based users in several states (meaning different server locations). That qualifies as a wide-spread problem, IMO.


-------------------------------------------



All of which leaves me scratching my head and wondering just how detached from their customers Microsoft has become. A move like that had to be instigated at a fairly high level. It had to be planned for some time. And it had to be coordinated among a lot of people. Didn't anybody over there in Redmond ask, "What'll happen to our customers if we do this without telling them?"

Guess not.


---------------------------------------
Update: Tuesday, June 6, 2017
I have heard from the Access team. They are looking into what happened and will reply directly when they have a better picture of the events.
As I thought, the Access team themselves were not involved in the move; it doesn't mitigate the pain, of course, but it does suggest the people who made the change were not aware of the potential problems it would cause.


Update: Wednesday, June 7, 2017
The Access team has responded to my request for clarification.
This was a result of a planned maintenance operation on SPO servers and tenants.
They will work with the owners of that operation to improve communication in the future.