"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

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.

So, Now You Rescued Your Data, What Do You Plan To Do With It?

In recent blog posts, I have described ways to rescue your data from your soon-to-expire Access Web Apps. Oh, you DO know that Microsoft plans to end AWAs don't you? If not, I'll wait right here till you catch up. And if you needed to read that one, you also ought to check out SharePoint Lists are Going to be Just Fine, Do You Want an accdb With That?, and Get Your Data From Your Expiring Access Web App as well. I'll be right over here if you want to spend a few minutes browsing.

Okay, then, ready to find out what is next?

Plan A Through H

At our Access User Group Access Web App Online meeting last night (May 18th, 2017), we had a round table discussion of alternatives to AWAs for online, cloud database applications. We're all members of the AUG AWA OL group because we have long been fans of the AWA approach. Unfortunately, our meetings this year have been devoted primarily to trying to figure out what to do next. Here are some ideas we came up with last night.

If you are currently "evaluating your options" as the press release often says when announcing the departure of a long-valued employee, here are some places to get started. We'll come back in future blogs with more detailed write ups of anything we learn as we try them out. So, in no particular order of importance, relevance, ease of use, and especially NOT any recommendation as of this point in time, here are some options. Consider this a "getting to know the players" statement for now. I hope to add to it over the next few weeks as new candidates appear.
I've included some comments on cost. AWAs were an incredibly cost-effective way to get a small cloud-based application up and running, so this has to be a part of the evaluation of any potential replacements.

ZOHO Creator

Julian Kirkness has already published a review of this contender, so I'll content myself with a couple of personal comments. Cost appears to be reasonable for this option. Apparently there's even a free option, although I am not sure how appropriate that would be for many uses. We're casting about for replacements for AWAs, which are -- or were -- incredibly cost-effective, so this might well be a selling point. Just don't let it be the only one you look at. Julian likes this tool and is exploring it further, so that's a good sign. We'll probably see more of this one in the coming weeks as developers like Julian get further along in their reviews of options.

Microsoft PowerApps

I have to admit that I've not yet been sold on this option, although I do look at it with increasing favor, partly because it's relatively more accessible to traditional Microsoft users. It's a no-code/low-code tool. While I hesitate to use the term, you can "program" many functions with the expression language it uses. Frankly, I have been impressed by the relatively rapid expansion of the PowerApps initiative. Every time I visit one of their sites, I see new capabilities and extensions. Unlike AWAs, PowerApps have the full weight of the Microsoft enterprise behind them. They're still a distant second, IMO, to the capabilities of the AWA, but there's a lot going on over there and I'm sure we'll be incorporating them into our MS toolkits at some point. Not as a transition from AWAs, unfortunately, but as an alternative for sure.

Pricing is, as are many things Microsoft, a bit obscure, but from what I can tell, you can still get involved with PowerApps for a minimal (i.e. "free") cost to begin with. That's attractive even if you try it and decide it's not for you.

Oh, one thing I do have to say about PowerApps is that the range of data sources they can consume is impressive: .csv files to SQL Azure tables. Oh, with the exception of accdbs.... There are good technical reasons for that, but when you tell an Access developer that the first three steps in adopting PowerApps are:

1. Migrate your data to a different kind of storage, such as SQL Azure.
2. Close your Access application.
3. Open your PowerApps application.

Well, it's kind of hard to call that "an Access solution" any longer, is it not?

To be fair, of course, if you do use SQL Azure or another cloud database, you can connect to that same data store from both an accdb and a PowerApp, so there's that hybrid application angle.

Quickbase

Selling point? It originated from Intuit, makers of Quicken and QuickBooks, so it should have some muscle behind it, although I understand they did spin it off.
To my way of thinking, the no-code/low-code approach only makes sense for fairly simple applications, such as an issue tracker. That was one of the "featured app templates" offered when I checked their website. They also mention using "QuickBase Sync [to] connect with popular cloud apps, such as Salesforce and Zendesk, as well as CSV files in just a few clicks. " Not mentioning relational databases suggests to me this one will work better with other types of data.
Cost is a factor in our search. It looks like the basic entry point is going to be $150 a month ($15 per user with a 10 user minimum.) I will, as promised, look at it more in-depth, but I can hardly recommend it until I'm sure it's got the horse-power to justify the cost of entry.

