Thursday, January 9, 2014

The Quasi-Definitive Access Web Database Deployment Guide

Last year I wrote a series of posts about InfoPath deployments (link) and lamented how there's next to nothing on the internet about moving no-code solutions between environments. There's bits and pieces, but nothing comprehensive. Well guess what, the same is true for Access Web Databases (aka Access Services). So saddle up kids, ‘cuz here's another quasi-definitive deployment guide.

As I wrote in my InfoPath guide, I'm calling this post the quasi-definitive guide because this will help you with about 85% of Access Web DB deployments. This post will only cover SharePoint 2010.

There are two kinds of scenarios this post will cover: new web databases and existing web databases.

New Web Databases

Deploying a brand new Access Web DB is super easy. I'm going to assume that you've already ran the compatibility checker. If not, you may be in for a world of hurt. If you haven't run the compatibility checker, you're going to most likely run into some cryptic error messages. My rule of thumb is don't freak out. 8.7 times out of 10 if you fix the first error the rest will go away the next time you run the checker.

Back to the matter at hand here.

Whether you're deploying from a local environment or to a new site in Production, first save the web DB as a local database

  1. File>Save & Publish>Save Database As
  2. Select Access Database>Save As
  3. If you're developing locally, bring the local copy to a machine with access to Production
  4. Then publish the database via Access (File>Save & Publish>Publish to Access Services)
  5. Enter the SharePoint site it'll reside beneath and a site name for the web DB

Existing Web Databases

Deploying an upgrade to an existing Access Web DB is a little trickier and I want to talk about some issues I ran into before we get to the step by step.

When I exported my Production data via Access and imported it to my Development environment, all of my auto-number fields maintained their original ID from Production. Sweet! However, when I published the database all of the IDs changed to be new values. No me gusta! I tried exporting Production data via Excel and pasting it in, but the same behavior persisted. I found a thread saying that auto-number should persist when export/import via Access but I couldn't find this to be further from the truth.

Because I wanted to deploy in one fell swoop, I tried a few other approaches. For clarity's sake, let's call this table Projects.

One approach included copying and pasting the Production data from Projects into a new table in Development named ProductionProjects. The new table has my updated table structure. I didn't remove any columns from Projects, only added new ones and added validation and required fields.

Then I deleted my Projects table (the development one) and renamed the table from ProductionProjects to Projects. The major problem with this approach is that when I published, the Projects table went AWOL in both the client app and in the web DB. After some digging, I found it in the site collection recycle bin. I restored it and could access it via some URL hacking, but it couldn't be re-associated with the Access client. The scary part was it wasn't hidden in the client app; it was a victim of composite development lost in the SharePoint ether.

One other approach to this problem is to export Production and import it into Development and create a new column and populate it with the Production auto-number values. However this requires the creation of a new column and updating the forms.

However, I'm going to go with the approach that Production is sacred. I want to keep my auto-number and I really don't want to be creating fields and playing around with my forms at the last minute. By using this approach, you can import all of my assets into the existing Production environment EXCEPT the table where auto-number is a key field. So let's get into this.

  1. During the development cycle, for any table with auto-number, document any new fields added, as well as any new validation rules and required fields. These will be manually added to Production.
  2. Save a local copy of both the Development web DB AND the Production web DB (File>Save & Publish>Save Database As>Save as Local Database)
  3. If you're developing locally, bring the local copy to a machine with access to Production
  4. Open the Production web DB in Access
    Note: This is not the backup taken in step 2. That is a CYA backup in case things go awry. Go to the existing site in Production and click Options>Open in Access
  5. In the Ribbon, click the External Data tab and click Access
  6. Select the local copy of the Development DB
  7. Select all of the Development assets with the exception of table(s) where auto-number is a key field
    Note: Don't worry about overwriting objects. Objects with the same name are appended after being imported. For example, frmHome becomes frmHome1.
  8. In your table(s) with auto-number, consult your development notes and add the new fields and implement any validation and required rules. Make sure the names exactly match Development's.
  9. Delete the Production files that will be replaced with the ones imported from Development
  10. Rename imported files to their new names. Example: tblStatus1 becomes tblStatus
  11. Update the default form (File>Options>General Database)
  12. Publish the database

I'll admit that other than a clean deploy between environments there really isn't a pretty solution here. When you import everything into Production except the table you care the most about because it has all the latest and greatest gee-whiz functionality, it's very easy to overlook a validation rule, or a column and throw something off in the application.

My advice is to plan for hiccups like this in advance so when it comes time to deploy there aren't any surprises nor comprises to data integrity. It also goes without saying that after you deploy, be sure to test both in the browser and the client app.