Access Web databases

Build an Access database to share on the WebApplies to:Microsoft Access 2010, SharePoint Server 2010

You can use Access 2010 and Access Services, a new component of SharePoint, to build web database applications. This helps you: Secure and manage access to your data Share data throughout an organization, or over the Internet Create database applications that don't require Access to use

OverviewAccess Services provides a platform for you to create databases that you can use on the Web. You design and publish a web database by using Access 2010 and SharePoint, and people use the web database in a Web browser.HOW IT WORKSWhen you publish a web database, Access Services creates a SharePoint site that contains the database. All of the database objects and data move to SharePoint lists in that site.After you publish, SharePoint visitors can use your database, based on their permissions for the SharePoint site. Full ControlThis lets you make data and design changes. ContributeThis lets you make data changes, but not design changes. ReadThis lets you read data, but you can't change anything.You can open the web database in Access, revise the design, and then sync your changes effectively, save them to the SharePoint site. You can also take it offline, use the offline version, and then sync data and design changes when you are back online.NOTETo build a web database, you need Full Control permissions on the SharePoint site where you want to publish it. For more information about SharePoint permissions, see theSee Alsosection.Forms and reports run in the browserForms, reports, and most macros run inside the browser. This lets Access refresh data on the screen without having to redo the whole page.You can create a form to help people navigate your application. A new control, the Navigation control, makes it easy to add standard Web-style navigation buttons to a form for this purpose.NOTEThe Navigation Pane (the feature that you use in Access to browse the objects in a database) is not available in a Web browser.Data is stored in SharePoint listsAll of your tables become SharePoint lists, and records become list items. This lets you use SharePoint permissions to control access to your web database, as well as take advantage of other SharePoint capabilities.Queries and data macros run on the serverAll SQL processing happens on the server. This helps improve network performance by limiting traffic to result sets.Intranet or InternetYou can publish to your own intranet SharePoint server, or to the Internet. Microsoft is offering an Internet-facing, hosted SharePoint solution.

Create a web databaseThis section describes key new features and provides steps for the basic design tasks you need to complete to create a web database.

BEFORE YOU BEGINThere are a few tasks that you should perform before you start to design your web database. Moreover, there are design differences between web databases and desktop databases that you should know about, especially if you are an experienced Access developer. Determine the purpose of your databaseHave a clear plan so you can make good decisions when working out design details. Find and organize the information requiredYou cannot use linked tables in a web database. Any data that you want to use that will not originate in the database must be imported before you publish. If you line up your data before you start designing, you can avoid having to refit your design to accommodate unexpected data challenges. Identify the SharePoint site you will use to publishYou cannot publish at all without SharePoint. If you want to test your design in a browser while you design it (not a bad idea), you have to publish it first. Plan your securityYou can take advantage of SharePoint security to control access to your web database. Plan security early so you can build it into your design.Design differences between desktop and web databasesSome database features that you can use in a desktop database are not available with Access Services. However, there are new features that support many of the same scenarios as these desktop features.The following table lists the desktop-only features, and the new feature that helps support the same scenario.SCENARIODESKTOP-ONLY FEATURENEW FEATURE

Designing database objectsDesign viewEnhanced Datasheet view; Layout view

Reviewing summarized data, such as sums, averages, and groupsGroup functionsData macros; group functions in reports

Programming eventsVBAMacros and data macros; New macro design experience with IntelliSense

Navigate to a database objectNavigation Pane; switchboardsNavigation control or other form element

IMPORTANTYou can create many client objects in a web database, but you cannot use them in a browser. However, they are part of the web database and can be used in Access 2010 on the desktop. People can open the web database in Access, and then use the client objects. This is an effective way to share a database, and also opens new opportunities for working together over the Web. SharePoint handles any concurrency issues.Desktop-only features with no Access Services counterpartNOTEThe following list is not exclusive. Union queries Crosstab queries Overlapping controls on forms Table relationships Conditional formatting Various macro actions and expressionsCONSIDER USING A TEMPLATEWhen you have determined what your application must do, consider whether a database template would work. Database templates are pre-built applications that you can use as-is or modify to suit your particular needs.You can review the available templates on theNewtab in Backstage view.GET STARTED WITH A BLANK WEB DATABASE On theFiletab, clickNew.

TheFiletab opens Backstage view, a new part of the Access interface where you'll find commands that apply to an entire database, such asPublish to SharePoint.In Backstage view, theNewtab has commands for creating a database.

