The WorkerThread Blog

we know stuff so you don't have to

Thoughts on Access Services in SharePoint 2010

Posted by workerthread on January 16, 2010

Back in November last year I gave a presentation to the UK Access User Group  on Access 2010 and in particular how it works with Access Services in SharePoint 2010.  I’ve been meaning to put something together on my thoughts on Access Services, both good bits and not so good.  Last week Robert Bogue wrote an interesting piece about Access Services and how he sees key usage areas.  Robert sees one main area of interest being as a reporting tool for SharePoint content, even content not originally created in Access.  I’m not sure I agree with him here, for reasons I’ll explain below.

First let me say that the Access team have put some clever features in Access 2010.  As with Access 2007, the emphasis is on a well-behaved Office application where customisation and development is ideally done without coding in VBA.  To this end, the Access macro editor has been completely re-designed, and now includes data macros which allow you to create events at the table level (similar to triggers in SQL Server).  There are also some interesting new navigation features which make it easy to create a modern looking application for users to find their way around.  Same with forms and report design – each of these has a “layout” design mode which is mandatory if you want to design for Access Services.  Using layout mode means designing forms and reports in pretty much the same way as you would an HTML table, or an InfoPath form.  There is a new option to create a “web database” which disables any Access feature which won’t be compatible with Access Services.  There’s also a compatibility checker which reviews all of the objects in your database and highlights anything which may cause problems when publishing to Access Services in SharePoint 2010.  At the bottom of this post I’ll include some links to videos and getting started tutorials which you might want to try out yourself.

Robert mentions the fact that Access 2010 web database reports are published up to Access Services using RDL (report definition language).  And in fact you don’t need to install full-blown SQL Server Reporting Services to get this to work – there is a reporting services add-in for your SharePoint 2010 server which you can install from a download here.  This can be run in local mode when using Access Services as explained here.  Once you have published your Access app, reports work in pretty much the way they would in the full version of Reporting Services, including pagination and report export options for PDF, Excel etc as you can see here:

WebDBReportOptions

Web database applications are also available as SharePoint site templates.  If you have installed the beta of SharePoint 2010 you will see several of these in the available templates list, here’s an example:

SPWebDatabase

When you provision your new site it will look very much like it would if you had published it from Access – and you can also further customise the site in Access 2010.  There are similar templates provided in the current Access 2010 beta, so you can also try it out by using one of the Access templates to create your web database and taking a look under the covers at some of the design and macro coding techniques used (no VBA of course!).

And yet, and yet….

There are still several reasons why Access services might not be that attractive to a lot of SharePoint users.  First and foremost, as Robert says, Access Services is a SharePoint 2010 Enterprise feature, which will bump up your licence costs considerably.  And the appeal of Access Services as a SharePoint reporting tool may be diminished by the fact that SQL Server 2008 R2 includes SharePoint lists as a data source out of the box.  This means that you could install SQL Server 2008 R2 and integrate with any version of SharePoint including SharePoint Foundation 2010 (Windows SharePoint Services 4.0).  From what I have seen so far SQL Server 2008 R2 will also happily connect to SharePoint Server 2007 lists and will allow you to deploy reports to multiple SharePoint zones, so even if you aren’t considering a SharePoint 2010 upgrade you may want to consider the R2 release of SQL Server 2008 for reporting.

Then there’s the degree to which a published Access web database is locked down.  Most of the standard SharePoint 2010 site settings options are missing in the published site – if you want to continue customisation you need to do this in Access and re-sync changes.  Here is the standard menu of options:

WebDatabaseOptions

Also, the look and feel of the Access site will be inherited from the Access database, not from your (possibly) extensively re-branded master page.  When I did the user group presentation, this was something that several attendees who also had SharePoint involvement were concerned about.  I DID notice that it the _layouts/settings.aspx and _layouts/viewlsts.aspx pages were still accessible though, so it was still possible to see all of the site settings options that had been hidden – not sure you’re meant to do this though as it could make further Access customisations difficult and it may be that this is locked down further in the final release.

As someone who has been doing a lot of work with SharePoint over the past three years, I would also have liked to see some tighter integration with standard SharePoint stuff.  For example, if you have a web database which assigns items to users (extremely common in SharePoint) I would want to use alerts, the out of the box personalisation features that we take for granted (custom views that use a [me] filter for example).  I couldn’t find any easy way to do this with an Access Service database.  Equally important for anyone thinking of trying this out, a published Access web database can’t be opened in SharePoint Designer 2010.  So if you want to use any of these features and you are moving data from Access your best bet could be to migrate the Access tables to SharePoint lists in a normal SharePoint site, then do your additional customisations rather than using an Access Services web database.  I can understand the reasons why Access Services doesn’t allow this (it needs to retain Access client fidelity) but it kind of eliminates a lot of the reasons why you want to use the SharePoint platform in the first place.  I suppose what I’m really looking for is a way of publishing the web database to SharePoint then disconnecting from the original Access app so I can continue my customisations on the SharePoint side.

