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.
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.
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|
|Figure 2. Convert Linked Tables to Local Tables|
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"|
|Figure 6. The Export Has Started|
- 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|
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 dacpacDacpacs 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|
|Figure 10.Saved Location for Your App Package|
|Figure 11. Find and extract the dacpac in your renamed app package|
|Figure 12. Extracted and Renamed dacpac|
|Figure 13. Connect to Your Target SQL Server|
|Figure 14. Select Deploy Data-tier Application from the Shortcut Menu|
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|
|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|
|Figure 18. Progress of Deployment|
|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 MovesThere 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.