Microsoft Office Access and Cloud Computing

Srinivasan Sundara Rajan described “Business productivity thick client cloud applications” in a 5/10/2010 Microsoft Office Access and Cloud Computing post to azure.ulitizer.com:

Microsoft Office Access is one of the  most  popular thick client / client server application development tool  that is prevalent in the enterprises today.  Its simplicity to use, fitment for quick departmental applications and ability to be home grown by the business users without dependency  on IT and  still a  poor man's adhoc  reporting tool,  all makes MS Access a  permanent place in most enterprises.

The latest incarnation of this application development and reporting tool has the following  benefits:

• Build a relational database for small applications much faster using wizards
• User interface forms and reports using a common look and feel of Microsoft Office
• Automation features to avoid manual coding
• Ability to act as Information Integration Engine which can connect disparate data sources

Mostly Microsoft Access is used as  a desktop thick client application and with increased usage of the Cloud Platform it would be helpful to analyze the options for Microsoft Office in Cloud.

Microsoft Access In Office 365
From older version of Microsoft Access we can  move the data to Microsoft Access 2010, and then publishing the database to Access Services on Office 365, you can make your data accessible to all of your colleagues wherever they are. Perhaps more importantly, your database will be better able to grow as your needs change.

We can use Access 2010 and Access Services, a new component of SharePoint, to build web database applications. Access 2010 and Access Services provide a platform for you to create databases that you can use on the Web. You design and publish a web database by using Access 2010 and SharePoint, and people who have SharePoint accounts use the web database in a web browser.

When you publish a web database, Access Services creates a SharePoint site that contains the database. All of the database objects and data move to SharePoint lists in that site.

SSMA to SQL Azure
Microsoft SQL Server Migration Assistant (SSMA) is a toolkit that dramatically cuts the effort, cost, and risk of migrating from Access to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Azure. SSMA 2008 for Access also supports migrating to SQL Azure.

SSMA provides a Wizard-like interface that allows us to:

• Create a Migration Project in SSMA with SQL Azure as the destination
• Allows us to choose the target schema for migration
• Migrate the tables and data with the convert , Load and Migrate command
• Provides ability to verify the migration

This tool provides an out-of-the-box solution for migration of Microsoft Access databases to Cloud.

Microsoft Access As Thick Client Cloud Consumer
If the  data resides in SQL Azure, then Microsoft Access can continue to be a thick client and connect to a Cloud database using the linked tables option.

With Microsoft Access 2010, You can create an external data connection to a Web Service. For example, you can create a Web Service data connection to your enterprise Business Data Catalog and get business data. After you create a Web Service data connection, you can link to it as you would any other external data source. If the Web Service accepts parameters, you can change the parameters when you create a linked table by using the Web Service data connection.

The following are the general steps  in  making Microsoft Access  consume Cloud Web Services as a thick client.

• Obtain a Web Service data connection file
• Install the web service data connection by using the connection file
• Create a linked table by using the installed web service data connection

Summary
Microsoft Office Access continues to be most productive end user reporting tool and in the past decade numerous enterprise class applications have been written using that tool.

We cannot discount the immense value Microsoft Access applications brought to end user analytics, ad hoc reporting and complex client side calculations. The biggest draw back  for the Microsoft Access applications  have been hitherto its storage whereby the Jet engines which is the underlying database have historically known for space limitation.[*]

While over the period the limitations have been reduced to a great extent, the above mentioned Cloud Migration options makes Microsoft Access continuously play a role in the enterprise end user productivity applications.

With the support for  Microsoft Office mobile  applications, having  Microsoft Access as a thick client which can consume  cloud data may be a  worth while option for future productivity applications.

Srinivasan Sundara Rajan works at Hewlett Packard as a Solution Architect.

* Historically, problems have been encountered with database corruption with more than about 20 users updating records simultaneously, not “space limitations.”

For more details about Access 2010 Web databases and as a RAD front-end for Microsoft’s SQL Azure cloud database, see my Three Microsoft Access 2010 Webcasts Scheduled by Que Publishing for March, April and May 2011 post updated 5/3/2011.