Personally I think it’s unlikely that many existing, complex Access apps will be migrated to Access Services as there will be too many incompatible features.  It’s possible that some smaller departmental applications might get migrated, but this still require support from the IT Department which I’m not sure exists in lots of places.  And if I was designing a new application, first I would look hard at the new code-free development options in 2010.  We have what looks like a better version of SharePoint Designer, SPD workflows which can be re-used in multiple lists, list forms that can be customised in InfoPath, even (finally) options to enforce relationship behaviour in lists.  And many of these features will be available across all versions, so you won’t necessarily need SharePoint 2010 Enterprise.

Whereas Robert thinks SharePoint reporting is one of the key areas where where Access Services fits in, for me, I think the real opportunity is in hosted SharePoint 2010 solutions.  With Access 2010 you could develop an in-house application, or possibly a commercial one and make this available as both a rich client or easily extend reach using a hosted version of SharePoint 2010 enterprise.  This could have strong appeal to the many developers who have vertical market Access applications out there already and means they could offer a hosted browser based version of an application, even to small, distributed groups of users who can’t afford or don’t want their own in-house server. 

So that’s my take on Access Services.  I’m interested to hear what you think, particularly if you get to try it out yourself so as usual comments are welcome.  If you would like to try out the hosting option for yourself, you can get a free trial right now from Access Hosting.  Then all you need to get started is a download of Office 2010 beta.

Here are some links you might find useful of you plan to try out Access 2010 and Access Services:

Access Product Team Blog: Access Services Overview and Install Information

Access Product Team Blog: Publish to SharePoint (part 1)

The Access Show: Access 2010 Demo of Access Services and Web Databases

The Access Show: Publish an Access 2007 Database to SharePoint with Access 2010

Albert Kallal’s Access 2010 web database demo video

Advertisements

