Changing The Design Of A Table Access 2013

In Section 2, “Creating a custom Web App,” we demonstrate how the desktop interface can allow you to display content both by using the Table Selector and by showing the navigation pane. You can use either of these interfaces to enter design view and change the design of your table. Here we show how to enter design view from the table in the Table Selector. (If you prefer the navigation pane, right-click an object to see similar options.) 

In Access 2013, the available data types for fields have changed, and the most obvious change is that a Text field is now called a Short Text field and a Memo field is called a Long Text field. However, there are some other differences between the avail­able data types in a Web App compared to a desktop database.

Change the design of a table:

1. Right-click a table, and select Edit Table. 

2. Click the Data Type drop-down arrow to see the available choices of data types. 

Change the design of a table:

The following table describes the data types that are available. For each data type, we show in parentheses the SQL Server data type that is used to store the data. 

Data type	Description (SQL Server data type)
Short Text	Unicode Text field allowing a maximum of 4,000 characters (nvarchar(4000)). The default is 220 characters.
Long Text	Large amounts of text (corresponds to a nvarchar(max), 2^30–1 bytes in SQL Server 2012).
Number	Whole number with no decimal places (int), Floating-point number (float), and Fixed-point number (decimal(28,6), six decimal places).
Date/Time	Date (date), Time (time(3)), and Date with Time (datetime2(3)).
Currency	Money (decimal(28,6)).
Yes/No	(bit) True, False (default is False).
Hyperlink	Large amount of text (nvarchar(max)).
Image	Binary Image data (varbinary(max) 2^31-1 bytes in SQL Server 2012).
Calculated	Calculated field using SQL Server functions. Storage depends on expression.
Lookup	Creates a foreign key lookup. (Lookup foreign key stored as integer (int)).

TIP: Behind the scenes in Office 365, your data is stored in Microsoft SQL Server for a corporate solution or using Micro­soft SQL Azure in conjunction with Office 365. SQL Server is similar to Access in being a relational database but it is more scalable (so it can handle more data that the normal Access Database), and SQL Azure is a version of SQL Server that it is the platform that Microsoft use to deliver data on the web. We only explain the underlying SQL Server data types here for reference; you don’t need to learn anything new to use this.

Importing From An Access Desktop Database Access 2013

If you have an existing Access Desktop database, you can import the tables and, optionally, the data from that database into a new Web App. The data can be imported from a variety of sources, including an Access desktop database. 

Where possible, the process of importing data will also import relationships between your tables and convert these to look­ups. If you don’t have an appropriate database available to investigate the techniques described in this section, you can refer to Section 1, “About this book,” for information about how to download the sample data that we have used in preparing this section of the book. 

We start with a new custom Web App into which we will import existing data. 

Import an Access database:

1. Click Table on the Home tab of the ribbon. 
2. On the Add Tables screen, click Access. 
3. Click Browse. 
4. Locate the Access desktop database file. (If you do not already have an accessible database file, you will not be able to complete any further steps until you have installed or created one.) 
5. Click Open. 
6. Click OK. 

Import an Access database:

• 7 Select the tables to import, or click Select All. 
• 8 Click Options to display further options when importing data. 
• 9 Choose to import or exclude the relationships. 
• 10 Choose to import either definitions only or definitions and data. 
• 11 Click OK. During the import process, your existing relationships will be converted into Lookup data types. 
• 12 Right-click the table that has relationships to other tables, and select Edit Table. The relationship is now shown as a Lookup data type. 
• 13 Close the table. 

CAUTION: Data types such as Attachments or OLE Objects are not supported in a Web App and will be excluded from the import process.

Creating A Web App Using A Template Access 2013

Access has a number of application templates that you can use to create complete applications. When these templates start with a name beginning with Desktop, they generate desktop databases, and the other templates will generate Web Apps. 

After you start Access, the initial screen shows a choice of both desktop and Web App database templates. 

Create a Web App using a template:

1. Click the appropriate template in the Office Start Screen. 

2. Type a name for your Web App in the App Name text box. 

3. Type your Office 365 URL in the Web Location text box, or select from the Available Locations list. 

4. Click Create.

Finding Your Site And Navigating To The Team Site Access 2013

Your team site contains all your Web Apps. If you start by launching your Web App, you can use the Navigate Up menu choice to get to the site, or you can get to the site from your main Office 365 Account area. On the left menu, the Recent choices will display a list of Recent items. Use the Site Contents to display all items on the site. 

If you are using a different computer, where your copy of Access does not have your site located as a Recent item, you can go to Team Site – Site Contents and open your Web App in Access. 

Find a site:

1. Click Back To Site. 
2. Click Site Contents. 
3. Click the ellipse button (...) to display other options for an application. 
4. Close the options for the application, and click a Web App to display the application in the browser window. 
5. Click Customize In Access. 
6. Click Open to launch your Web App in Access, and answer OK to any further security prompts to open the Web App on your desktop.

Working With A Summary View Access 2013

A summary view is the third way of presenting data. Summary views created by templates typically use By Group as the label in the view selector. Summary views allow you to view a tabulation of data by grouping on a particular field. You can also filter the grouping field to limit the displayed groups. 

A summary view combines a list of values with the related item control, which tabulates the matched records. The view is read-only. 

Work with a summary view:

1. Find a suitable table that has a By Group view. Click By Group. 
2. Type any filtering data to reduce the visible groups. 
3. Click the search symbol, and then click a desired group to display the members of the group. 
4. In views created by templates, you can click the tabulated record to display a popup window for a detailed record.

Working With The Datasheet View Access 2013

The Datasheet view is the second of three presentations for data; this presentation provides a tabulation of the data and can be scrolled left to right. 

When working with the Datasheet view to enter new data, you can start by typing into a blank row or by clicking the plus sign (+) on the action bar. To edit an existing row, type into a cell in the row. Records and changes are saved when you click a cell in another row. 

The columns can be reordered and adjusted in width, and the rows can be sorted and filtered. 

WORK WITH THE DATASHEET VIEW: 

1. Click Datasheet. 

2. Type new data in a record. After you move out of the first field, the record selector on the left changes to a pencil shape. 

3. Click a cell in any other row to save your changes. 

4. Point to a column heading until the mouse shape changes to a cross. Click, hold, and drag the column to a new column position to reorder the column. 

5. Click the drop-down arrow to display options for filtering and sorting data.

Working With The List View Access 2013

The List view is the first of three presentations of data shown for the selected table. The List view is split into two areas. The first area, called the list control, shows a simple tabular list of the records on the left side of the page, from which you can select 

a specific record. The full details of that record are displayed in the second detail area, on the right side of the List view. As new records are added or removed, the list control area automati­cally updates when the changes in the detail area are saved. 

Work with the List view:

1. Click List. 
2. Click the Edit button on the action bar. 
3. Look for a field that displays the text Find. This is the new Autocomplete control. (In our example, both the Customer and Employee choices are Autocomplete controls.) 
4. As you type into an Autocomplete control, any matches are displayed. The control also has an <Add A New Item> option. Click this option. 
5. After choosing to add a new item, type new data values. 
6. Click the Save button on the action bar. 
7. Close the window; your newly created record is now displayed as having been selected in the Autocomplete control. 

TIP: In our example for the Orders view, below the main order you can see an area for displaying and entering order details. This uses another new control, called the Related Items Control (RELIC).