Page 5 of 7PREVNEXT

Build and publish web databases

We can’t provide a regular practice session because we can’t emulate a connection to a SharePoint server. But these suggested exercises demonstrate a best practice for using web database templates – you download them, get them into the form you want, and then publish. This may be a lightweight exercise for experienced Access users, but you’ll get a feel for web databases.

Exercise 1: Download a web database template

  1. Start Access. It opens in Backstage view.
  2. On the New tab, click Sample Templates.

The list of templates may take a few moments to load.

  1. Scroll down to and click the Issues Web Database template.
  2. In the File Name box, enter a name for the template.
  3. Optionally, click the folder icon next to the File Name box and browse to a location for the new file.
  4. Click Create. The database downloads, opens, and asks you to log in. Keep going.

Exercise 2: Trust the database and log in

  1. In the Login dialog box, click the X in the upper-right corner to close the dialog box.
  2. In the yellow Message Bar, click Enable Content. That adds the database to your list of trusted files, and you never see that message again for that database on that computer.

After you trust the database, the Login dialog box reappears.

  1. Click New User.
  2. In the User Details dialog box, enter at least one full name. For this exercise, any name will do. You can also add an email address and network domain, but they’re optional.
  3. Click Save & Close when you’re done.
  4. In the Login dialog box, click one of the names you just entered and click Login.

Exercise 3: Change placeholder values

  1. In the template, the Navigation Pane is closed by default. To open it, click the double arrows (>>) at the top off the pane.
  2. Under Tables, double-click the Issues table to open it in Datasheet view.
  3. Open the lists in the Status, Priority, Category, and Project fields. To do so, click the field (the blank row just beneath the field name) and then click the arrow that appears. Note the values each field contains.
  4. Right-click a record in any of those fields and click Edit List Items.
  5. Use the Edit List Items dialog box to change the values in the list. For example, in the Category field, replace the original values with “Choose One,” “Production,” “Purchasing,” “Assembly” and “Sales.”
  6. Optionally, use the Default Value list to designate one of the list items as a default value. To do that, open the list and select a value. For example, use the “Choose One” value.
  7. Click OK to close the dialog box.

Exercise 4: Add a field to a form

  1. Click the document tab for the Main form, and then in that form, click the Open Issues tab.
    The Main form and Open Issues tab
  2. Right-click the document tab for the Main form and click Layout view.
    Opening a form in Layout view

The Field List task pane appears on the right side of your screen and displays the fields in the Issues table.
Note    If the task pane doesn’t appear, do the following:

  1. Go to the Ribbon and on the Design tab, in the Tools group, click Add Existing Fields.
  2. Expand the Issues node.
  1. On the Main form, in the Open Issues tab, click the Status field. If you don’t select a location in the Open Issues form, the field will land in the layout above or outside the form. In other words, the field will land in the Navigation form.
  2. Drag the Project field from the Field List task pane and drop it next to the Status field. The orange drop bar shows you where the new field will land.
  3. Press CTRL+S to save any changes, or click Save on the Quick Access Toolbar.

Exercise 5: Publish a database the first time

You only publish a web database once. After that, to save any changes, go to Backstage view, and on the Info tab, use the Sync All command.

Note Before you publish, contact your I.T. department for the URL of your SharePoint server.

  1. Click the File tab to go to Backstage view.
  2. Click the Save & Publish tab, and then click Publish to Access Services.
  3. Optionally, click Run Compatibility Checker. You don’t need to run the check if you’re publishing one of the Access templates. But if you make a major change to a database, such as altering your table schema, or you build a web database from scratch, it’s a good idea to run the check.

Note    If the compatibility check returns an error, you’ll find help in the Quick Reference card. Look for the links in the section that corresponds to this exercise.

  1. In the Server URL box, enter the URL of your SharePoint server.
    Use a fully qualified URL (htttp://server name). Notice the Publish to Access Services button only becomes available after you enter the URL. Also, once you enter a URL, Access adds it to the Recent Locations list for easier reuse.
    Practice Image
  2. In the Site Name box, enter a name for your site. For example:

Practice Image

The name and site URL combine to become the full URL of your site.

Note    Write down the full URL so you can navigate to your database after it’s published.

  1. Click Publish to Access Services. If prompted to save any changes, click Yes.

The publish process runs, and then displays a success message. You can start using your site.

  1. Close Access.

Exercise 6: Change a published database

Important    You must have Full Control permissions on your SharePoint server to change a published web database.

This exercise uses the Issues web database that you published in the previous exercise. If you want to use another database, substitute your steps for the ones listed here.

Note    These steps assume you closed Access at the end of the previous exercise. If you didn’t, do so now.

  1. Start your browser and navigate to your published web database.
  2. Open the Options list and click Open in Access.
    Opening a web database from SharePoint
  3. In the File Download dialog box, click Open again.
  4. In the Microsoft Access dialog box, click OK. Optionally, click Don’t show this message again before you click OK.
    The database opens in Access and displays the Main form.
  5. Right-click the document tab for the Main form and click Layout View.
  6. On the Main form, click the Getting Started tab and press DELETE.
  7. Press CTRL+S, or go to the Quick Access toolbar and click Save.

Exercise 7: Save (synch) a changed database to the web

  1. Click the File tab to go to Backstage view.
  2. On the Info tab, click Sync All.
    The publish process runs a compatibility check and synchronizes the database in Access with the database on SharePoint.

Note    If the compatibility check returns an error, you’ll find help in the Quick Reference card. Look for the links in the section that corresponds to this exercise.

  1. In your browser, navigate to the published site. If necessary, press F5 to refresh the page and see your changes.

Reasons for adding client objects to a web database

At times, you may want to include client objects in your web databases. Here are some possible reasons why:

  • Your boss wants a report that only she can read.
  • You need to use a Crosstab query, or another client-only query such as Find Duplicates. Note that you use the Query Wizard to create those types of queries.
  • You need to create a PivotChart or PivotTable form for in-depth analysis.
Page 5 of 7PREVNEXT