UnderAvailable Templates, clickBlank Web Database.Review the proposed file name in theFile Namebox, and the path for the database file, listed just below. You can change the file name by typing in theFile Namebox.To change the path, click the folder icon next to theFile Namebox to browse for a location to put your database file.ClickCreate. Your new web database opens and displays a new empty table.DESIGN A WEB TABLENOTEYou use Datasheet view to design a web table.When you first create a blank web database, Access creates a new table and opens it in Datasheet view. You can use the commands on the Fields tab and the Table tab to add fields, indexes, validation rules, and data macros a new feature that lets you change data based on events.After you edit and use the new table, you'll most likely want to create more tables.Create a new web tableWith your web database open: On theCreatetab, in theTablesgroup, clickTable.When you first create a table, it has one field: anAutoNumberID field. You can add new fields to store the items of information required by the table subject. For example, you might want to add a field that stores the date you begin tracking something.Add a field from the field galleryYou can choose from a variety of preformatted fields and add them to your table by using the field gallery. On theFieldstab, in theAdd & Deletegroup, click the field type that you want.Add a field by clicking the datasheet1. With the table open, clickClick to Add, and then select a field type.2. Give the field a name that reflects its contents.TIPTo change the name of an existing field, double-click the field name.3. Repeat for each field that you want to create.Change field propertiesFormatting and properties determine how a field behaves, such as what kind of data it can store. You can change these settings so that the field behaves the way that you want.1. Select the field that has formatting and properties that you want to change.2. On the ribbon, click theFieldstab.3. Use the commands in theFormattingandPropertiesgroups to change the settings.ADD A CALCULATED FIELDYou can add a field that displays a value that is calculated from other data in the same table. Data from other tables cannot be used as the source for the calculated data. Some expressions are not supported by calculated fields.1. With the table open, clickClick to Add.2. Point toCalculated Field, and then click the data type that you want for the field.The Expression Builder opens.3. Use the Expression Builder to create the calculation for the field. Remember that you can only use other fields from the same table as data sources for the calculation. For Help using the Expression Builder, see the articleUse the Expression Builder.SET UP DATA VALIDATION RULESYou can use an expression to validate input for most fields. You can also use an expression to validate input for a table, which can be useful if you want to validate input for a field that does not support validation, or if you want to validate field input based on the value of other fields in the table.You can also specify the message that is displayed when a validation rule prevents input, known as a validation message.Set up a field validation rule and message1. Select the field to which you want to add a validation rule.2. On the ribbon, click theFieldstab.3. In theField Validationgroup, clickValidation, and then clickField Validation Rule.The Expression Builder opens.4. Use the Expression Builder to create your validation rule. For Help using the Expression Builder, see the articleUse the Expression Builder.5. In theField Validationgroup, clickValidation, and then clickField Validation Message.6. Type the message that you want to display when input data is not valid, and then clickOK.Set up a record validation rule and messageYou can use a record validation rule to prevent duplicate records, or to require a certain combination of facts about the record are true, such as [Start Date] is greater than January 1, 2010, and less than [End Date]1. Open the table to which you want to add a validation rule.2. On the ribbon, click theFieldstab.3. In theField Validationgroup, clickValidation, and then clickRecord Validation Rule.The Expression Builder opens.4. Use the Expression Builder to create your validation rule. For Help using the Expression Builder, see the articleUse the Expression Builder.5. In theField Validationgroup, clickValidation, and then clickRecord Validation Message.6. Type the message that you want to display when input data is not valid, and then clickOK.CREATE A RELATIONSHIP BETWEEN TWO WEB TABLESTo create a relationship in a web database, you use the lookup wizard to create a lookup field. The lookup field goes in the table that is on the many- side of the relationship, and points to the table that is on the one- side of the relationship.Create a lookup field in Datasheet view1. Open the table that you want on the many- side of the relationship.2. Click the arrow next toClick to Add, and then clickLookup & Relationship.3. Follow the steps of the Lookup Wizard to create the lookup field.MODIFY A LOOKUP FIELD IN DATASHEET VIEW1. Open the table that has the lookup field that you want to modify.2. Do one of the following: On theFieldstab, in thePropertiesgroup, clickModify Lookups. Right-click the lookup field, and then clickModify Lookups.3. Follow the steps of the Lookup Wizard.MAINTAIN DATA INTEGRITY BY USING DATA MACROSYou can implement cascade updates and deletes by using data macros. You can use commands on the Table tab to create embedded macros that modify data.For more information about creating data macros, see the articleCreate a data macro.CREATE A WEB QUERYYou can use a query as the data source for forms and reports. Queries run on the server, helping minimize network traffic.For example, suppose you use a web database to track charitable contributions. You want to see who donated money while an event was occurring. You could use a query to select the data and prepare it for use in forms and reports.NOTEThis procedure uses the charitable contributions template as an example. You can follow along if you create a new database by using the charitable contributions database template.1. On theCreatetab, in theOthergroup, clickQuery.2. In theShow Tabledialog box, double-click each table that you want to include, and then clickClose.In this example, double-click Constituents, Donations, Events, and EventAttendees.3. Create any required joins by dragging fields from one object to another in the query design window.In this example, drag the ID field from Constituents to the DonorConstituentID field in Donations, and then drag the DonorConstituentID field from Donations to the ConstituentID field in EventAttendees.4. Add the fields that you want to use. You can drag the fields to the grid, or you can double-click a field to add it.In this example, add Event from the table Events, DonationDate from the table Donations, and Greeting, FirstName, and LastName from the table Constituents.5. Add any criteria that you want to apply.In this example, you want to limit DonationDate so that it falls between the StartDate and EndDate of the event. In the query design grid, in theCriteriarow under DonationDate, type>=[StartDate] And