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.