Caspio

They advertise themselves as an ultra-fast, no-code solution and have a section devoted specifically to migrating MS Access accdbs "to the cloud". Along with other claims on their website, that kind of hyperbole makes me wince. I wish I could be more positive, but it just makes me feel too much like I'm being sold something when it's made to sound so simple. I'll go back and give it a better look along the way, but for now,it won't be at the top of my list.

Pricing appears to be reasonable. They offer a free plan for unlimited users and three "DataPages". A DataPage appears to the equivalent of one "screen" of data, i.e. an input form or a report. For some kinds of applications, that might suffice. One could get started with that plan, find out if it's going to work for their needs, and then move to a paid plan with the appropriate level of resources.

Somewhere in their website, they mention ..." a backend database built on Microsoft SQL Server." That's potentially a good thing, but it's not clear whether you, the developer, will be able to get close to it. On the other hand, this looks like a truly relational database system, so it has to be given serious consideration on that basis alone.

Alpha Anywhere

These guys are good, from everything I hear. But they are not cheap. I looked at this tool once, when it was known as Alpha Five. I liked the fact that one code base can generate both a desktop and browser based version of an application. I didn't like the fact that it requires a proprietary server to run. I also was not thrilled by the coding language and approach involved. I suppose you'd get the hang of it sooner or later, but it's really quite different from our traditional MS Access/SQL Server experience. That means you're moving out of the mainstream, where you'll be taking on more of the risk of adequate support for yourself and your clients.

However, for an enterprise level application, this one ought to get serious consideration.

FileMaker

Another venerable tool that has its roots in the Apple ecosystem. However, it's not Apple-centric. You can create applications that work on Mac or Windows. I'm not sure yet if that requires two sets of code; that's one of the questions we need to address. I've been told that is not the case. I know too little about it to offer more yet. Stay tuned as we dig into our options.

FileMaker also requires you either license a proprietary server, or host on Amazon Web Services (AWS) starting at $888/year for up to 5 users and up.

Code On Time

Unfortunately, this one is relatively new to us, and I can't really offer much detail yet. From their website, it looks like another no-code/low-code approach. It does support a range of platforms, including SharePoint and Azure, as well as ASP.net and DotNetNuke. All of these, by the way, are application tier products, not necessarily database products. They do provide "support for major SQL databases" That means we need to dig further into their offerings to get a handle on that. Pricing includes a free option for starters. From there it looks like licensing would get pricey fairly quickly, though, the lowest level is priced at $349/user/year.

OutSystems

This one was discovered during our Panel search, but I'm not going to pursue it for the time being. Pricing puts this one at the far end of what most Access developers or power users would consider  to be a viable alternative to an AWA. We'll probably get back to it later, but for now, you might want to check it out on your own if you're so inclined.

So, that's the first crop of contenders our User Group came up with.

Over the next few weeks and months, we'll try to get "down and dirty" with as many of them as we can, and we'll provide our opinions as we learn.

Whew! SharePoint Lists are Going to Be Just Fine, Thank You.

Kudos to the Access Team in Redmond.


When I learned that Access Web Apps were going to be terminated, one of the first things I did was try out the feature that allows you to export the tables from an AWA into SharePoint Lists in the Office 365 site where they reside. This is the default procedure suggested by Microsoft. Actually, I can see a few advantages to it, which I'll sum up  below. It's not going to be my first choice for most AWAs, but for a lot of situations, it ought to work just fine.

MS did a really good job of explaining the process, so I don't need to duplicate that here now.

The Good, the Bad, and the Ugly

