Wednesday, April 26, 2017

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.

Thursday, April 20, 2017

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.

Sunday, April 2, 2017

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.