Develop Advanced Access Web Databases and Publish to SharePoint
These are some notes from a REALLY great session today at the SharePoint Conference 2009 in Las Vegas delivered by Ryan McMinn, an Access Services guy at Microsoft. Lots of good, detailed information here. The notes are as organized as I can make them while I’m sitting here in the room, but they will of course not be as polished as I’d like them to be. I’ve decided to err on the side of more information – less polish.
This is a follow-up to a more basic session on building apps yesterday (which I didn’t attend). Access has long been valued by business users for its rapid application development capabilities. It’s also been hated by many IT people for its lack of security, management, and disaster recovery. Since the 2007 version, Access and SharePoint have been friendly with each other. Now however,fully integrating Access via SharePoint 2010’s Access Services addresses IT’s concerns and actually makes it more powerful for business users at the same time.
A big emphasis has been put on providing database templates to help people get started, including community templates uploaded to Microsoft from you and me. One of the OOTB ones is a ‘Web database’.
Miscellaneous Access Client Improvements
Building table schema in datasheet view is easier now, as the column headings allow you to pick the data type on the fly.
Data Models are groups of fields you can create and insert as a group.
Data Types allow you to insert common calculated fields along with the fields they reference.
The Macro writer has been amped up to be almost like working in the VBA editor, but still maintaining the low-security-required environment for web publishing. This adds enhanced capabilities to do full If Then Else structures, comments, parameters with full Intellisense.
The new Web Browser Control allows you to show web content in your forms by parameter-based URLs.
Publishing to Access Services
You do this in Backstage (Office menu > Info; or Office menu > Share)
Tables turn into lists, forms turn into aspx pages, reports turn into rdl files (Rptg Svcs), and macros turn into workflows.
It creates all of this in its own sub-site.
Full web functionality is only available on native Access lists that are moved up to SharePoint. External lists are usable in the full Access client, but not via web forms. This is true even if you are leveraging BCS to bring that data into SharePoint.
You can also save your database design as templates that get saved into SharePoint as wsp solution files.
Macros are great for:
Aggregates (doing them this way gives better performance that queries)
There are Before * data macros that trigger before the database is updated. They are meant to be quick, and are good for validation.
After * data macros trigger after the change is committed to the database. They can be much longer, looping, firing other macros…
Named data macros are not triggered automatically, but are called by other macros or button clicks. Functionality is expanded like the After * data macros.