(I know, I know. I used a cliché in public. I'm sorry.)

I had mixed results that first week.

100% Success

One of the AWAs I support for a client worked exactly as it was supposed to work. The process took only a few minutes and the end result was a set of SharePoint lists that mirror the source tables exactly. If I had wanted, I could have been designing a new interface for that application within a half hour of starting the export.

Mixed Success

A second AWA of my own, one with two very large tables in it, partially worked. The SharePoint lists were created, the data was exported, but the Lookup Fields which identify the relationships between tables failed. The error message was not detailed, but the gist of it was that the number of records I was trying to update was too great. Not the end of the world by any means. Nonetheless, a cautionary experience to anyone depending on getting their data out of the AWA into a safe, new home. Plan ahead, practice the procedure until you are certain you can get your data for reals when the final day of doom descends.

Oh No!

The third AWA I tried to export to SharePoint lists, however, failed repeatedly. I was baffled. I shared my experience with the Access Team, who responded immediately. They published a fix. It partially worked, but still no joy for me.

I kept trying to export that same AWA to SharePoint Lists, doing so off and on for a couple of weeks. Today, I had a beautiful thought. What if I've overloaded my O365 site? I do have some serious work on the site, but there was an awful lot of left-over "experiments" laying around. I cleaned up a lot of it, focusing primarily on deleting old AWAs and lists. It was somewhat surprising, in fact. I counted 42 separate subsites, many with at one moribund AWA in it. After getting rid of most of them, I tried the Export to SharePoint Lists function again.

Bang! It went smooth as silk, and much faster than I would have expected.

So, I touched bases with the Access Team. They let me know they had pushed another fix out just recently. I don't know if it was a coincidence, but whether it was their fix, or my house-cleaning, as of now, I have a 100% success to smile about.

Why Would You Even Do That?

Let me wrap up with my impressions of why you might want to export the tables from your AWA's SQL Azure database into SharePoint lists.

1. You already have an Office 365 site. You don't need to find a new hosting solution. It's not a great transition, but it's a relatively smooth one.
2. SharePoint Lists in Office 365 are available either directly in a browser or as linked tables in Access accdbs. They are versatile.
3. Unlike any other method of remote hosting for Access linked tables, SharePoint lists support off-line processing. The resynch process between accdbs and SharePoint is very good.

Saving Your AWA Data: Do You Want an accdb With That Lemonade?

You need to get your data out of the SQL Azure databases behind your AWAs. But, you already knew that, right? And you've started doing that, right?

This task isn't like the recent all-nighter you pulled to prepare and file your Federal Income Taxes. Maybe the stress level is just as high, but for most of us, gathering receipts and other related documents, filling out the forms, and writing that check is a known quantity. This one isn't, and that's why I want to offer some observations in my personal experiences, which I hope will prove to be relevant and helpful to you too.

Practice Makes Perfect

Getting your data safely out of your Office 365 AWAs and into more stable long-term storage is a once-in-a-lifetime event--at least that's our hope. That means you probably don't have a lot of experience doing it so far. I sure hadn't done it that much until recently.

So, while you still have plenty of time to practice, it's a good idea to take a run at one or more of the options available to do that.

As I outlined recently, you have three main options to rescue your data:

1. Convert the SQL Azure tables to local Access tables in an accdb.
2. Convert the SQL Azure tables to SharePoint lists in your current O365 site.
3. Extract the SQL Azure tables as a dacpac and deploy them via that dacpac to a new SQL Azure, or SQL Server database.

It's All About the Data

Keep in mind that we're NOT talking about the interface yet. We're focusing only on rescuing your data.

Follow a Strategy, Not a Reaction

I've found there are advantages and disadvantages to each of the three main strategies listed above. Here's a review of the pros and cons I've observed personally for the first option. I hope this can help you decide which way to go with your own AWAs. Let's start with what I think is probably the simplest one for most Access users. In future blog posts I'll tell you more about my experience with the Export to SharePoint Lists option, and revisit the dacpac option.

Convert Linked SQL Azure Tables to Local ACCDB Tables

This is, in my opinion, the easiest and quickest way to get your data into a new home, albeit not the one I'd recommend for most of us. First, let's review the method; then we can review some of  the Pros and Cons I've observed.

Here are the basic steps. You start with the template web app file for your Access Web App.

• Navigate to, select, and open the accdw for your AWA in File Explorer.
 



Figure 1. Open the ACCDW file from your hard drive

• When the AWA's design interface opens in Access, click "File" on the ribbon to go to the Back Stage area.
• In the Back Stage, you can select the option to create a Read-Only Database with links to your tables. It's designated as "Report on My Data". See Figure 2.
• When you click the "Report on My Data" button, an information dialog appears. Click "OK" to continue to the next step.

Figure 2. Create a Reporting, Read-Only ACCDB

• Navigate to an appropriate folder, select an appropriate name for your accdb, and save the read-only accdb.

Figure 3. Save your Reporting database with ODBC Links to all of the tables in your AWA

• This may take a few minutes, but it's been pretty fast in most of my tests.
• When it's done, you'll have a new accdb containing nothing but ODBC links to the SQL Azure database.

Figure 4. Accdb with Read-Only links to your AWAs tables

• The tables in this newly created accdb (called  DiabetesTracker_ReadOnly.accdb in Figure 4) use the Read-Only credentials provided by default in all AWAs.

Figure 5. Connection String showing the "ExternalReader" UserID

• The intent behind this automated process was to make it easier to develop reports for your AWA, using the powerful Access reporting engine. For that purpose, Read-Only tables are the best choice. For this data rescue task, it really doesn't matter very much whether the linked tables are Read-Only or Read-Write. And, since creating this accdb is a built-in function, let's run with it. There's another option--creating Read-Write links, but we're not going to spend a lot of time on them because, frankly, it doesn't matter all that much at the moment.
• As you can see I Figures 5 and 6, the SQL Azure database behind your AWA contains more tables than you will probably need. The ones we don't want are identified by the inclusion of the question mark, ?, in their names.

Figure 6. Filtering Out  Unneeded Tables for Deletion

• Disclaimer: You may want to keep image tables for now. For performance reasons in the browser, these tables contain all images attached to tables in your AWA. Therefore, if you have stored images as part of your AWA, you may want to retain these tables, at least for the short run. You can always remove them later if you decide you don't want them.
• After choosing the tables you don't need, and therefore don't want to convert to Local Access tables, use the mouse and keyboard to select them.
• Right mouse-click on the Navigation Pane and select "Delete" from the short-cut menu.

Figure 7. Delete links to tables you don't want to be converted to local Access tables.

• Now it's time to remove the "?" filter and review the remaining linked tables.
• Select the tables to be converted to local Access tables, using the mouse and keyboard.
• Make sure you include or exclude any of the remaining linked tables as appropriate.
• When you have the right group of tables selected, right mouse-click on them in the Navigation Pane.
• Click "Convert to Local Table" and watch the magic happen.

Figure 8. Select and convert ODBC linked tables to local Access tables

• You're Done!



Figure 9. Linked tables after conversion to local Access tables

That was relatively fast and painless, wasn't it?

All of the data in the tables you selected for conversion from SQL Azure to local Access should be intact, ready for your next project.

The Dark Side of the Cloud

There is a dark lining to this cloud, though. So far, I've not been able to retain any relationships between tables that are clearly related in the SQL Azure database, with proper Foreign Key restraints.

 

Figure 10. Relationship information didn't survive the trip from SQL Azure to Access.

Why this happens isn't really clear to me. In SQL Azure, those relationships are there, defined by Foreign Key restraints. And the indexes on the SQL Azure tables DO survive.

Figure 11. Indexes migrate from SQL Azure to Access

Summary and Conclusion

Obviously, this strategy has pros and cons.

It's quick and relatively easy to do.

• Creating the read-only accdb is a built-in function.
• Filtering tables for deletion or conversion is manual to some extent, but you can make the conversion in bulk after selecting a group of tables to convert.
• There's nothing terribly complex or unfamiliar about converting linked tables to local tables in the accdb.

It retains some important meta-data.

• This method does retain indexes on the converted tables.

It loses some important meta-data.

• So far in my testing, at least, conversion to local tables isn't able to preserve Foreign Key restraints.

Given the observations above, I'd say this would be a good way to grab a current data set from your AWA's SQL Azure database to use in a re-development effort. However, for the final transition to its new home, I'd most likely go with the dacpac method I introduced here.


There is one more method to cover, exporting your tables to SharePoint lists. We'll visit that one next.

Get Your Data From Your Expiring Access Web App

Microsoft has announced the end of Access Web Apps. Not, as is usually the case, end of support for the product, which actually means that you can go on using the application as long as you don't need support from Microsoft for that application. Some people (perhaps just a handful) still use Access 97, for example, twenty years later. Access 97, of course, is long out of support, but still working. This is different.

Rather, if you have Access Web Apps (AWAs) running on an Office 365 site, those Access Web Apps will be removed from your Office 365 site in April, 2018.

If you have an on-premises installation of SharePoint, Access Web Apps will continue to run for the normal SharePoint product life cycle, however long that turns out to be. I'll leave it to you to sleuth through the Microsoft sites to figure that one out, but it is probably going to be five years.

The end of the line for Access Web Apps means you need to start now to get your AWAs converted to a new technology. That, in turn, means you have two tasks ahead of you in the next few months:

• Rewrite the interface from the current browser-based environment to "something else”, whatever you decide it needs to be.
• Get the data for your existing AWAs out of the SQL Azure database behind them before the database is dropped from its SQL Azure home.

If you have a pure AWA application, running only in a browser, you'll have to pick a different way to provide a new interface.

Your choices include:

• A hybrid Access accdb/SQL Server architecture
• .Net application architecture.
• Other desktop or browser based development environments.

In a way, you might think of this as an opportunity to really get the interface right. The “railed” experience of AWAs left a lot to be desired, to be frank. Unfortunately, that’s not much comfort to those faced with ramping up a new development effort, I suppose. It can be costly and time consuming to pick a new development tool, design a new interface and data logic layer and then design, test and deploy it.

Fortunately, getting your data out the SQL Azure database is a different matter. It's not that hard to do. In fact, there are at least three ways to get your data out of the SQL Azure database behind an Access Web App:

• Link your AWA's tables to an accdb and, using the built-in conversion function, convert them to local Access tables in that accdb.
• Export your AWA's tables to SharePoint lists on your current Office 365 site.
• Extract a dacpac from an app package created in the Access design tool using the Save As option.

There are other, less straightforward, ways to do it than these three; perhaps they would be familiar to Access developers. However, given the ease and simplicity of these three options, we should give preference to them, IMO. Let’s quickly review the first two.

Convert To Local Tables in Access

One way to get your tables from the SQL Azure database into an Access accdb is to convert to local tables. If you’ve created a hybrid database with an Access accdb linked via ODBC to the tables in your SQL Azure database, you can use a built-in function in the Access accdb to get the tables into the accdb. Or, if you haven’t already done so, you can create an accdb and use the credentials from your AWA to connect that accdb to the SQL Azure (or SQL Server) database and create those linked tables. That’s usually a rather trivial task, actually, and we don’t need to cover it in great detail.

To get the credentials you need, go to the Backstage in your accdw and select “Manage-Connections”.

Figure 1. Get the Credentials to Create ODBC-Linked Tables in an accdb

I will assume, for the purposes of this discussion, that you can take it from here. You can also see in Figure 1 that you can create a similar accdb by selecting “Report on my Data”. That creates a read-only accdb with the links you need to the SQL Azure tables. It will work for the next step even though the linked tables themselves are read-only. Once you have them in an accdb, they are read-write.

Figure 2. Convert Linked Tables to Local Tables

This screenshot of the short-cut menu ought to be sufficient to get you going. Just right-click on a table and select the Convert to Local Table option. This method requires manual conversion, one table at a time, so in a database with many tables, it could be tedious, although it is pretty easy to understand.

If you want to revert back to an Access FE/Access BE design, this is about as simple as it gets. Just keep in mind that this is no longer going to be a cloud-facing design; if that’s important, you might want to keep looking for another data export strategy.

Let’s consider Microsoft’s official strategy for exporting tables from your AWA into a different storage container next.

Create SharePoint Lists from your SQL Azure Tables

This method­—exporting the tables from your AWA’s SQL Azure database to SharePoint lists—has been outlined on the Office Support Site. This strategy is in keeping with the assumption that many of the Access Web Apps created by Office 365 users will be adequately supported as SharePoint lists. And that is, no doubt, true, IF you want to maintain your Office 365 platform as a way to share the data across locations. I also expect that a fairly high percentage of existing AWAs fall into the "small-to-medium sized" range, which would fit comfortably within the SharePoint List environment.

On the other hand, SP lists do limit your ability to create a new web-based interface, unless you happen to be skilled with SharePoint.

I have exported the tables from a handful of my own AWAs this way, with mixed success, to be frank. One of them, fairly small, went quickly. Another, with more than 43,000 records in each of two tables (86,000 + in all), failed to completely export after running 24 1/2 hours. All of the data was copied into SharePoint lists. However, the process failed during the next phase—creating the relationship lookups on those two large tables. If all I had wanted was the data, that might be tolerable. But without relationships, you really don’t have a relational database, now do you?

If you want to go this route, the steps are quite straightforward. Open the AWA in your browser. Under Settings (the “gear” icon in the upper right hand corner of the page), click the recently added option “Export to SharePoint Lists”.

Figure 3. Select Export to SharePoint List from the Settings "Gear"

When the dialog opens, click “Yes” to start the export.

Figure 4. Click Yes to Start the Export

The next dialog is primarily informative. It provides the location of the new subsite where the exported lists will be saved.

Figure 5. Location of the Subsite with your Newly Exported SharePoint Lists

Clicking the “OK” button starts the export.

Figure 6. The Export Has Started

Export progress is updated for each table as it is processed. There are a few steps involved.

• The first is creating the list
• The next is exporting your data to those lists.
• Then, lookups are created between fields in the lists to manage the relationships defined in the SQL Azure database.

Figure 7. Periodically Refresh the Page to Follow Progress of the Export

Figure 8. Progress for Large Tables Being Exported to Sharepoint

That was how it went for the first two attempts I made to export existing AWAs, however, there were two problems, one with this large AWA and one with my most important personal AWA.
For the tables shown in Figures 7 and 8, the data was exported, but the process failed at the next step— creating the lookups to support relationships.

Fortunately for me, I have a better strategy for getting this data out of the SQL Azure database anyway. I’m including this example as a caution about what you might expect if you choose this export strategy.

Even more problematic, the third AWA I tried to export repeatedly failed to even begin creating lists. I’m still not sure what the problem is with that one. I’m still working on that, but I’m not particularly worried about it because I have a better way to do this anyway, particularly for databases that I want to move to SQL Server, not as SharePoint lists.

Let’s look now at a strategy for moving the entire SQL Azure database behind your AWA into another SQL Server. That target database can be an on-premises SQL Server,or a SQL Azure instance.

Deploy a dacpac

Dacpacs have been supported in SQL Server since the 2008 R2 version
If you want to move your data into a different SQL Server database, either a SQL Server or SQL Azure, here’s how you can get that database moved quickly in just a few steps, using a dacpac from your current AWA’s SQL Azure database. Start by creating an app package from your Access Web App using the Save As function.

Figure 9. Save an App Package From Your Access Web App

The app package contains all of the components from the original AWA, including the dacpac, which is what we are interested in right now. Save it to a folder on your hard drive, like this one.

Figure 10.Saved Location for Your App Package

It may take a few minutes to create the app package, depending on the amount of data in it. The app package is actually a zip file containing several other files that make up the AWA. Changing the extension to .ZIP allows you to extract the dacpac from it.

Figure 11. Find and extract the dacpac in your renamed app package

The dacpac in all app packages is named the same, “appdb.dacpac.” Therefore, when you have extracted it for the next step, you’ll immediately want to rename it to reflect the AWA to which it belongs.

Figure 12. Extracted and Renamed dacpac

Now, open SQL Server Management Studio (SSMS) and connect to the SQL Server instance to which you plan to migrate the database. This can be a local SQL Server instance, a network SQL Server instance, a remotely hosted SQL Server instance, or SQL Azure. I’m going to use a local instance for this demonstration, but ultimately, to enable cloud-based support for the replacements for my existing AWAs, I’ll move them to SQL Azure.

Figure 13. Connect to Your Target SQL Server

Right-mouse click on the Databases node, highlighted in Figure 13, and select Deploy Data-tier Application from the short-cut menu, as shown in Figure 14.

Figure 14. Select Deploy Data-tier Application from the Shortcut Menu

The wizard will open and lead you through the steps needed to create the new database on this SQL Server instance.

Start by using the Browse button to locate and select the dacpac you just extracted and renamed.

Figure 15. Browse to and Select the dacpac

The wizard assigns the name of the dacpac as the default name of the database to be restored.

Figure 16. Default Database Name Obtained from dacpac file

Note that I accidentally misspelled the name of the app, and then retained that misspelling from the dacpac file while working on this demo. However, you could change it here if you don’t want to keep the original name, which would be, as previously noted, “appdb.dacpac” in all app packages. Once you’ve set things up, you are ready to start the deployment. Aside from the default name for the database, the wizard will take you step to step, normally with no issues.

Figure 17. Click Next to Start the Deployment

During the deployment, the dialog will continually update, showing you progress and the success or failure of each step.

Figure 18. Progress of Deployment

When the deployment completes, you can browse the Databases node again, select the newly created database, and work with it exactly as you normally do with other SQL Server databases.

Figure 19. Completed Deployment of Your Database

At this point, the entire database from your AWA’s SQL Azure server is now replicated on its new home. You will probably make additional modifications. For example, as you can see in Figure 13, the original SQL Azure database includes “xxx?image” tables which you probably not need any longer. These tables were introduced into AWAs to make handling images more efficient. If you have a lot of images in your existing AWAs, fixing this up will require additional strategy and effort. I’ll have to leave that to another time, though.

Summary and Next Moves

There you have it. Three ways to get your data out of the SQL Azure database behind an Access Web App. There are, no doubt, other ways to do it that are less straightforward, but perhaps more familiar to Access developers. However, given the ease and simplicity of the options, we should give preference to them, IMO. I will move all of my own AWAs from the limited SQL Azure database environment behind them onto a full SQL Azure platform. In addition to retaining their cloud-based capabilities, that opens up the ability to manage and enhance them with SQL Server Management Studio (SSMS) just as one would with any other database. That means writing your own stored procedures, views and functions, and all of the rest.

Cost may be a bigger factor. One of the many benefits of the AWA platform was that a single $6.00 a month Office365 license allowed you to create multiple AWAs in that site, each capable of supporting hundreds of users. The cost of a standard SQL Azure account with multiple databases is potentially much greater, although they can scale to support the same number of users, or greater, as needed.

And to reiterate, I see the primary pain point in this transition being that you will have to create a new interface for each of your AWAs. Getting the data out of them is relatively simple, straightforward, and inexpensive by comparison.

My advice, therefore, is to start practicing how to extract your data. Once you have designed and deployed your new interface, migrating your data to its new home for real will be relatively straightforward.

Saying Goodbye to a Not-So-Old Friend

Well, it happened. We had a short, intense, bitter-sweet interlude and now it's over. The cliché rings hollow. "All good things come to an end." Yes, but it was over too soon, too soon.

Here's the official notice We no longer recommend Access Services for new apps. This feature will be retired from Office 365. We will stop creation of new Access-based apps in SharePoint Online starting June 2017 and shut down any remaining apps by April 2018. 

Somehow I don't feel much like talking about it at the moment.

When the Data Goes Down the Drain, It Pours

It seems like paying attention to a particular topic tends to surface many previously unremarked instances of the same thing. So today I also ran across this blog post, Saved by the backup – Do you have a backup plan in place?, by my friend and fellow Access MVP, Anders Ebro Christiansen, also known as the Smiley Coder in online forums.

If you don't have a good backup plan in place, you better have a good exit plan ready.

Just saying.

Back Me Up, Rambo, I'm Going In....

So, the day started with a phone call from a friend and former colleague. Did I have time to go with him to his client’s site to help restore his Access/SQL Server application following a Ransomware attack?

That sounded interesting, so I went along. After all, how hard could it be? A PITA, as it turned out.

We got there. The owner's face had that hollow look which said he’d lost most of his business data—and he has.

 The rest of the story.

My friend migrated the client’s Access mdb to SQL Server Express on a brand-new dedicated computer over a couple of months last summer and fall. Being on a workgroup, not a domain, my friend had asked me to help with the connections back then. We got it all working. The time frame was late October. The last copy of their data was a “development” .mdf my friend used to fine tune the Access FE for deployment from that date.

Guess what, that is also the most recent copy of the data left in the world, and it’s partially made up of sample data my friend used while working on it. (I “think” that is the case, at any rate. Some of the data I saw wouldn’t make sense otherwise, but, under the circumstances, I really didn’t get a good look at it, as you can imagine.)

The client also had a pre-migration mdb with his business data from April of last year.

What happened? A ransomware attack. Somehow the hackers got control of the new box where the BE SQL Server instance was located. All of the working files on it were converted to “.wallet” files, encrypted of course. Well, we didn't see them, so we assume all, or most of them, were encrypted.

The client must have panicked, because he formatted that hard drive and only then called my friend. Oops. It turns out that all of his daily backups of the master .accdb Front End, as well as the .mdf  that contained the business data itself--i.e. the Back End--were right there on that same HD.

ALL OF THEM.

And he had already formatted that HD before he contacted my friend for help.

 

Fortunately for my friend's peace of mind, he had obtained and installed third-party backup software for the SS Express instance, when he turned it over to the client but, unfortunately, the client had set it up to do the backups only on the same drive ... and nowhere else. 

Why the ransomware had not spread to the rest of his network I don’t know. My only previous exposure to such an attack was that it began to spread across the entire network, picking off Office application files as it found them. This one seems not to have gotten off the source computer. Maybe it was because he’s on a Work Group. Maybe the rest of the office computers were turned off at the time--this happened on Saturday afternoon.  In any event, he indicated he had stopped the intrusion at the original source computer. I am not 100% convinced of that, by the way. Let's just say I think it would be unusual for a ransomware invader to honor a network boundary like that.

All in all, though, that business owner is screwed.

• The most recent available copy of his business data may or may not be clean.
• That copy of the data is at least three months old, maybe more.
• It does include at least 6 months more data than his only other copy of the data, so they’ll probably go with it.

Someone in his office has the unenviable task of sitting down with paper files and rekeying months of orders. By my guess several thousand of them. And then there’s identifying the test data and eliminating that….

All because the only backup was on the same HD as the source. And because he panicked and formatted that HD as his first recovery step (well, maybe he disconnected it from their network first; I don’t know what happened or when).

He told me yesterday he had just purchased and connected external Hard Drives for each of his office computers and was in the process of setting up daily backups for them.

Good first step.

I suggested he add a cloud backup plan as well. I think he will.

When you are talking to your clients, do you offer to include a viable backup protocol as part of your service? Do you ask them to sign off when they decide not to accept your backup plan? Do they understand the risks they are assuming if they don't?