All you need to know about the Access Services 2010

Access Services 2010 is the new feature to host the entire Access databases within SharePoint 2010. It is only available in the SharePoint 2010 Enterprise Edition. This article not only covers the New Features in the Access 2010 and overview of the Access Services but also covers the benefits (both for business and IT), limitations, architectural details, typical Access Services Application Lifecycle (development, deployment, management process), and migration considerations for the Access 2007 (or earlier) databases to the Access Services 2010.

Access Services 2010 is the game changer in the Access 2010 space and may open new avenue for the departmental no-code database applications.  Hold tight and enjoy the reading.

Improvements in Access 2010

  • Integration of the Microsoft Office Backstage as part of the Microsoft Office Fluent User Interface
  • Web Database Format, Support for the Web Compatibility Checker, and Publish to Access Services
  • New Controls – Web Browser Control, Navigation Control ( Replaces the Switchboard View from previous version to navigate the Access objects)
  • New Data Type – Calculated Column Based on Expressions, much better improvements over the calculated values based on the queries
  • Support for the Office Themes for the Professional look and feel
  • Application Parts – Reusable forms, reports, or any other Access objects.
  • Data Type Parts – Custom Grouped Field Types, create your own group data type with the collection of fields to reuse in other tables.
  • Form and Report Designer – Live Data Preview , Improved Conditional Formatting on Reports & Forms (Conditional Formatting was added in the Access 2007)
  • Expression and Query Builder Intellisense
  • BDC Integration
  • Macros
    • Revamped Macro Designer
    • Form Macros/ UI Macros – Introduced in the Access 2007, Declarative Actions along with VBA Code for the UI logic, Additional Declarative Actions for the interactive forms programming in 2010
    • Data Macros – Works similar as SQL Server Triggers, Reusable Macro logic to create events at the table level (form level UI Macros are not reusable), Supports Before (Before Change, Before Delete) and After (After Insert, After Update, After Delete) events
    • Named Macros – Data level macros not attached to any events and can be called from the UI
    • Macros are XML based and easily portable from one environment to another
  • Ability to export to PDF and XPS files as a built-in feature (It was released in Access 2007 SP2) – Allows users to export forms, reports and datasheets to PDF and XML formats for easy distribution
  • Ability to connect to a web service as an external data source – Linked table to the Web Service Interface
  • Save as Template for creating access database templates, Download the Sample Templates from the Microsoft – http://office.microsoft.com/en-us/templates/CT010214400.aspx
  • Last but not least, Access 2010 (even 64-bit edition) has a limit of 2GB size.

  SharePoint Access Services 2010 – Benefits, Limitations, and Architectural Details

  • Access Services are nothing but Data driven web applications called web databases hosted in the SharePoint 2010 environment
  • Access Services Benefits and Usage
    • Balance between business agility and IT manageability
    • No Install Solution –  Web based Access
    • Improved Collaboration – Share and collaborate on the declarative RAD no-code web based team databases.
    • Centralized Data Storage – Single truth of the Application Logic and Data
    • Improved Reliability, Scalability, Security, and Manageability – Central IT management
    • Improved Backup/Restore – Access databases are part of the SharePoint Backup and Restore Process
    • Increased Concurrency – Locks the database at the object level, instead of database file level resulting in fewer conflicts
    • Access Applications Standardization using Templates – IT can configure/support services, start building out the web database standards, and let end-users manage it
    • RAD No-Code SharePoint Applications – Useful to build web based powerful RAD applications which supports query engine that can perform complex joins, filtering, aggregations, and parent-child relationships between lists.
    • RAD Reporting Tool – Useful as reporting tool to generate the customized reports (RDL files) based on SharePoint Lists hosted in the SharePoint.
  • Architecture Details
    • Access Service is a middle-tier service, which handles the  query processor and data access layer. It also manages communication between Access Web Application and SharePoint Content Databases.
    • Support for the Relational Database Data Integrity using SharePoint Lists Enhancements Infrastructure – Lists Relationships, Cascade Deletes, Unique Constraints, and Data level validations at the List and Item Level
    • Improved Performance/Scalability – Offers caching layer that addresses the limitations of the maximum number of list items that a query can return at one time by ignoring the List View Threshold. Improved Performance by allowing large record sets in the SharePoint List. Access DB supports more than 100K records. SharePoint Lists has filtering or sorting restrictions if you have more than 5K records in the list because content database blocks large calls. Access Services Data Sheets retrieves all the 100K SharePoint list items in the 2K records in chunk and later stitches them together in the ADO.NET record set in memory Layer as cached data for the performance. This allows sorting and filtering against cache instead of content database on the more than 50K records in the Access datasheet. Access datasheet View is rendered by the Project Datagrid object and it is smart enough to bring only 200 records at a time in the browser. With the smart navigation using AJAX experience, as you scroll down, it will bring additional records from the cache without affecting user experience. Access List Views are continuous forms supports paging, sorting, and filtering, and behaves same way as Datasheet View by retrieving data from the ADO.NET cache in the middle tier.
    • Concurrency Conflicts – Different users can make changes to different objects or different data items in a list without causing conflicts. When data conflicts do occur, a conflict resolution wizard enables the user to choose which version of data items to preserve. For object conflicts, Access provides the name of the user who made the saved changes and creates a renamed backup copy of the local object before downloading the other user’s changes.
    • Source Control – While downloading the database locally on file system,  Access client downloads the entire database only when a user doesn’t have local copy. Access fetches only objects or data items that have changed.
    • Only Web databases are supported in the Access Services. Web database supports two types of Access Objects – Web Objects that can run either in browser or Access Client and client objects (non-web objects) that can only run in the Access client. All design changes for both web and client objects must be made in Access Client. It is important to remember that client object definitions are published and stored in the SharePoint but they can accessed and executed during runtime only in the Access client.
      • All linked tables are client tables. Only client objects like reports, forms, macros can work with linked tables. Linked tables aren’t available to the web objects.
      • All the reports ,forms, and macros with VBA code makes these objects client objects.
      • All the web objects like reports, forms, and macros would work only with the web tables (Note web tables are Access DB tables fully compatible with SharePoint lists)
      • When working in the Access client and connected to the network where Access Service App is running, data and design changes to web tables automatically synchronize with the server.  When disconnected, Access client works with local copy and doesn’t allow changes to the tables. When reconnected, Access notifies users to synchronize and resolve any conflicts. Design changes to objects other than web tables synchronize only when users explicitly request sync by clicking Sync All button.
    • Forms and Reports gets rendered in the Data Form Web Part
    • Data Sheets rendered in the Project Datagrid JS Object to support large data sets
    • Reports are rendered in the Report Viewer Web Part installed with the SSRS Add-in
    • Access Service AJAX WS provides AJAX experience on the Access Web Applications
    • Better Administrative Control – Uses the OOB SharePoint Security for the Security Layer 
    • Managing and Fine Tuning the Access Services – From the Central Admin -> Manage Access Services Settings e.g. Max columns/rows per query, Max sources per query, Max calculated columns per query, Max order clauses per query, Max records per table in the join, Max sessions per users etc.
  • Limitations
    • Advanced SharePoint functionalities like Site Content Type, Metadata/Taxonomy, BCS are not supported in the Access Services 2010
    • Advanced Access functionalities like Linked Objects (Linked Tables, Linked SharePoint Lists, or BCS Links), VBA/Modules, Action Queries, Traditional Full UI Macros are not supported in the Access Services 2010
    • SharePoint Designer can’t open the Access Services Site. Any modifications in the Access Services Site requires Access 2010 client.
    • Access Services Application adopts look and feel from the Access Client. It  can’t be configured to inherit the master page branding or color scheme from the parent site collection/web application.
    • There is no site actions menu from the Access Services Web Application.

  Prepare the Environment to host the Access Services 2010

  • Access Services is installed part of the SharePoint 2010 Enterprise CAL
  • Make sure Service Infrastructure is enabled
    • Manage Services on Server – Access Database Service is started
    • Manage Services Applications – Review the Settings, No need to change any performance tuning settings
    • Manage Web Applications – Make sure application is associated with the Access Servic
  • Make sure all the features are activated
    • Central Admin -> System Settings -> Manage Farm Features -> Access Services Farm Feature
    • Central Admin -> Manage Web Applications -> Select Web Application hosting the Access Services Site -> Manage Web Application Features -> SharePoint Server Enterprise Web application features
    • Site Collection -> Site Settings -> SharePoint Server Enterprise Site Collection features
    • Site Collection -> Site Settings -> SharePoint Server Enterprise Site features
  • To the Access Reports on the web, Access Services requires

  Typical Access Services Application Lifecycle (Development, Deployment, and Management Process)

  • Development
    • Create Access Database using Access 2010 and Start with “Blank Web Database” or OOB/Custom Web Database Templates
    • Design Tables, Queries, Forms, Queries, Reports, Macros/Business Logic, Navigation, and Style/Theme
  • Deployment from the Microsoft Access Client using the Access Services “Publishing” Model
    • From the Backstage -> Publish to Access Services.  If there are errors during the Publishing Process, “Web Compatibility Issues” table will be created in the Access database for further review.
    • Publishing process creates a brand new SharePoint Site at the specified Path. Access Services Web Site is an isolated environment to host the Access database information.
    • Transformation Process to SharePoint (Client Objects  -> Web Objects, full fidelity between client and web objects)
      • ACCDB -> SharePoint Site
      • Access Tables and Data -> SharePoint Lists
      • Queries -> CAML Entries in the System Tables Rendered through Query Processor
      • Access Forms -> ASPX Pages using the Data Form Web Parts stored in SharePoint Document Libraries
      • UI Macros -> JavaScript Attached to SharePoint ASPX Pages
      • Popup Forms -> Floating Divs
      • Design Themes -> CSS Style Sheets
      • Access Reports -> RDL files (Requires SSRS 2008 R2 Add-in for the SharePoint 2010)
      • Data Macros -> SharePoint Workflow Actions
      • Modules, VBA Code Library -> It stored in the Access Services but not accessible/supported through browser interface. You can access them through the Access 2010 client.
      • Linked Tables, Linked SharePoint Lists, or BCS Links -> Linked Table Definitions or BCS Links Definitions are stored in the Access Services but not accessible/supported through browser interface. You can access them through client. Client objects in the Access Services will access to the linked table definitions but web objects in the Access Services can’t access the linked table definitions. Until Data in the SharePoint Lists (Natively as tables in the Microsoft Access), Web Objects in the Access Services can’t talk to data.
    • After the publish, what happens to the Accdb hosted on the server? Answer is during publishing process, Access 2010 Database Client stay as it is on the desktop or server in Accdb format. Access Services creates the backup copy of the Accdb on the server/desktop and makes the published Accdb as connected Access database to the Access Services Web Application. If you access the connected database after weeks or months from the desktop or server, Access DB client will sync/update the database with Access Services Site.
  • Deployment from the SharePoint without Access Client
    • Save the Access Database as a Template from the Backstage in Accdt format. 
    • Browse the SharePoint Site Collection’s Solutions Gallery and upload/Activate Accdt file in the SharePoint solution gallery, SharePoint is smart enough to treat it as a solution package file.
    • Visit the New Site Creation Section and Create a new Access Services site based on the custom Accdt web template database.
    • Use this approach if the person creating the new site based off the Access template need not have Microsoft Access installed on their machine or if you need to standardize the Access Services Web Database templates in your organization to create more than one sites based on same template.
  • Management  of Access Applications – To Modify Access DB After its been published using the Access Services “Sync” Model
    • Best Option: From the Options Menu, Click on the “Open In Access” to download the “Connected Microsoft Access” database for further modification
    • Any data changes on the client syncs to the web in real time. Any client changes of  the Table Schemas, Forms, Macros, Queries, reports doesn’t sync to web automatically. You have to use “Sync All” from the backstage to sync interface changes.
    • Individual objects in the Access Database on client can be modified independently and resynced to the SharePoint without affecting other objects. This allows better shared design and concurrency while updating the Access object design.
    • Please note that Access Services runtime environment is both browser and client but design environment is only Microsoft Access client. If you need to make changes in the reports or forms or schema, download the access db on the client, make the changes, and sync back to the SharePoint.

  Typical ways to integrate the Access database with the SharePoint 2010

  • MOSS 2007 supported sharing the Access DB on the SharePoint by uploading Access DB on the SharePoint Document Library just as any other documents and required Access 2007 on the user’s machine.
  • Three major options available
    • Good Solution – Basic Integration in the SharePoint Foundation 2010 (without Access Services 2010) 
      • Requires Access 2010 Client software must exists on the end-users machine.
      • Requires Access Database data must be compatible with the SharePoint.
      • Data and UI is centrally stored in the SharePoint and there is only one version of truth. Data is stored in the SharePoint Lists and UI is stored in the Document Library.
      • From the Access Client DB, visit the Database Tools Tab and Export the Access Tables to the SharePoint Site as a Lists using “SharePoint” ribbon button and Upload the Access DB to the SharePoint Document Library. 
      • Lock is per database. When multiple users are making design changes simultaneously, last person who upload the changes back to the SharePoint overwrite the other user changes. Make sure versioning and check out is required to avoid concurrent changes while making design changes.
      • Using this option, if you open the database read-only from the SharePoint, you can only change data in the linked tables from the Access Client. To make design changes, download the copy of the database to the hard drive, make changes, and upload it back to the document library replacing the previous version of the database. This option doesn’t provide the web UI on the SharePoint to view the data.
    • Better Solution – Supported as Access 2010 Services, Requires SharePoint Server 2010 Enterprise CAL
      • Doesn’t Require Access 2010 Client software exists on the end-users machine.
      • Requires Access Data and UI must be compatible with the SharePoint
      • Data and UI is centrally stored and there is only one version of truth. Access Database is published to SharePoint and hosted in the SharePoint as Access Services Web Application.
      • Lock is per object, as opposed to per whole database file. Using this option, while one user can make report changes, another user can create or modify the new form, and both changes can be synced back to the Access Services.
      • This will migrate the data as SharePoint lists and UI components as a client and web objects in the Access Services. Most of the Access Objects like web forms, reports will be transalated as web objects and can be accessed through the web UI. Many of the other objects like VBA code will be hosted in the Access Services as a client objects and will require Access Client software to view them.
      • Using this option, to make design or schema changes, save copy of the database to the hard drive.
    • Hybrid Solution
      • This scenario will cover most of all the Incompatible Access DBs in your organizations especially database applications created/maintained using the Access 2007 and prior versions.
      • What if you don’t have data compatible with the SharePoint? Can you share the Access database with SharePoint with above two solutions (good and better solutions)? Answer is No, Access DB must contain the SharePoint Compatible Data. Data must be compatible with SharePoint Lists. If any of the data can’t be moved to list, publishing can’t happen unless data is exported to separate database or changed to be compatible.
      • Instead use hybrid step by using the Linked Tables in the Access Services
        • Move any of the Incompatible Access Data to the SQL Server or another access db (from the database tools tab -> SQL Server or Access). This will move all the incompatible data to the access or SQL DB and keep the incompatible UI in the existing Access DB.
        • Next step is publishing the Access database with compatible data components and UI components to the Access Web Services. Client objects in the Access Web Services will reference to the external SQL Server for data and client objects can remain in the database without interfering with publishing process.
        • Later on, gradually, fix the incompatible data. Migrate the external data to the SharePoint lists and generate the web objects in the Access Web Services for full migration. To migrate the external data in the SharePoint lists, import the previously incompatible external SQL server or access db tables into the Access DB tables with valid UI and fix all the browser compatibility issues and republish it to the Access Web Services
  • Access Services feature comparisons supported in the SharePoint Foundation and SharePoint Enterprise CAL
    • Data in SharePoint Lists – SharePoint (included in the SharePoint Foundation)
    • Centrally Deployed Interface – SharePoint (included in the SharePoint Foundation)
    • Collaborative Design – Access Services only (Requires Enterprise CAL)
    • Web Forms in the Browser – Access Services only (Requires Enterprise CAL)
    • Web Reports in the Browser – Access Services only (Requires Enterprise CAL)
    • Server Macros – Access Services only (Requires Enterprise CAL)
  • Going forward, future direction should be Access 2010 and Access Services 2010
    • Start all the client Microsoft Access databases with the web database template to make sure its always compatible with SharePoint and Access Services.
  • How can you lock down the Access Services Publish Locations?
    • Client Level – Lockdown the registry key – HKCU\Software\Policies\Microsoft\Office\14.0\Access\Security\Allowed Publish Locations
    • Server Level – SharePoint security, define permission levels, Design, Contribute and Read only, Designers and above has permission to create the access web applications, contributors can read and write data, Users with read-only permission can only read data.

  Access 2007 to Access 2010 Migration Considerations for the Access Services 2010

  • Access 2010 will share the same native file format as Access 2007 – ACCDB format.
  • For Pre-Access 2007 version, follow the guide – “Transitioning Your Existing Access Applications to Access 2007” to upgrade from MDB to the ACCDB version – http://msdn.microsoft.com/en-us/library/bb203849(office.12).aspx
  • Access 2007 Deprecated Features
    • Microsoft Calendar control (mscal.ocx) – Use Date Picker control as an alternative
    • Microsoft Replication Conflict Viewer
    • Option to export a report as a Snapshot file
    • Data Access Pages (DAPs
  • Rationalization Process – Analyze Access Databases in the Organization
  • Migration Process – Convert Access 2007 Databases in the Web Database Format
    • Look at Third-Party Web Conversion Service Offering:  http://www.access2010converter.com/index.html
    • Open the Access 2007 or Previous Versions of DB in the Access 2010 client
    • Fix the Access 2007 database objects web compatibility issues – Run the Web Compatibility Checker on the Access database to conforms the web compatible schema to meet the SharePoint requirements. This will create the “Web Compatibility Issues” table in the Access Database. The errors in table are explanatory with which table, which control you have and there is also a link for each error where you can also get info about what to do.
      • Analyze the Web Compatibility Issues Table , Fix the compatibility issues, and rerun the Web Compatibility Checker until Access database is web  compatible with Access Services and ready to be run on SharePoint.
        • Common Issues – Formatting, Data type, Validation Rules, and Structural Issues
        • Invalid Names -Tables and Columns Name should not conflict with reserved words or illegal characters. Changing the column names would not change all the Access objects. The Access Name AutoCorrect feature will propagate the changes to the queries and bound controls as long as objects are open at least once and saved. Please note that VBA Code and expressions are not automatically updated.
        • Compound Indexes – Indexes based on multiple columns are not supported over the web. One workaround for enforcing uniqueness on multiple columns are use the BeforeChange data macro.
        • Declarative Referential Integrity – Referential Integrity configured using the Relationships window that are not associated with a Web-compatible lookup are incompatible with the Web. You have to delete all relationships not based on lookups and create them using lookup wizard. Tables with relationships that aren’t implemented in lookup can’t be published. These lookups must be based on numeric data types.
        • Composite Keys – Composite keys are not supported in the Access Web Databases. If you have a database with composite indexes, you have to create a new primary index with AutoNumber type and create a data macro to preserve the uniqueness of your fields making up the composite key. Here is the article to write a data macros to resolve these issues – http://blogs.msdn.com/b/access/archive/2010/02/18/composite-keys-in-web-databases-through-data-macros.aspx
        • Primary and Foreign Text Keys –  Access supports primary and foreign keys on the text or dates.  SharePoint Supports relationship on numeric keys, not on the text based keys. Web database Primary key must be long integer. Easiest way to fix this add an auto number column to the parent table and add a corresponding long integer column in the child table as foreign key. You can then use the update query to sync the foreign key values in the child table. You would also need to delete the old relationship and create a new one using the lookup wizard. Make sure all other objects like queries, reports, forms are updated to use the new autonumber column before deleting the old text keys and relationships. You can also use data macros discussed in the Composite Keys section.
        • OLE data types needs converted into attachment data types. Update all the forms and reports where OLE data types were used for the pictures. Also, SharePoint supports only one attachment column per list so, make sure you have only attachment field per table in Access.
        • Tables with the recursive relationships to manage parents and child data in same table are not supported in SharePoint.
      • Please note that web compatibility checker doesn’t check all the database issues. Many of these issues may cause publishing fail. Any errors during the publishing process, Access logs the issues in the “Move to SharePoint Site Issues” table.
        • Incompatible data (e.g. invalid date, hyperlinks etc.) may cause publishing process fail.   Web compatibility checker checks data schema but  doesn’t check data values. Some of the Number/Currency/Date Time formats are not supported in Access Services and here is the guidelines around the supported format.
          • If you have a Number field, make sure it is formatted as General Number/Standard/Percent.
          • If you have a Currency field, make sure it is formatted as Currency/Euro.
          • If you have a Date/Time field, make sure it is formatted as General Date/Short Date. 
          • If you have hyperlink field, make sure it as fully qualified URL (relative URL doesn’t work) and URL length is less than 255 characters.
        • VBA code – It would require migration strategy to rewrite VBA code in the Data and UI macros. Please note that VBA code doesn’t cause publishing fail and require Access client to access them from the browser.
        • Invalid Expressions – Invalid Expressions entered manually without Expression Builder may cause publishing process fail. Invalid expressions in data schema definitions (e.g. validation rules or calculated column) will cause the publishing errors but Invalid Expressions in the web forms, reports, and queries can be found out only during runtime after publishing has been succeeded.  Access Services logs the compilation issues in the USysApplicationLog table.
    • Once Access 2007 objects are converted to the Access 2010 web legal format, Access 2007 (now converted to the Access 2010 web legal format) database is ready to publish to the Access Services. However, you can’t open your objects (Forms/Reports/Reports/Modules) other than tables in the browser if they are using the VBA code since they are still client objects. These client objects will reside in Access Services and Access s clients can open the forms and reports but they will not render in the browser.
    • For full web compatibility, plan for new web objects like navigation (tabbed interface), form and reports for the web interface with the new themes and look and feel, etc. You have to create new web objects if you want to open forms and reports in the SharePoint. You have to set the Web Display Form which will be displayed as splash page when you open your access application in the browser. To set your Web Display form go to the Backstage | Options | Current Database page and select a form from the Web Display Form dropdown menu.  Typically, it should be the main navigation form.

 Access Services 2010 Resources

Advertisements
This entry was posted in SP2010 Architecture, SP2010 General. Bookmark the permalink.