Downloading NwindMove.zip and GroupPol.zip from Windows Live SkyDrive
NwindMove.zip (1 MB) contains the NwindMove.accdb database and a NwindMove_Backup.accdb file for moving tables from an Access application to SharePoint lists with the Export Tables to SharePoint Wizard. These two files are slightly modified versions of those included in the \Chaptr22 folder of the Microsoft Access 2010 in Depth book’s sample files. Download the file here.
The GroupPol.accdb (11,944 KB) database has contact data for 2,275 fictitious employees and 25,344 students of an equally fictitious OakLeaf University. The file is suitable for adding contacts in bulk to Access databases and Web databases created from the Contacts template. GroupPol.accdb is based on the Oakmont database that’s included in the downloadable sample files for my Microsoft Access 2010 In Depth book. You can download GroupPol.zip, which contains GroupPol.accdb, from my Windows Live SkyDrive account here.
Office 365 SharePoint Online and Administration Forums, Blogs
- Microsoft Office 365 Blog: Interesting in learning more about Microsoft Office 365? Follow our blog to stay up to date with the latest news and information on taking your business to the cloud!
- Office 365 Technical Blog: The Office 365 engineering team blog, supporting our customers whether getting started, performing advanced tasks or troubleshooting common issues. We love to hear from you, so comment and share your thoughts on the latest and greatest cloud service!
- The Grid: The Grid is a new community that brings together IT pros and other experts who will share their considerable expertise about technology and Office 365.
SharePoint 2010 TechNet Forums
-
SharePoint 2010 - General Questions and Answers: General questions, comments and discussions on SharePoint 2010 products and SharePoint services such as Access Services, BCS, Excel Services and Visio Services.
-
SharePoint 2010 - Setup, Upgrade, Administration and Operation: Discuss setup, upgrade, administration and operation for SharePoint 2010 and SharePoint services such as Access Services, BCS, Excel Services and Visio Services.
-
SharePoint 2010 - Using SharePoint Designer, Infopath, and other customization: Discuss using SharePoint Designer, SharePoint Gallery Solutions, templates & other customization for SharePoint 2010 and SP services such as Access Services, BCS, Excel Services, & Visio Services.
-
SharePoint 2010 - Using Visual Studio with SharePoint and other programming: Discuss using Visual Studio and other programming with SharePoint 2010 and SharePoint services such as Access Services, BCS, Excel Services and Visio Services.
Note: SharePoint Online doesn’t support Business Connectivity Servces (BCS)
Downloading and Installing SharePoint Foundation 2011
Microsoft’s Download Center’s Microsoft SharePoint Foundation 2010 page contains the following information:
Overview
SharePoint Foundation 2010 is the new version of Microsoft Windows SharePoint Services. It is the essential solution for organizations that need a secure, manageable, web-based collaboration platform. SharePoint helps teams stay connected and productive by providing easy access to the people, documents, and information that they need to make well-informed decisions and get work done. Use SharePoint Foundation to coordinate schedules, organize documents, and participate in discussions through team workspaces, blogs, wikis, and document libraries on the platform that is the underlying infrastructure for SharePoint Server.
Search Server 2010 Express: If you’re using SharePoint Foundation, you can easily add search capabilities to your collaboration environment across SharePoint sites, file shares, web sites, Exchange Public Folders, and third party repositories using Microsoft Search Server 2010 Express. Click here to download.
System Requirements
- Supported Operating Systems:Windows Server 2008 R2;Windows Server 2008 Service Pack 2
Please review the SharePoint Foundation 2010 system requirements before you proceed. Actual requirements and product functionality may vary based on your system configuration.
SharePoint Foundation 2010 can also be installed for development purposes on Windows 7 and Windows Vista Service Pack 2. Installing on these operating systems is not supported for production deployments. For instructions on how to install on these operating systems, refer to Setting Up the Development Environment for SharePoint 2010 on Windows Vista, Windows 7, and Windows Server 2008.
Signing Up for the Office 365 Small Business (P1) Beta Program
Click the Join the Beta button on the Office 365 for Small Business page.
Evaluating database needs across SQL Server, SQL Azure, SharePoint and Access
The Access Team’s Evaluating database needs across SQL Server, SQL Azure, SharePoint and Access post of 1/10/2011 to the Microsoft Access blog explains how to choose between the four primary back ends for Access front ends:
Every now and then we hear from customers who want some cheat sheet or Excel-based "calculator" to help them decide what technology to base their application on: SQL Server, SharePoint, Access, etc. While in some cases (especially in the extremes) the decision is relatively easy to make, it is very common for the decision to be more complex than what can be obtained from such "calculators." There are many issues to consider: from technical requirements of the application in terms of performance, availability, maintenance, backup, scalability, security, etc., to important business requirements in terms of agility, time to value, and the changing business needs of the organization.
We put together a white paper that provides an overview of a few of these products, including:
- Access
- SharePoint
- SQL Server
- SQL Azure
The paper also discusses some of the requirement areas to consider as part of the decision-making process.
The most important take-away of this document may be common sense: in many cases, deciding how to best solve data-centric problems requires careful consideration and participation of technical and business experts who are able to articulate multiple angles of the problem (both business and technical) and are able to make an informed decision that addresses the immediate and future needs of the organization. In other words, avoid the temptation to use a cheat sheet! Look around at the people in the room: do you have the right people for the scope of the application you are building? Do you need, and if you do, do you have representation from both IT and the business organization? Do you have the right experts on these technologies helping you make a choice? If you don't, what are the implications if you make the wrong choice? Do you understand how the application and business problem will evolve in the future?
Those are questions that only you can answer and for which a calculator (or a white paper!) is really not the best approach. We hope that this white paper helps you understand the core strengths and capabilities of these technologies and gives you a starter set of issues to consider in discussing your selection. The good thing is that we got you covered: from Access to SharePoint to SQL Server and SQL Azure, your application will likely benefit from one or more of those products.
(Word 2007-2010 .DOCX format)
(Word 97-2003 .DOC format)
SharePoint Lists
Microsoft Access 2010, 2007 and 2003 can link front ends to SharePoint 2010 Foundation, Server 2010 Standard or Enterprise Edition, or SharePoint Online lists. A SQL Server [Express] instance holds the data for SharePoint lists. Following are sub-topics of the Microsoft patterns & practices described in the MSDN Library’s SharePoint Guidance topic:
SharePoint Columns, Lists, and Content Types
Data models in SharePoint 2010 are implemented using columns, lists, and content types. A full understanding of these constructs underpins every effective data model in SharePoint 2010.
List Relationships in SharePoint 2010
SharePoint 2010 allows you to create relationships between lists in the same site collection. List instances are related through lookup columns (as known as lookup fields). The real benefit of this functionality is that SharePoint 2010 allows you to use join statements, in LINQ to SharePoint or in collaborative application markup language (CAML), to query across lists where lookup column relationships are defined. By default, SharePoint permits a maximum of eight joins per query, although administrators can change this limit through the Central Administration Web site or by using PowerShell. However, queries that contain large numbers of join statements are resource-intensive, and exceeding eight joins per query is likely to have a significant detrimental effect on performance.
Although this newly introduced ability to query across lists brings the capabilities of SharePoint data models closer to those of relational databases, SharePoint support join predicates only where a lookup column relationship exists between the lists. In this regard, the join functionality in SharePoint is less powerful than the JOIN predicate in SQL.
Query Throttling and Indexing
A familiar challenge when you work with SharePoint lists is how to address the performance degradation that can occur when your list contains a large number of items. However, SharePoint is capable of managing extremely large lists containing millions of rows. The often-quoted limit of 2,000 items per list, actually refers to the maximum number of items that you should retrieve in a single query or view in order to avoid performance degradation. Effective indexing and query throttling strategies can help you to improve the performance of large lists.
Note: For more information about working with large lists, see List Patterns, "Designing Large Lists and Maximizing Performance" from Performance and capacity test results and recommendations on TechNet and Handling Large Folders and Lists on MSDN.
Data Platform Improvements in SharePoint 2010
The Access Team’s Data Platform Improvements in SharePoint 2010 post of 2/15/2011 describes changes to SharePoint lists that apply to their use as linked Access 2010 back ends:
Database Relationships, Unique Columns and validation rules make it easy to validate your data. This creates much more maintainable applications and helps users to do the right thing the first time.
Some of the feedback we received from blog readers and developers for Access/SharePoint integration was to have better data platform features to ensure the data integrity they expect from a relational database.
In SharePoint 2007 validation such as required fields and min/max values are only enforced through the browser user interface. This means that applications talking to SharePoint via the SOAP Web services or the Object Model will be able to bypass those rules.
The data platform improvements we included in SharePoint 2010 are enforced at the at the data layer to make sure that no matter how an application/user interacts with the backend lists (Browser, Office Client, Web Service, Object Model), the rules will be enforced.
List Relationships
In SharePoint you can create lookups between lists, in 2010 lookups can enforce cascade delete and restrict delete relationships between the two lists.
In Access you can use the lookup wizard to set up a relationship.
You can use this to create simple 1:Many or Many:Many relationships between lists that are enforced at the data layer.
TIP: Use the lookup wizard to create SharePoint supported relationships in Access 2010. If you are working against a published web database—you will need to sync the table after creation before you can create lookups
TIP: Web Databases do not support relationship view. So if you want to see a view of all your relationships use the database documenter or create a dummy query with all the tables.
Unique Column Constraints
You can use unique constraints to make sure that duplicate values are not entered in specific column. Multiple unique constraints can be defined on a table and they allow for NULL values.
You can use these to enforce rules such as making sure an order number is unique across all orders. These constraints are enforced at the data layer.
The property is set through the Table Tools | Fields | Field Validation | Unique check box in the ribbon.
Tip: Unique columns are not supported for attachments, table based lookups (foreign keys), multi-value lookups, memo, hyperlink and Boolean fields.
Data Validation
As in Access, validation can be enforced at the table/list level and at the field/column level. You can supply an expression that must be true whenever you enter or change data. This allows a developer to validate data before it goes into the database. If the condition is not true the developer can provide a customer error message. The properties are set through the Table Tools | Fields | Field Validation | Validation dropdown in the ribbon.
When you are in SharePoint you use Excel expressions to create your rules but in Access you continue to use the familiar Access expressions and we handle the translation back and forth from the server.
In the browser we remote simple expressions to JavaScript to notify the user of any issues before they try to commit the record. This creates a more responsive user experience and reduces calls to the server.
This is our Country field in the browser
If I enter invalid data and tab off the field is automatically highlighted
When I place my focus back into the field the validation message is displayed so that I know how to correct my issue.
More advanced expressions like the sample below get evaluated at save and produce an error prompt.
Tip: You can use NOT, <>, <, >, <=, >=, IN, BETWEEN, LIKE (?, *, # as wildcard characters) and IS NOT NULL as comparisons on your expressions. Table and field validation rules are enforced at the data layer.
One of our testers and author of Access 2007 Inside Out, Jeff Conrad, sent me this great sample;
In the EmailAddress field, we want to be sure the e-mail address provided by the user appears to be a valid e-mail address. We can verify the e-mail address meets most standards of valid syntax by using a combination of the LIKE operator and wildcard characters in a field validation rule. In the blank text box at the top of the Expression Builder dialog box, type Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*")) for the field validation rule.
This field validation rule ensures that every e-mail address provided by the user starts with at least one character followed by the @ symbol, contains at least one more character following the @ symbol, and contains the dot symbol followed by at least one more character after the dot symbol.
Also, this field validation rule does not allow a space, a comma, or a semi-colon anywhere in the email address.”
Enforcing SharePoint validation at the data layer
SharePoint also has a couple more validation options for required fields and min/max values for numbers. For Access Services lists we enforce those at the data layer as well.
Tip: There is a list OM property called SPList.EnforceDataValidation that gets or sets a Boolean value specifying whether required fields and min/max values are enforced at the data layer. You can set this on any SharePoint 2010 list to get this behavior.
More Reading
List Relationships and Unique Columns from the SharePoint perspective(http://sharepoint.microsoft.com/blogs/GetThePoint/Lists/Posts/Post.aspx?ID=316)
More info on data validation in general for Access from 2007 (http://office.microsoft.com/en-us/access/HA100963121033.aspx)
Leveraging Excel and Access Services in SharePoint Online
MSDN’s Leveraging Excel and Access Services in SharePoint Online page provides links to hands-on labs and videos for Access Services and Web Databases:
Excel and Access Services provide powerful features for building SharePoint Online solutions. In this session, you’ll get an inside look at both Excel and Access services and how each can be accessed programmatically when building SharePoint Online solutions.
Hands-On Labs
- Excel Services
In this lab you will learn how to add Business Intelligence (BI) capabilities to your SharePoint Online site using Excel Services.
- Access Services
In this lab, you will learn how to use SharePoint Access Services to publish an existing Microsoft Access database to SharePoint Online. Doing so will create a new SharePoint web and convert Access tables into SharePoint lists, and Access forms into corresponding HTML and CSS. You will then use this database as a bug tracking database. You will learn how to proactively log exceptions in SharePoint Online.
Videos
Note: Office 365 Small Business’ P1 subscription includes Access Online but not Excel Online.
Microsoft SharePoint Online Developer Guide (Beta)
The Microsoft SharePoint Online Developer Guide (Beta) is a 44-page PDF File that the Overview describes as follows:
This guide walks you through some of the rich features that are available to developers and designers in SharePoint Online in Office 365. It provides an overview of the feature set and extensibility points for SharePoint Online, and a discussion of how to create solutions for this new environment. This guide begins by describing the types of solutions you can build, and then addresses the developer tools for SharePoint 2010, the new platform features, and the solution deployment architecture
SharePoint Online’s Enterprise and Storage Features
SharePoint Online’s Small Business (P1) and Information Workers (E3/E4) plans offers most of features of SharePoint Server 2010 Standard Edition plus Access Services, which are required to generate Web Databases from Access databases. The E3/E4 plans include Excel Services, Forms Services and InfoPath Services. Kiosk Workers in plans K1/K2 and Information Workers in plan E1/E2 don’t have Access, Excel, Forms or InfoPath Services. All plans include an initial 50 GB of online storage, plus 500 MB for each additional licensed user.
For more information on what’s included in the five different beta licensing plans, download the “Microsoft Office 365: Microsoft SharePoint Online Beta Service Description” as Microsoft_SharePoint_Online_Standard_Beta_Service_Description_Final.docx from the Office 365 Beta Service Descriptions link. From the Beta Service Descriptions:
Like all Microsoft Online Services offerings, SharePoint Online has 99.9-percent scheduled uptime with financially backed SLAs covering any instance when this service availability standard is not met.
| Note |
| The SLA is not offered during the Office 365 Beta period. |
Deleted Item Recovery
SharePoint Online users have a Recycle Bin where deleted content is stored. They can access the Recycle Bin to recover deleted documents and lists if they need to. Items in the Recycle Bin are retained for 30 days. The following data types are captured by the Recycle Bin:
- Lists
- Libraries
- Folders
- List items
- Documents
- Web Part pages
The following data types are not captured by the Recycle Bin:
- Deleted Sites
- Deleted web pages
- Site customizations made through SharePoint Designer 2010
Data protection services are provided to prevent the loss of SharePoint Online data. Backups are performed every 12 hours and retained for 14 days. Note that this describes the data backup services as offered when SharePoint Online is generally available.
| Note |
| The data protection services policy may be different than described during the Beta period |
Service Continuity Management
SharePoint Online is hosted in Microsoft-managed, enterprise-level data centers that are designed to operate highly available online services. Because of this, the Microsoft SLA with SharePoint Online subscribers is set at 99.9 percent uptime.
However, service availability can be affected by hardware failures, natural disasters, and human error. To address this, SharePoint Online offers service continuity management, a process for managing risks to ensure that a company’s IT infrastructure is capable of providing continuing services if normal availability solutions fail. Service continuity management for SharePoint Online includes provisions to quickly recover from such unexpected events.
Two metrics commonly used in service continuity management to evaluate disaster recovery solutions are a recovery time objective (RTO), which measures the time between a system disaster and the time when the system is again operational, and a recovery point objective (RPO), which measures the time between the latest backup and the system disaster, representing the nearest historical point in time to which a system can recover. SharePoint Online has set an RPO and RTO in the event of a disaster:
- 12-hour RPO: Microsoft protects an organization’s SharePoint Online data and has a copy of that data that is equal to or less than 12 hours old.
- 24-hour RTO: Organizations will be able to resume service within 24 hours after service disruption if a disaster incapacitates the primary data center.
Virus Filtering
Forefront Security for SharePoint is included with SharePoint Online to help protect the SharePoint Online environment from viruses while maintaining uptime and optimizing performance.
Forefront Security for SharePoint provides comprehensive protection for SharePoint document libraries using multiple scan engines and content controls to help eliminate documents that contain malicious code.
Understanding Office 2010’s SharePoint Workspace
TechNet’s SharePoint Workspace 2010 overview topic of 5/12/2010 describes Office 2010’s Sharepoint Workspace feature as follows:
This article describes the key functionality and architecture of Microsoft SharePoint Workspace 2010. SharePoint Workspace 2010 is the new name for and succeeds Microsoft Office Groove 2007. SharePoint Workspace 2010 is a client application that provides fast, any-time interactive access to document libraries and lists on Microsoft SharePoint Server 2010 and Microsoft SharePoint Foundation 2010. SharePoint Workspace 2010 also provides options for creating Groove peer workspaces and Shared Folder workspaces. SharePoint Workspace 2010 is more versatile than Microsoft Office Groove 2007 and can be integrated with Microsoft SharePoint Server 2010 or can run independently.
Microsoft SharePoint Workspace 2010 provides a client for Microsoft SharePoint Server 2010 and Microsoft SharePoint Foundation 2010 that enables real-time synchronization of desktop content with SharePoint documents and lists. SharePoint Workspace 2010 also provides options for creating Groove collaboration workspaces and synchronized shared folders. By using SharePoint Workspace 2010, information workers can easily synchronize online and offline content with a designated SharePoint site or collaborate with external partners and offsite team members through shared workspaces. SharePoint Workspace 2010 is included with Microsoft Office Professional Plus 2010. …
The article continues with “Key features of SharePoint Workspace 2010.”
Creating SharePoint workspaces from Office 365’s SharePoint Online is disabled in Safe Mode.
