Wiley SharePoint 2010 All-in-One For Dummies
Contents
1. Most database applications contain multiple tables many of which have a relationship For example you may have a Jobs table that contains a list of all the jobs within your organization including such information as the Job Title Job Description Job Classification and Salary Range The Employee table would contain a Job Title field that would retrieve the list of possible jobs from the Job table In this case the Job Title field would be a lookup field with a relationship to the Jobs table Microsoft Access provides an intuitive designer to help you through the process of creating your database tables and their associated fields and relationships Forms When you create an application that requires users to enter data you invari ably need to create a form The Forms Ribbon group shown in Figure 1 3 provides a number of commands from which you can create your Web data base forms TA tg Datasheet BiG E Navigation Form Multiple Blank Rems Form Client Forms Forms Book IV Chapter 1 suonesijddy 599 fuiys jqnd 6 14 Designing SharePoint Compatible Access Databases You can access the Forms Ribbon by clicking the Create tab in Access The Form commands include these Form Creates a Web form that allows you to enter data one record at a time Web forms can only be created for tables or Web queries Multiple Item Creates a Web form that shows multiple records at once Conti2. link bar from the parent site doesn t appear onscreen Excluding the top link bar from the Web database application pages reduces possible user confusion because it helps give navigation a close resemblance to the familiar navigational structure of the client application We re convinced that this behavior must be a feature not a bug It s just too convenient You can still use the Options Navigate Up command to get back to the parent site 11 Click Create Access Services creates the new Access Web database site ready for use After your Web database is created you re free to start using the site as desired If you want to customize the site or give it additional functionality you can open it in the Access 2010 client locally For example you can add forms and reports rename tables and fields and change the navigational structure When you ve made your changes simply save them and click the Synchronize button to apply them to your SharePoint site For more about the publishing and synchronization process flip to the Publishing to Access Services section of this chapter Access Web Database Objects When you re creating a Web database application using the Access client it helps to familiarize yourself with the various objects and tools at your dis posal before you just start slinging electrons around The creation of a typi cal Web database application includes one or more of the components we pres
3. you can publish edit cus tomize and synchronize your changes by using controls that appear in the Access 2010 client application Access Services is a new feature that comes with SharePoint Server 2010 Enterprise Edition If you don t have an Enterprise license the Access Web Database site templates won t appear in your SharePoint Gallery Figure 1 2 shows you a list of the templates available with SharePoint 2010 Music Windows Internet Explorer gr htty sp2010rep Pages Defaullaspx ty Bing gt Favontes Home Music te gt GB the Pager Satety toos gt Create Browse From Filter By All Types You have a range of Access Web database templates available Assets Web Database Use this database site template to create a data base to keep track of assets The information stored includes asset details and owners Charitable Contributions Web Database Use this site template to create a Web database to track information about fundraising campaigns including donations made by contributors campaign related events and pending tasks Contacts Web Database Use this site template to create a Web database for managing information about the people you and your team collabo rate with such as customers and business partners Issues Web Database Use this site template to create a Web database to help you manage a set of issues or problems such as bug
4. Definition Language you can find out more about that in Book III Chapter 1 Then they re presented using SQL Server Reporting Services the Report Viewer Web Part renders the reports in the browser Access User Interface Macros JavaScript Macros attached to user interface objects such as buttons text boxes forms and reports are known as UI macros Data macros see the next item in this list are associated with tables Use UI macros to automate a series of actions such as opening a report when you click a navigation tab UI macros in SharePoint use JavaScript that is attached to your Web pages Access Data Macros SharePoint Workflows Data macros use and apply business level Access macros at the level of individual tables For example if you want to update the content of a field in a table when the content of another field in another table changes then you can easily achieve this using Data Macros When you publish your application to SharePoint the data macros you ve defined in your application are con verted to SharePoint workflows on the list Figure 1 8 shows you how SharePoint turns the data macros associated with the PurchaseOrders table in the Northwind Traders Web database application into workflows Lib Workflows Workllow Name click to change settings Workflows in Figure 1 8 All she Content Data macros are converted Book IV into Chapter 1 SharePoint The ae workfl
5. SharePoint 1 Using your browser navigate to the home page of your SharePoint site 2 From the Site Actions menu click New Site The Create site page appears displaying a list of the available SharePoint site templates sorted by category 3 From the list of categories click Web databases The list of available Access Web database site templates appears 4 Select the Web database template that you want to use as the basis of your site In the Title field enter the title for your site In the URL field enter the URL to your site Click More Options In the description field enter description for your site Book Chapter 1 suonesijddy 5992 612 Designing SharePoint Compatible Access Databases ar 9 Choose whether your new site will inherit permissions e Use same permissions as parent site Choose this option if you want the new site to inherit the permission structure from its parent site e Use unique permissions Choose this option if you want to specify per missions for the new site After the site is created you can not only manage the site permissions through the browser interface but also add and remove users and groups to suit your needs 10 From the Navigation Inheritance section accept the default setting of No so the new site won t inherit the top link bar from the parent site Regardless of whether you select Yes or No for this setting the top
6. appears Click the Solutions Gallery page to activate the Gallery Ribbon menu From the New Ribbon group click Upload Solution The Solution Gallery Upload Solution dialog box appears Browse to the Access Web database template file that you saved in Step 9 Click OK to upload the file Access Services gets busy and wraps the template file in a SharePoint solution file wsp Select the newly uploaded solution file and click the down arrow next to the file A shortcut menu appears From the shortcut menu click Activate The template now appears under the Web Databases category as a site template that you can select when you create a new site When you create a site based on your new Access site template Access Services extracts the solution file and converts what s in it to a SharePoint site suonesijddy 599 63 0 Book 10 Using the SharePoint Services
7. computer of your Web database application opens in Access 2010 ready for editing Any changes that you make won t show up in SharePoint until you synchro nize the local copy back to SharePoint Click the File tab to present the Access Backstage View Click the Options command The Access Options dialog box appears Click Current Database The options for the current database appear In the Web Display Form drop down list select the form you want as the default form when users navigate to the SharePoint site Click OK Save your changes The changes are saved to the local copy of the Web database application but won t show up in SharePoint until your synchronize them back to your SharePoint site Click the Info tab and then click Sync All Your changes are synchronized back to your SharePoint site If you refresh the home page of the SharePoint site that hosts your Web database application the new default Web form displays Publishing to Access Services Access 2007 provided the capability to publish your Access list into a SharePoint list Suddenly you could manage and control your data directly from SharePoint s browser interface With Access 2010 you can use the inte gration features to publish your entire database application into SharePoint along with its associated forms queries and reports as a SharePoint site suonesijddy 599 620 Publishing to Access Servic
8. the Blank Web Database template creates an Access 2010 Web database application which is compatible with Access Services by default let s hear it for good software design and may be published to SharePoint All menu options in the Access client application are filtered automati cally the only options enabled are compatible with SharePoint which helps keep your database application compatible That way you re less likely to run into problems during the publishing process If you create your Access database application using another template such as the Blank Database template you can still convert that appli cation to a Web database if it s compatible with Access Services Simply publish it to SharePoint If the database application publishes success fully it becomes an Access Web database application automatically Web Compatibility Checker You can use this feature to check your database application before you publish it to SharePoint so you can make sure that all the objects forms reports and code are compatible and translatable to Access Services The Compatibility Checker identi fies any issues that may prevent you from publishing your application successfully Armed with this knowledge you can troubleshoot the offending items before publishing to SharePoint The idea is to keep that experience clean and simple Figure 1 1 Creating an Access Web database application 609 Designing SharePoint Compa
9. 50 81 00 30219 boxe 10 00 14 00 18 40 9 65 546 00 24 pkgs 4 pleces 24 12 bottle 24 4 oz tins You can create a report very quickly if not quite yesterday by following these steps Open your Web database application in Access 2010 2 Click the Home tab to display the left navigation pane 3 From the navigation pane select the table or query you want to use to generate the report 4 In the Reports group on the Create tab click Report to create a Web report Access creates a report automatically and gives it a standard look and feel You can customize the report as needed changing the layout color scheme and adding or removing fields Setting the default form When you publish your Web database application to Access Services SharePoint accepts your application as a SharePoint site When you navigate Publishing to Access Services 6 19 to that SharePoint site you ll want to present your users with a form that acts as the launching pad for your application The form should contain nav igation components so your users can cruise easily from one form or report to another Here s how to set the default Web form that appears in your SharePoint site 1 2 Using your browser navigate to the SharePoint site that hosts your Web database application From the title bar click Options gt Open in Access A local copy which is a fancy way of saying on your
10. Chapter 1 Publishing Access Applications In This Chapter Designing SharePoint compatible Access Databases Understanding how your Access objects map to SharePoint Publishing your Access application to Access Services Creating your Access Web database site templates A ccess Services is a new feature that comes with SharePoint Server 2010 Enterprise Edition bringing the power and agility of Access appli cations into the flexible scalable and easily reachable SharePoint environ ment When Access Services is up and running in your organization here s what you get You can extend the reach of your Access database applications to the Web by publishing them to SharePoint Server 2010 Integrating the Access client application with SharePoint s Access Services architecture means a single click gets Access up and running in SharePoint providing all your users with a centrally managed Web accessible version of Access Publishing your Access application to SharePoint means that you have only one instance of the application to manage gone are the days of distributing multiple copies of your database software throughout your organization When a user adds removes or modifies data those changes auto matically accessible to all other users You can easily control the roles and access permissions on your data base application by using standard SharePoint permissions Any functional or schematic updates y
11. Database Objects Figure 1 5 809 Fitter Query Seareh BO Metro Commands You can tnd If m Gg Userintertace Commands d End Submacro mE Window Management ra 9 zj SO In this Database and drop ioni conditions nae and actions 0 zzzEraseOrders Reports from the Bled Tables Action Catalog Data macros Data macros are new with Access 2010 and allow you to add logic that is trig gered when specific events occur on a table such as adding updating or deleting data Data macros don t appear under the Macros command in the Access naviga tion pane Instead you can find them by selecting a table in your database and then clicking the Table tab from the Ribbon menu The Table tab gives you access to number commands that allow you to create data macros Before Change Allows you to create logic that runs before a change is committed to the database You can use this event to validate changes before saving them to the table Before Delete Allows you to create logic that runs before deleting a record You can use this event to validate the delete function or to cancel the deletion and raise an error After Insert Allows you to create logic that runs after a record has been added to the table Figure 1 6 The Query Designer makes queries easy to create 617 Designing SharePoint Compatible Access Databases After Update Allows you to create logic that runs after a reco
12. Web display form for your site you ll be staring at the Site Settings page which will have appeared in Step 1 until you specify the pesky Web display form Humor the machine and move on 3 Click the Modify this Web database add new fields customize forms and reports link The File Download dialog box appears 4 Click Open The Web database application opens in Access 2010 You can custom ize the database as needed When you re ready to push your changes up to the server flip ahead to the Synchronizing your changes with SharePoint section of this chapter and follow the instructions we left there Opening your database from the Options menu You can use the following instructions to open your published Web database application in Access 1 Using your browser navigate to your SharePoint Web database site 2 From the Options menu click Open in Access The File Download dialog box appears 3 Click Open The Web database application opens in Access 2010 You can custom ize the database as needed When you re ready to synchronize your changes with the server follow the steps in the Synchronizing your changes with SharePoint section of this chapter You can t use SharePoint Designer to open a Web database SharePoint site Instead you have to use Access just as you do if you re going to change or customize your database application suonesijddy 599 628 Publishing to Access Service
13. complete with appropriate button appears just below the Compatibility Checker option as shown in Figure 1 12 In the Server URL field enter the URL to the server running Access Services In the Site Name field enter the name you want to give to the new SharePoint site that will represent your Access Web database application Click the Publish to Access Services button The Synchronizing Web Application dialog box appears showing you a status bar while the conversion process runs If the publishing process is successful you receive a success message along with a link to the new SharePoint site If the publishing process is unsuccessful you ll see a message similar to the one in Figure 1 13 Oh well the world hasn t ended yet Try again Figure 1 12 The Publish command appears under the Compa tibility Checker Figure 1 13 Access notifies you if the publishing process fails Publishing to Access Services 625 al 9 1 Database Database Access 2007 Microsoft Access 2 Home Create External Database Tools File Types Access Services Overview GAY Save Object As Share your database with your team friends ar organization with Access B Save Database As al Save Database As Services and SharePoint Coen EA Use this if you want to SB close Database A Soe eget Ae Make your database available through a Web browser and Access Store tables in a central ShareP
14. e Filter Retresh Clipboard amp Filter 9 Web Compatibility Issues a Control Type Control Property Na IssueType Issue Type ID Description TableColumn Sample Error ACCWeb107000 Column data type is incompatible with the Web g 2 2 econ Lori WP Fi Search 4 2 Datasheet View T ea amp Book Chapter 1 suonesijddy 99 Guiysijqnd 62 4 Publishing to Access Services Figure 1 11 OLE Object data types aren t compatible with publishing to the Web Oops ACCWeb107000 Error text Field data type is incompatible with the Web Whatitmeans The indicated field has a data type that is not supported on the Whattodo Recreate the field as of the supported data types such as OLE Object Text Number Currency YesiNo Date Time Calculated field Attachment Hyperlink Memo Lookup The first time you publish your application to Access Services you use the Publish to Access command to deploy your application Follow these steps to publish your application 1 2 3 Open your Web database application in Access 2010 Click the File tab to present the Access Backstage View Click the Save amp Publish tab The save and publishing options appear In the Publish section click Publish to Access Services If this is your first time publishing the Web database application to Access Services a Publish to Access Services section
15. en your Web database application in Access 2010 2 Click the File tab to present the Access Backstage View 3 Click the Save amp Publish tab The save and publishing options appear 4 In the Publish section click Publish to Access Services The Compatibility Checker option appears on the right 5 Click Run Compatibility Checker to check to see whether your Access Web database is Web compatible If you have any objects open in your database a dialog box appears and asks whether you want to close those objects If you ve saved all those objects click Yes to have Access close them The Compatibility Checker presents a dialog box that shows the prog ress as Access processes every object in the database to check for com patibility issues If your database is compatible with the Web the Compatibility Checker dis plays a message to tell you so and the Web Compatibility Issues button is disabled If your database is incompatible oh the shame the Compatibility Checker shows the following attributes Figure 1 9 Access lets you know if your application isn t Web compatible Figure 1 10 Access provides a list of compatibility issues Publishing to Access Services 62 3 The Compatibility Checker is highlighted in red to indicate that errors turned up It s not embarrassed it just looks that way This status message appears The database is incompatible with the Web Press Web Compatibil
16. ent in the following sections Tables and Fields When you create your Access Web database you re initially presented with the Table Designer from which you can design the first table in your Web database application Figure 1 3 You can create many different form types in your Web database Designing SharePoint Compatible Access Databases 6 13 Okay we re not talking furniture here but you knew that In Access a table is a database object that stores information about an entity For example an Employee table may store information relating to all the employees in your company A table is a grid made up of fields not grassy places for playing ball or growing corn but smaller database objects that hold specific pieces of data arranged in rows that represent the items that the fields describe For example an Employee table may contain multiple fields describing an employee such as First Name Last Name Job Title and Employee ID and one row containing all these fields corresponds to one employee Fields are also commonly referred to as attributes or columns because each one occurs at the same place in every row and so stacks up as a column Rows are also called records or if you re being vague items At least one field in the table is used to uniquely identify an item or row In our example this unique field may be the Employee ID For search purposes this unique field is also known as the primary key
17. er some more with your changes in Access and then synchronize those changes back to SharePoint we have just the place to look for instructions yes the Synchronizing your changes with SharePoint section of this very chapter Book Chapter 1 suoneoiddy 99 Guiysijqng 626 Publishing to Access Services MBER amp Figure 1 14 Server side publishing errors show up in the Application Log Table Before you can publish your Access Web database application you must have Designer permissions in the SharePoint site to which you want to publish Viewing the Application Log In addition to the Compatibility Checker table you can check for error noti fications in the Application log USysApplicationLog for any server side errors that may have occurred during the publishing process Follow these steps to view the Application log 1 Open your Web database application in Access 2010 2 Click the File tab to present the Access Backstage View 3 Click the Info tab General options relating to you application appear including the appli cation log options 4 In the Application log section click View Application Log Table The application log table UsysApplicationLog appears Figure 1 14 shows the View Application Log Table command as it appears in the Microsoft Access Backstage View Application Log x Your application has no new errors View Application Log Table Opening
18. es Microsoft Access 2010 provides enough tools and wizards that you can easily create sophisticated Web database applications without writing a single line of code Publishing your database applications to Access Services extends their reach which makes Microsoft Access a powerful development platform for SharePoint Business units can maintain customize manage and deploy their own databases freeing up the IT staff to focus on running a stable high performance infrastructure That s the idea anyway Publishing Web applications to Access Services gives your users a common and familiar way to meet their business needs For example if your users are already taking advantage of SharePoint for collaboration and content manage ment you might as well give their productivity a boost by making their depart mental Access database applications accessible in the same environment Mapping Access Objects to SharePoint Objects When you publish your Access Web database application to SharePoint Access Services kicks off a process that gives every Access object a cor responding object in SharePoint Access Services enables your Access Web database applications to run the same way on both the server and the client machines The conversion process also ensures that your users can get to and use your Access applications regardless of which browsers they re run ning That s because the conversion process steers clear of special ActiveX type c
19. ity Issues to see errors The Web Compatibility Issues button is enabled Figure 1 9 shows the Compatibility Checker for a database that isn t Web compatible Check Web Compatibility 97 You check your database application for Web compatibility to identify items and settings that are not Run Compatibility supported on the Web Checker The database is incompatible with the Web Press Web Compatibility Issues to see errors Web Compatibility Issues When you click the Web Compatibility Issues button Access displays a list of all Web compatibility problems the Checker found along with a descrip tion of each error complete with Issue Type ID and a link to a Microsoft arti cle that provides more information on the error and a possible resolution Figure 1 10 shows an example of the compatibility error found in a database that uses on OLE data type for a field in one of its tables Figure 1 11 shows details of the error as they appear on the Microsoft site Publishing your Access Web database When you ve made sure that your database application is Web compatible you re ready to publish the Web database application to Access Services that is SharePoint All a is Databases Database Access 2007 Micros ee lene Fite Home Create External Data Database Tools 2 Al ascending Ee cai Nev a 22 seu r Z veseenaing Yge C View Past
20. lame Login FirstName 3 EmailAddress 4 OrderDate SheppecDate ShepAddress LastName Invoices Products OrderDetails Quantity UnitPrice v1 DueDete Tax Cost Shipping ListPrice StatusID Field orcer Date Table Orders Orde OrderDal Employee FullName Product Name Produ Sales ExtendedPrice Employees Products OrderDetails StateProw Customer Show y i E Criteria Book IV Chapter 1 suonesijddy 29 Guiysijqng 618 Designing SharePoint Compatible Access Databases Figure 1 7 Access creates a sample layout when you Create a report Reports Reports help you present the data in your database visually in the format your business users require For example you can create a report that makes use of charts and graphs if those get your point across best Figure 1 7 shows an example of a Web report created for the Products table in the Northwind Traders sample Access Web database Impressive isn t it Microsoft Access provides you with lots of tools to make sophisticated reports easy to create l Products 9 Products D lt 9 NWTO 5 10 NWTJP 6 11 NWTJP 7 13 NWTB 34 14 NWTCM 40 15 NWTSO 41 16 NWTB 43 Northy Northwind Tre Northwind Traders Beer 10 50 Northwind Traders iTrader 13 80 Northwind Traders Coffee 34
21. nd upload it to the SharePoint Solution Gallery When you upload an Access database template to the SharePoint Solution Gallery Access Services kicks into gear and wraps the template in a SharePoint solu tion file automatically Gotta love it Here s the process that deploys your Access Web database as a SharePoint site template 1 Open your Web database in Access 2010 2 Click the File tab to present the Access Backstage View 3 Click Save amp Publish and then click Save Database As A list of database file types appears on the right under the Save Database As section 10 11 12 13 14 15 16 17 15 Publishing to Access Services 62 9 From the list of file types click Template accdt The Create New Template from This Database dialog box appears Enter a Name and Description for your template Select an Icon that you d like to use for your template In the Instantiation Form field select the form you want to appear as the default display form If you want to include the data in the current database as part of the template select Include Data in Template otherwise leave the setting clear Click OK Note the location where your template is saved Using your browser navigate to the home page of the SharePoint site to which you want to deploy the template From the Site Actions menu click Site Settings From the Galleries section click Solutions The Solutions Gallery
22. nuous forms can only be created for table or Web queries Blank Form Creates a Web form that contains no controls or format ting Blank forms can only be created for tables or Web queries Datasheet Creates a Web form that displays multiple records in a data sheet with one record per row Web datasheets can only be created for tables or Web queries Navigation Create a Web form that allows you to move between forms and reports in your database application Navigation When you re creating a Web database application you ll need to provide your users with a way to get around between the various forms and reports in your application You can create your navigation system using Navigation Forms as mentioned in the previous bullet list When you re creating a navigation form Access provides you with a variety of navigation types including these Horizontal Tabs Allows you to add a series of tabs horizontally across your Web page Vertical Tabs Left Allows you to add a series of tabs vertically to the left of your Web page Vertical Tabs Right Allows you to add a series of tabs vertically to the right of your Web page Horizontal Tabs 2 Levels Allows you to add two levels of horizontal tabs across your Web page Figure 1 4 shows an example of this type of navigation form Horizontal Tabs and Vertical Tabs Left Allows you to add a series of tabs horizontally across your Web page and a series of tab
23. oint location QB Databases accan Publish Round trip queries forms code and linked tables that are not Web compatible ey Publish to Access Services Q Databese2 accad 0 Databasel accan A Central Valey Data istri a Package and Distribute Check Web Compatibility Info 18 Package Solution 4 You can check your dstabase application for Web Run Compatibility COmpatibility to identify items and settings that are Recent Checker not supported on the Web New The database is compatible with the Web Publish to Access Services 4 2 Full URL https sp20t0rep Employers Publish to Access Server URL http sp20 rep Save amp Publish Services ane a ah Site Emplayees Heg Recent Locations 2 Options i 5 2010 a http sp2010rep Bit spdevt SS http spdevOL Publish Access Application eS Publish Failed Your application has encountered compatibility issues before attempting to publish The publish operation has aborted and the target site has not been created For more details see the Compatibility Checker table Web Compatibility Issues You should run the Access Compatibility Checker before attempting to re publish your application When your database application is published the Publish to Access com mand is no longer available The command that replaces it is the Sync All which synchronizes your changes with the SharePoint server If you want to tink
24. ontrols using only standard Web HTML and JavaScript Here s a list of the Access client objects and how they re represented in SharePoint Access Web Database Application SharePoint Site When you publish an Access Web database application to SharePoint Access Services works behind the scenes to create a new SharePoint site for the Web database application Access Tables SharePoint lists Tables in the Access Web database are implemented in SharePoint as SharePoint lists The fields in an Access table are represented as columns in the SharePoint list reflect ing the appropriate data types For example if your Access Web data base application contains an Employee table that has First Name Last Name text fields an 1 integer field each field becomes a column in the Employee list with data types of Single Line of Text for the text fields and Integer for the EmployeeTD field Access Forms ASPX pages Forms that you create in your Access Web database are implemented in SharePoint as standard ASPX Web pages for rendering in the browser If you need to modify any of your pages you can implement the changes in the Access client application and syn chronize em back to SharePoint as needed Publishing to Access Services 621 Access Reports SQL Server Reporting Services Reports rd1 Reports that you create in your Access database application are con verted to RDL files Report
25. ou make to the database appli cation can be seamlessly synchronized with SharePoint ensuring that your users are always working with the most up to date version of your application 608 Designing SharePoint Compatible Access Databases In Book III Chapter 1 we look at the architecture behind Access Services and examine the settings that can help keep both your Access applications and your SharePoint environment running smoothly In this chapter we hop on board the Access Services train again to show you how easy it is to pub lish your Access applications to SharePoint Server 2010 Designing SharePoint Compatible Access Databases Access 2007 provided the capability to publish an Access list into a SharePoint list you could manage and control your data directly from SharePoint s browser interface With the Access 2010 integration features you can publish your entire database application into SharePoint includ ing all the forms queries and reports associated with your application Before you can publish your Access 2010 database application to SharePoint it must be compatible able to work seamlessly and play nice with Access Services Fortunately Access 2010 provides a lineup of tools to help you ensure that compatibility Access 2010 Web Database client template When you create a new Access database Access 2010 presents you with a list of templates you can use as the foundation of your database application Selecting
26. ows sel Workflow Setings page for that content type gt a os Access Queries System table If your Access database application 5 5 contains queries SharePoint puts them the System table that is a the MSysASO SharePoint list in the SharePoint site where the Access Services query processor renders the queries 622 Publishing to Access Services Checking Web compatibility Your Access Web database applications can be shared across your organiza tion by being published to Access Services If your database contains items and settings that aren t compatible with Access Services your application won t publish correctly Use the Compatibility Checker that Access provides to check your database before you publish it to SharePoint If compatibility problems turn up they land in a table called Web Compatibility Issues Most errors that the Checker identifies usually happen because some gremlin in the works tried to apply client only features or settings the ones that are supposed to be disabled when an Access database becomes a SharePoint site to your database You can avoid many of these errors entirely by selecting the Web database template when you initially create your database Doing so filters out many of the client only features prevent ing you from applying them to your database Follow these steps to check your Access database to see whether it s com patible with the Access Services 1 Op
27. rd has been edited in the table After Delete Allows you to create logic that runs after a record has been deleted from the table Named Macro Allows you to create edit delete or rename data macros that can be called from a macro or from event code Queries Queries are the heart and soul of most database applications allowing you to extract and filter the information you need when you need it You can use queries to extract data from a single table or multiple tables to perform calculations and to generate reports Queries appear in the Access left navi gation pane under the heading Queries When you re working with Web data base applications the queries you create are called with admirable clarity Web queries Access provides a Query Designer that provides you with a visual representa tion of the tables in your query and their associated relationships Using this feature you can specify which fields will appear in your query results as well as dictate the fields used to filter data and perform calculations on that data Figure 1 6 shows the SalesAnalysis query from the Northwind Traders sample Web database application The figure shows you all the rela tionships among the various tables that make up the query When you add tables to a query Access creates the relationship for you automatically in the Query Designer 5 SalesAnalysis _Employess Customers Orders Email Sa Company Fulit
28. s Synchronizing your changes with SharePoint After you ve initially published your Access Web database to Access Services the way you synchronize all subsequent database modifications and customizations with SharePoint is to use the Sync All command in the Access client application Here s how to synchronize your database application changes to SharePoint 1 Open your Web database application in Access 2010 2 Click the File tab to present the Access Backstage View 3 Click the Info tab General options relating to your application appear including the syn chronize options 4 Click the Sync All command to synchronize all your changes with the server and reconnect all the tables The Synchronizing Web Application dialog box appears showing you a status bar so you can keep track of the conversion process Saving your Web Database as a Site Template The integration between Microsoft Access 2010 and Access Services makes it easy to create an Access Web database template and implement it as a site template in SharePoint SharePoint Server 2010 implements site templates as solution files wsp If you haven t created a solution file yet don t despair there s an easy way With Access 2010 handy you don t have to figure out how to package your database as a SharePoint solution file Instead you wave your magic wand well almost You simply save your Web database as a regular Access tem plate file accdt a
29. s vertically to the left of your Web page Horizontal Tabs and Vertical Tabs Right Allows you to add a series of tabs horizontally across your Web page and a series of tabs vertically to the right of your Web page Figure 1 4 You can create a two level navigation bar Designing SharePoint Compatible Access Databases 615 Employees CustomerList Customer List Customer Name Contact Phone Number Address Customer A Anne Bedecs Mobile 123 1st Street Owne r Business 123 555 0100 Seattle WA 98052 USA Home Fax 123 555 0101 Customer B Antonio Gratacos Solsona Mobile 123 2nd Street Owner Business 123 555 0100 Boston 98112 USA Home Fax 123 555 0101 Macros Access 2010 provides a new and improved Macro Builder also referred to as the Macro Designer to allow you to add logic to your database to auto mate repetitive tasks and create a more usable interface Using the Macro Builder you can quickly define conditions and actions for your macros When you re creating Web macros the Macro designer filters out actions and expressions that don t translate to Access Services taking much of the guess work out of creating macros and ensuring that the publishing pro cess is as easy and efficient as possible The designer allows you to expand and collapse the macro data in much the same way you d expand and col lapse the elements of an XML file When a macro appears in expanded vie
30. tible Access Databases Creating an Access Web database Here s how to create an Access Web database from the Access 2010 client that can be published to Access Services 1 Launch the Access 2010 client 2 From the File menu click New A list of the available Access templates appears 3 From the list of available templates select Blank Web Database and then click Create Figure 1 1 shows the list of available Access templates that appear in the Access 2010 client Help E Options bt Microsoft Acce Datab Available Templates Blank web database Home Blank Blank web Recent Sample database database templates tem lates My templates Office com Templates E g 1 ig Assets ortae Issues amp Non profit Task mmia ects When you click the Create button the Access client opens and filters the menu options to present you with only Access Services compatible com mands You re now ready to create your Access Web database application Creating an Access Web database from SharePoint SharePoint 2010 provides a list of available Access Web database site tem plates from which you can create your SharePoint site When you create a Book IV Chapter 1 suonesijddy 99 Guiysijqng 610 Designing SharePoint Compatible Access Databases MBER KV amp Figure 1 2 You can create your Access Web database from SharePoint SharePoint site based on one of these templates
31. tracking for Designing SharePoint Compatible Access Databases 611 an application under development You can assign prioritize and follow the progress of issues from start to finish Projects Web Database Use this site template to create a project tracking Web database that you can use to follow the progress of multiple projects stage by stage and assign tasks to members of your team In addition to the standard templates that come with SharePoint you can find a plethora of Access Web database templates on the Internet not only from Microsoft but also from third party vendors For example the Microsoft Web site provides you with a sample Web database application starring the intrepid imaginary company Northwind Traders that s a virtu ally painless introduction to the process of publishing and synchronizing content from the Access client application to Access Services You can customize these templates to suit your business needs and then upload them to your Site Template Gallery for use in your SharePoint envi ronment Alternatively you can simply publish the Web database applica tion to SharePoint creating a SharePoint Web application Using a template to create your Web database application means you don t have to create your application from scratch which saves time you can get your applications up and running as quickly as possible Follow these steps to create an Access Web database application directly from
32. w it looks very similar to viewing code Figure 1 5 shows an example of the zzzEraseOrders macro from the Northwind Traders sample Access Web database from Microsoft The macro is being viewed in design view using the Macro Builder The Action Catalog appears on the right side from which you can drag and drop the conditional statements and corresponding actions as needed The In This Database node in the Action Catalog allows you to view all the macros you ve already created in the current database in addition to other objects in the database such as tables forms and reports You can reuse the logic created from other macros by simply dragging and dropping them from this node onto your new macro Follow these steps to create a macro Open your Web database application in Access 2010 2 On the Create tab in the Macros amp Code group click Macro Clicking the Macro command launches the Macro Builder which you can use to build your business logic Book Chapter 1 suonesijddy 99 Guiysijqng 616 Designing SharePoint Compatible Access Databases 3 Build your business logic using the Action Catalog 4 On the File tab click Save to save your macro The macros that you create appear under the Macros in the navigation tree Tahan zzzEraseOrders Action Catalog x i Program Group TempVars YesNoRes BO Actions Data Enty Operations 5 F mA
33. your Access Web database application When you successfully publish your Access Web database application to Access Services a SharePoint site is created sporting the name you sup plied during the publishing process When you navigate to the site the Web Access application appears looking the way you specified as the site s default Web display form If you haven t yet defined a default Web display form for the site that s a minor gaffe and see the Site Setting page below The publishing process stores the entire Access Web database application in SharePoint Thus you can use SharePoint as your launching point for edit ing your application in Access Even if you lose all the local copies of your database application you can still open the application from SharePoint which downloads a new copy of the application to you linked to the server ar ar MBER Publishing to Access Services 627 When you open your Access Web database application from SharePoint you are in effect opening an offline copy of your database application But not to worry Any changes you make while offline can be synchronized back to the server Opening the database from the Site Settings page Here s how to open your published Web database application in Access 1 Using your browser navigate to your SharePoint Web database site 2 From the Options menu click Settings to display the site settings page If you haven t defined a default
Download Pdf Manuals
Related Search