26 Responses to “Thoughts on Access Services in SharePoint 2010”

  1. […] Thoughts on Access Services in SharePoint 2010 (Worker Thread Blog)Back in November last year I gave a presentation to the UK Access User Group  on Access 2010 and in particular how it works with Access Services in SharePoint 2010.  I’ve been meaning to put something together on my thoughts on Access Services, both good bits and not so good.  Last week Robert Bogue wrote an interesting piece about Access Services and how he sees key usage areas.  Robert sees one main area of interest being as a reporting tool for SharePoint content, even content not originally created in Access.  I’m not sure I agree with him here, for reasons I’ll explain below. […]

  2. I am of very much the same opinion as you are, Derek. The ability to create a database wholly served by web pages created in Access 2010 may be taken up big time by large organisations, but I think the extent to which this happens will depend a lot on the politics inside those organisations. However, the idea of creating a web site with Access 2010-created web pages and serving a relatively small number of users would fit quite a number of scenarios and I would hope to get involved in that area myself. However, what truly excites me is the thought of having an Access 2010 rich client as a front end, even as the runtime, with the data in the now relational Sharepoint 2010. I once created an application using Access 2007 runtime with data in Office Live, but this hit real problems in that you could not take data offline in Access 2007 runtime, i.e. you couldn’t toggle between online and offline, and the Sharepoint storage of the data was not relational which meant having to create a system to update data in related tables. I’ve given it a quick test in Access 2010 with Sharepoint 2010 and it seems to work fine. The only thing which concerns me a bit at the moment is how to handle security with the rich client, i.e. the .accdb file.

  3. […] Thoughts on Access Services in SharePoint 2010 […]

  4. […] Thoughts on Access Services in SharePoint 2010 […]

  5. […] https://workerthread.wordpress.com/2010/01/16/thoughts-on-access-services-in-sharepoint-2010/ Access Services , Sharepoint 2010 Access Services, Sharepoint 2010 No Comments July 16th, 2010 About… peter.stilgoe This author published 366 posts in this site. Sharepoint, InfoPath, K2, Nintex, Business Process Mapping, Business Intelligence, Automation, ECM, Document Management, Document Imaging, Internet Marketing & Online Business Consultant Email / MSN: pstilgoe@hotmail.com LinkedIn: Pete Stilgoe – Sharepoint Consultant […]

  6. Ryan Charnock said

    Great post. You have well formed opinions from observation and experience. This gives you a great deal of respect from me. I am an Access and SharePoint developer. I did not know about the SSRS reporting services SP data source. That is a game changer for the Access/SharePoint integration story. I have found the best uses of access with SharePoint are for: 1. Bulk List/Library Meta Data updates through the use of Access Update queries. 2. Was reporting. 3. Complex Client Side user interfaces (form and Subforms).

    Great Post. It really stands out in depth / scope / and insight.

    “Fan”
    -Ryan

  7. Bill said

    I would think the best and maybe standard approach is to have a Complex Access Database that links to a Web Access Database.

    IE, the simple Data related Web Access from Table Data and Manipulation on the Web. Linked to the sophisticated Real Desktop Database with all of MS Accesses features. I came to the conclusion this is the only way to approach development. Since I have a very complex VBA that opens MS Project, MS Excel, etc. from MS Access. It works great in 2003, 2007 and 2010. But I need to publish reports on the Web for Apple user who are Graphics and Art oriented?

    YOUR THOUGHTS

    • Hi Bill

      If you you have SharePoint 2010 Enterprise this could be a good option for you. Dick Moffat has mentioned this in his blog, and there is also a Channel 9 Video demo.

      Don’t know how well the web app works on a Mac but I guess it should work on non-MS browsers and you would be able to create browser based reports.

      • Bill said

        Thanks for the quick response. I just got upgraded by Web Hosting for 2007 and hope they move to 2010 soon.

        I gave up on all of this when VBA was dropped and now that it is back. I am in business again. Since I know the minute I learn .NET I will fix all of the existing.Net and there will be a new one again keeping me from the forefront. 44 years of this gets old changing methods and not content and concept

  8. John said

    Excellent article. Maybe you can give a bit of guidance here:

    Salesforce is the future for my company, but the program I work on is stuck in an Access database used by multiple offices via linked tables on Sharepoint. We’re not getting salesforce in the foreseeable future in our program, but we have to replace this old database. I’ve begun work on a web database in 2010 and am stoked at the possibilities. How much IT support do we really need to make this happen?

    • Hi,

      Re how much IT support you need, it’s going to depend on the complexity of your app and whether you have in-house expertise for initial setup of Access Services. This is why I think if your objective is a distributed Access app then Accesshosting.com may be attractive.

      In terms of development effort, MSDN just published an article on design patterns used in one of their Access Web database templates, you can read it here.

      Derek

  9. Tom said

    I’ve created a rather extensive asset management system for our team to support assets for our sales team (laptops, blackberries, and printers). This helps us manage assets assigned to individuals, repairs, etc. and keep a history of all.
    The issue and shortcomming of Access Services I have found is that you have not way to update a master data base table – you can append, but I have not found a way to update. This is an issue because people change status – they leave the company, change organization, etc. Don’t want to delete the record, but need to update the current employee info which comes from one of our master databases. Can’t just delete the table and replace because of all the relationships already established between the various tables.
    The core question, is their anyway in SharePoint Access Services 2010 to update a table (in mass, not invidual form entry updates) when you append new data?
    Really appreciate comments on this.
    Thanks,
    Tom

    • Hi Tom

      You’re right when you say traditional update queries won’t work in a web database. Inside of the application the way I guess you might approach this is via an Access 2010 data macro which works a bit like an SQL Server “trigger” and can fire on an event, for example when a record is updated or inserted. Here are a couple of links that might help:
      Attaching Logic to Data using Data Macros
      Create a Data Macro
      if it’s a bulk update you need to do (eg updating a big set of SharePoint list rows with external data) then things get a bit trickier.

      • Tom said

        Thanks for the reply. You are correct in that it is a bulk update. Have an 8000 or so sales force (extracted out of the total employee database file) that is constantly changing. Churn factor in sales, so we need to keep the database in sync with the master databases (this is obviously only an extract from the master db – not a direct link which we cannot do). Thus various row will change over time – job changes, retirements, leaving the company, new managers, etc. As time goes, the employee table grows since we need to keep the history of the employee and the assets.
        Thanks,
        Tom

        • I can’t think of an easy way to do that inside of an Access web database. I guess you could do it with the full Access application joining the external table and the SharePoint list but even then it might time out with 8000 rows to update.

          I have seen SharePoint customers with asset management sites using their list of users sync’d from Active Directory as “owners” of items in a separate asset list, but not as an Access web database.

          • Tom said

            kind of what I am thinking and really wanted to do from the begginning anyway. I just have to get access to our data warehouse to do an extract of the data I am using from the employee table and then link to this “personal working” table rather than use a table within Access Services. Access services does not update any of this data, but rather user it as a “master” employee table to build thing from. This way the table could be dealing with “live” info, rather than static info I have to update several times a week. No to try to figure out how to get the application access to the data warehouser (versus individual) and then figure out how to link Access services to the table (have not been able to do external table links with success).
            Thanks,
            Tom

  10. Tom said

    Thanks much. Looks promising. You are correct in your comment about CodePlex…. would never get that approved for our production servers. I will look into this over the next few days and see what I can do and how. Appreciate the info and help.
    Tom

  11. Steve S said

    Thanks again Microsoft – for changing all the rules, and leaving life long Access users without choices……..We used to be able to “Publish” via ASP, without too many problems…..now we can’t….and if you want to publish an Access 2007 database to the web, Microsofts own tutorials START with – open your database in Access 2010……..which I am not going to pay for, as I have paid enough to Microsoft (for MS Office – Full) over the years…..and now their own system doesn’t allow the features of Access that existed for many years. If you MUST have Access 2010 to publish an Access 2007 database, then Microsoft should supply it free of charge…..because technically Access 2007 doesn’t work in this regard…….Can I start the law suit?

  12. eli said

    How to lock down an external user in sharepoint online (365) that I invite to my access services site, to fill a web form created in the Access 2010. To link the underlying List to an accdb file he created on his desktop using Access 2010.

  13. eli said

    Can Access Services Web Forms on (Sharepoint online office 365) support Smartphones (IPhone & Android)
    Thanks

  14. […] Thoughts on Access Services in SharePoint 2010 […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: