You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
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.
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.
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.”
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.”
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.
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)
Comments: (8) Collapse
I just downloaded your free trial product - and as should have been expected - it doesn't work. After waiting through the 30 minute download process and wacking my machine with all your registry hacks - the software is "disabled" and I cannot do even the most basic trial of the access functionality. Thanks a lot!!! You guys are just the best development team and I just love the way you think.
Tom @ are you talking about the client or server? If the server--were you able to use SharePoint?
Clint,
There is something I am unclear about in how Access and Sharepoint relate to each other. In Access 2007 and WSS 3.0, e.g. as in Office Live, it is possible set Access to cache data, to take the data offline and to synchronise when back online. All these can be done manually and it is also possible to discard changes. With Access 2010 and Sharepoint 2010, we have an automatic caching and manually taking offline seems to be only possible using Internet Explorer rather than in the Access 2010 UI or via VBA. Is that correct, please? As I understand it, the synchronisation is smarter in Access 2010/Sharepoint 2010, but does this need the enterprise version of Sharepoint 2010 or does it work with just Sharepoint Foundation, i.e. without Access Services running? I understand the need for Access Services to run Access-generated web pages, but am unclear on Access Services' role, if any, when using a standard front end with tables/lists in Sharepoint, i.e. in a sort of "Access 2007" mode. Thanks, Alan
To partly answer my own question, with a standard Access database front end linked via linked tables to WSS 3.0 (i.e. to a version of Sharepoint 2007 and in my case, to Office Live), there are still the same controls in the Access 2010 front end for caching/not caching, taking data offline, discarding changes, relinking and synchronisation that there were in Access 2007. That's good. All I need to do now is work out what happens:
1) With a standard Access 2010 front end using the runtime, i.e. seeing whether it is possible to take data offline and synchronise again. It wasn't possible in Access 2007 as far as I could ever make out.
2) With a web database, containing client forms and reports using a full version of Access 2010 when using it online and offline.
3) With a web database, containing client forms and reports using the runtime version of Access 2010 when using it online and when using it offline.
Excellent. I like this a lot. Especially like the ability to customize the validation error message on record or field.
What about the issue of multi users making changes to the data? Particularly adding new records (off line) and need to maintain the referential integrity rules. There are now GUID key values to add new records generating Primary and foreign keys for User 1 and User 2 adding records when Client is off line. This goes back to need some kind of replication schema with the parent Sharepoint list.
Ivan @ Records added offline work the same as Access 2007. We add them with a negative ID number and fixup the IDs when we resync. This makes sure that children records are related to their parents. Alan @ We are working on an offline post to cover your questions above. The short answer is--IE offline command is the only entry point and you have to open the tables before going offline to get the data cached.
With regard to taking the app offline using IE itself, is it possible to use a web control and programmatically take the app offline? Happy to wait for your "offline post" for an answer....
Comments: (loading) Collapse