Preparing a Client Database for the Web

If you create a new web database from scratch or use a web database template, you are less likely to encounter any errors when you run the Web Compatibility Checker tool because you start out within a confined design surface. But what happens if you have an existing client database and want to publish it to a SharePoint server running Access Services to make it a web application? If you have a client database you want to publish to the server, your first step is to run the Web Compatibility Checker.

In the previous section, we mentioned the Web Compatibility Checker checks all local tables and web objects. In a client database, this means the Web Compatibility Checker tool only checks the local tables because the other objects will remain client objects. To publish to the server, you need all the schema in your database to be web compatible. The Web Compatibility Checker tool helps you migrate existing client databases to the server by showing what changes you need to make to your tables for the database to successfully publish to a SharePoint server.

Let's use one of the built-in client database templates to illustrate this process. Close any databases you have open by clicking the File tab on the Backstage view and then click Close Database. Next, click the Sample Templates button on the New tab to show all the built-in local templates. Finally, click the Faculty client template in the center of the screen, select a location to save this new database, and then click Create to create the new client database.

Access creates the new client database and displays the startup form for the database-the Faculty List form. To run the Web Compatibility Checker on your client database, click the File tab on the Backstage view and then click Save & Publish. Next, click Publish To Access Services and then click Run Compatibility Checker. Note that Access prompts you to close all open objects before it begins to scan the database. Click Yes on the dialog box prompt, and the Web Compatibility Checker tool now scans your client database. Once it completes the scan, Access reports on the Backstage view that your database is incompatible with the web. Click the Web Compatibility Issues button on the Backstage view and Access opens the Web Compatibility Issues table.

The Web Compatibility Checker found five issues with the Faculty table in this client database. If you look at the Description field for each error, you'll notice that each error is identical. In this client database, the Web Compatibility Checker found five field names that are incompatible with the server. Specifically, each of these five field names contains the forward slash character (/), which is incompatible with the server. To fix these issues, you can open the Faculty table in Design view or Datasheet view and remove the forward slash character from each of the five field names listed in the Web Compatibility Issues table- Education Level/Degree, School/Program Name, State/Province, ZIP/Postal Code, and Country/Region. After you make these changes to the Faculty table and save your changes, you can run the Web Compatibility Checker tool again. You'll now receive a successful message on the Backstage view that your client database is compatible with the web. Note that because you changed field names in the table, you should verify that the rest of the application still functions.

Most existing client databases have more potential compatibility issues than just invalid column names. The Web Compatibility Checker does a good job of providing specific help topics to inform you what you need to change to make your schema web-legal. Listed below are some of the more common issues found in client databases that you might encounter when trying to prepare your database for the web:

• Incompatible relationships
• Lookup field row sources
• Primary keys that are not Number that have the Field Size property set to Long
• Composite indexes
• Custom field formats
• Subdatasheets
• Other field properties

Before making any changes to your client tables, we recommend creating a backup copy of your database in case you need to refer to the original. In general, most of the issues you'll need to fix involve adding, editing, or removing specific field properties. To fix these issues, you need to open the client table in Design view and make the appropriate changes. For errors concerning relationships, you'll need to take additional steps to fix these errors, especially if you have existing data in your client tables. First, you'll need to open the Relationships window and delete the offending relationships. Next, you'll need to make sure you have an appropriate Primary Key. The simplest solution here is to add a new AutoNumber field to your client table, if one does not already exist, and make that field the Primary Key. You should then use the Lookup Wizard to create relationships to other tables. If you have existing data in your tables, you'll also need to run some action queries to adjust the data so all the parent and child data is mapped correctly.

In this tutorial, you've taken the first steps to learn how to create a web database. You've learned how to create a web database from scratch and create your schema and relationships between different web tables. You've also learned how to use the Web Compatibility Checker tool to verify your tables are web compatible.