MS Access integration with SharePoint allows empowering of business data and information tools.
MS Access database can be fully or partially uploaded to SharePoint. This functionality allows implementing web-based solution that allows users online accessibility to organisational business data. MS Access database tables are transformed to SharePoint lists, and tables are connected as Web tables. From that point the database application works as web database.
Between Access and SharePoint
Still, there are some differences between the MS Access standalone approach and MS Access + SharePoint implementation:
- SharePoint for MS Access needs is not a relational database architecture: Although SharePoint is based on SQL Server the lists approach is a non-relational data set. This fact implies to adjusted design and development approach required in MS Access for SharePoint.
- Functionality: Latest MS Access versions allow seamless database export to SharePoint with no need in files uploads, security definitions etc. However, MS Access 2010/2013 still has functionality that is not yet supported by SharePoint.
- Security: MS Access allows distribution of executable file (.accde) that provides protection from changing user interface or data structure. As of 2013, SharePoint does not support .accde resolution. The available SharePoint format for database is .accdw, which is not protected.
- Web forms and web reports: Both are supported ion SharePoint with adjusted visual design to fit the web.
- Data Macros: Data macros on SharePoint are controlled and require customization.
- VBA scripting: Not supported by SharePoint. Transition to SharePoint data macros is required.
- Size limitations: SharePoint lists allow limited number of data fields, therefore better performance is for less data fields.
Why Use SharePoint Lists?
- Sync: SharePoint has built-in synchronization mechanism. It's more complicated in MS Access (Jet replication)
- Design changes: Applying changes in Access requires updates at application model design level and SQL Server management. SharePoint allows more simple way of update only at Access level.
- Versions management: SharePoint has a built in versions management feature and allows restore of previous versions.
What solution fits more?
Web database approach is different from Access client database, and therefore choosing the right solution requires analyzing business needs. Such decisions are usually affected by numerous factors, such as costs, time to market and problem solutions.
Following are basic questions to support decision process:
- What kind of access is required: online or remote?
Choosing among Web Database solution and simple MS Access client solution is mainly a question of access needs, budgets and time. There are also hybrid solutions that allow mixed combination of features.
- Will part of user interactive modules not be required on a constant access basis?
Sometimes, only part of applications features require wide users accessibility. In that case, SharePoint Access Services allow simple and fast implementation of these features.
- Who are the application users? What level of security and sharing is required?
Access Services require user identification (and therefore is less appropriate for surveys for example). On the other hand, user identification allows better application access control.