locked
Access Web Database Limitations RRS feed

  • Question

  • What are the limitations of either creating a new "Access Web Database" or publishing an existing Access Database to SharePoint 2010? Are Access Web Databases accessible to SharePoint's OM or COM? I've seen discussions how you cannot connect SharePoint workflows to an Access Web Database, but what is the bottom line? What can or cannot be done with Access Web Databases? I've scoured Google for looking to compile a list of what can be done and what cannot be done inside of SharePoint, but no real luck. Any ideas?

    Much thanks.

    Wednesday, November 7, 2012 10:13 PM

Answers

  • Hi,

    When creating a access web database which will be published to SharePoint, you need to take these things into consideration:

    Web databases cannot use VBAs, only macros. Tables created for the client may or may not be publishable to SharePoint. Some features in tables make them un-publishable. Access will let you know when you run the compatibility checker. Among other things, for example, all primary keys must be auto numbered, and compound primary keys are not supported. You cannot edit Access web databases in SPD. In short, you'll start with a clean, new database and create the objects needed to publish.

    As for adding workflows to Access Web Database, here is a limitation: If you attempt to open a Microsoft Access Services site with SharePoint Designer to create a workflow, you will receive the expected message "This web site has been configured to disallow editing with SharePoint Designer." Working with an Access Services web site through SharePoint Designer is not supported.Microsoft Access 2010 Data Macros can be used to add logic to events that occur in the Access Services tables, such as adding, updating, or deleting data. The Data Macros created in the Microsoft Access user interface are stored in SharePoint as Workflows when an Access database is published to Access Services.

    For more information, please refer to these sites:

    Microsoft Access Services on SharePoint 2010 Does Not Support Custom Workflows: http://support.microsoft.com/kb/2528459

    Can I manipulate sharepoint web database by programming?: http://social.msdn.microsoft.com/Forums/en-US/sharepointdevelopmentprevious/thread/66f3ca06-2d1a-421b-b6fc-fea47b000e72

    Thanks,

    Entan Ming


    Entan Ming

    TechNet Community Support

    • Marked as answer by Alex Dove Tuesday, May 7, 2013 8:44 PM
    Thursday, November 8, 2012 10:20 AM

All replies

  • Hi,

    When creating a access web database which will be published to SharePoint, you need to take these things into consideration:

    Web databases cannot use VBAs, only macros. Tables created for the client may or may not be publishable to SharePoint. Some features in tables make them un-publishable. Access will let you know when you run the compatibility checker. Among other things, for example, all primary keys must be auto numbered, and compound primary keys are not supported. You cannot edit Access web databases in SPD. In short, you'll start with a clean, new database and create the objects needed to publish.

    As for adding workflows to Access Web Database, here is a limitation: If you attempt to open a Microsoft Access Services site with SharePoint Designer to create a workflow, you will receive the expected message "This web site has been configured to disallow editing with SharePoint Designer." Working with an Access Services web site through SharePoint Designer is not supported.Microsoft Access 2010 Data Macros can be used to add logic to events that occur in the Access Services tables, such as adding, updating, or deleting data. The Data Macros created in the Microsoft Access user interface are stored in SharePoint as Workflows when an Access database is published to Access Services.

    For more information, please refer to these sites:

    Microsoft Access Services on SharePoint 2010 Does Not Support Custom Workflows: http://support.microsoft.com/kb/2528459

    Can I manipulate sharepoint web database by programming?: http://social.msdn.microsoft.com/Forums/en-US/sharepointdevelopmentprevious/thread/66f3ca06-2d1a-421b-b6fc-fea47b000e72

    Thanks,

    Entan Ming


    Entan Ming

    TechNet Community Support

    • Marked as answer by Alex Dove Tuesday, May 7, 2013 8:44 PM
    Thursday, November 8, 2012 10:20 AM
  • Alright, this is great information. So here is what I have taken from your post and the links:

    • Access client app can publish 'simple' tables to a SharePoint list
    • Access client app cannot publish 'complex' databases to SharePoint as the compatibility checker will fail
    • Any 'complex' Access client applications needing exposure to SharePoint either need to be uploaded as-is OR re-built from scratch in SharePoint using native SharePoint lists, OM, COM, LINQ, etc.
    • Access client app .accdb file can be uploading into SharePoint library to be accessed by end users who have Access client application
    • Access client app .accdb file stored in SharePoint library cannot be introspected by SharePoint API as would a normal list would be to expose fields and data.
    • Web Access Databases, like the 'Contacts Web Database' or 'Issues Web Database' are self-contained Web Access databases. There is no way to expose the data using SharePoint API. The better solution would be to create a SQL database and connect via BCS to allow exposing data.

    Would you agree or disagree with my synopsis? If you don't agree or I have misunderstood or if I have not mentioned an acceptable use-case, could you explain so I get the full picture? Thanks again.

    Thursday, November 8, 2012 6:19 PM