Overview
Access 2010 templates are
accessible from Access Backstage and are ready-to-use and free database
solutions complete with tables, queries, forms, and reports built for common
scenarios. In addition to the templates shipped with Access 2010, there are
numerous third party templates available at office.com.
Many times, you may find that
it is much easier to customize a template to a customer’s unique needs rather
than start from scratch. In this exercise, you will learn how to perform tasks
related to template customization and save development time.
Consider the needs of a human
resources manager who needs to track job applicants. A good place to start in a
situation like this is to see if there are any templates available that you
could use as a starting point. In Access 2010, the Contacts Web Database
template serves as an excellent starting point for a job applicant tracking
system. Since the purpose of this exercise is to get experience modifying web database
objects, we will use the Contacts Web Database template as a starting point for
this exercise.
How
to do
One common customization task
when starting with a template is to modify the structure of existing tables in
the template to suit your unique needs. Besides generic contact information, a
job applicant tracking system needs to keep track of the details of the jobs
that are available. In the first task, you will create a new database based on
the Contacts Web Database template and then create a new table called Jobs for
tracking available job details.
1. Open Microsoft Access 2010
2. Click on Sample templates
3. Select the Contacts Web Database
4. Change the File Name on the right side to Job
Applicants
5. Click Create
6. Check out the various items in the database. In particular, check out the four tabs:
a. Address Book –
shows list of contacts and details of selected contact
b. Datasheet –
shows a list of contacts in data sheet view
c. Report Center –
shows a list of available reports and currently selected report
d. Getting Started
– provides links to helpful information
7. Close the Main
tab by right-clicking on the tab and choosing Close
8. Expand the Navigation Pane. The navigation pane is the pane on
the left-hand side of the application window that shows objects in the
database. You can hide/show this pane by clicking on the “>>” button in
the top-right corner of this pane.
9. Take a minute
and review the Tables, Queries, Forms, and Reports in the database
10. Create the Jobs
table. The Jobs table is a table that contains fields for storing the details
related to the available positions. For example, it will store the position
number, position title, status, and creation date, a description of the
position, and the department of the position. As you perform these steps, keep
in mind that you are working with a web database. Using web databases imposes
some limitations on the types of functionality you can use in a database so
that the database can run on SharePoint. The benefit of starting with a web
database is that you can publish the database to the web with one click. You
will notice that the design experience for creating and modifying web database
objects is a little different from the design experience for modifying client
only objects. To design a web table, you perform the modification in datasheet
view using the functionality exposed in the Table Tools ribbon tabs.
1. On the Create tab, click Table
2. Select
the ID column, choose Name & Caption from the ribbon
3. Set the Name
and Caption to Position Number and click OK
4. Click Click
to Add in the next column and select Text. This process sets the data
type of the field and highlights the column heading so that you can enter the
name of the field.
5. Name the
field Position Title
6. In a similar
manner as steps d and e, create the following fields. The list below shows the
name of the field followed by the data type in parenthesis:
a. Date Created
(Date & Time)
b. Description
(Memo)
c. Department
(Text)
7. Create the next
field using the Lookup & Relationship data type. A lookup field
allows you to control the possible values for a field based on a list of
possible values. Use a relationship field for establishing relationships
between tables in a database.
8. Select I will type in the
values that I want and click Next
9. Underneath the Col1 column heading, enter the following values: Open, Closed
– filled, Closed – cancelled, Hold.
10. Click Next
11. Set the label
for the field to Status and check Limit To List
12. Click Finish. The
following screen shot shows an example of the completed jobs table after
entering a few records. Notice that the Status field contains the list of
values you entered in step i previously.
In addition to adding tables to
a template, you often need to modify existing tables. In this task, you will
modify the contacts table to store additional information related to tracking a
job applicant.
1. Under Tables, double-click Contacts to open the Contacts table
2. Click on the Company column header
3. On the Fields tab, select Name & Caption
4. Change the Caption from Company to Employer. In
practice, you could also change the Name of the field. Changing the name
however, could break any references to the field in queries, forms, or reports.
In the interest of time for this exercise, leave the name the same so that you
don’t need to worry about breaking any references.
5. Add new Column Initial Interview type “Date & Time”
1.
Navigate to Backstage via the File tab and click Options
2.
Select Current
Database and then choose Main to be the starter Web Display Form for the web user experience and click OK. (Follow any subsequent prompt instructions
accordingly.)
3.
In Backstage view,
click Publish to Access Services.
4.
To confirm your
database is web compatible, click Run Compatibility Checker. (There should
be no errors since this lab’s exercises use web compatible objects. If you
receive errors, fix them and continue. If you are prompted to close open
objects, close them and run the compatibility checker again.)
5.
Type your SharePoint
2010 Server URL, Site Name (JobApplicants
in this case) and click Publish to Access Services.
- Get error
- Make sure Access Service Application Proxy is started
- Make sure Access Database Application is stared
- Click to custom link yo your web application
- Check to Access Service
Application
11.
Access shows the
publishing progress.
12.
When publishing is
complete, the Publishing Succeeded dialog will display. Click the link to access the site.
13.
View your new web
application in SharePoint 2010!
a.
Add new contact form
b.
Result as
c.
Click to Report Center then get Error
d. How to solve
a. Click Start,
click All Programs, click Microsoft SharePoint 2010
Products, and then open the SharePoint Management Shell.
b. Type the
following command, and then press ENTER:
Enable-SPSessionStateService
– DatabaseName "<your string>"
Note In this
example, <your string> is a string that represents the
name that you want to specify for the new session state database, as in the
following example:
Enable-SPSessionStateService
–DatabaseName "Session State Database"
c. 3. Restart
Internet Information Services (IIS). To do this, type the following command (Do
not enclose the command in quotation marks) in the SharePoint Management Shell,
and then press ENTER:
d. Database was
created
e. Click to
report center and select some report, don’t have the error
