Wednesday, July 19, 2017

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

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

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

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

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

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

The tool?

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

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

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

You can find it here: Field Effect LLC website


Sunday, June 4, 2017

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

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

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

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

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

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

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


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


Open the accdw file which controls the Access Web App.


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


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


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



Allow connections from any location and enable Read-Write connections



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



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


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


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


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



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

Guess not.


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


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

Wednesday, May 17, 2017

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.

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.