MS-Access / Getting Started

---

Understanding Web Field Data Types

When you are creating fields for a web database, Access allows you to use only data types that are supported in SharePoint lists. Access 2010 web databases support 10 types of data, each with a specific purpose. You can see the details about each data type in Table-1.

The Lookup & Relationship option helps you define the characteristics of foreign key fields that link to other tables or value lists.

Table-1 Access Web Data Types

Data Type	Usage	Size
Text	Alphanumeric data	Up to 255 characters
Number	Numeric data	All numbers in web tables are 8-byte Double
Currency	Monetary data, stored with four decimal places of precision	8 bytes
Date/Time	Dates and times	8 bytes
Yes/No	Boolean (true/false) data; SharePoint stores the numeric value zero (0) for false, and one (1) for true	1 byte
Lookup & Relationship	When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row	Dependent on the data type of the lookup field
Memo	Alphanumeric data-sentences and paragraphs	Values are truncated if they contain more than 8,192 characters
Attachment	You can attach files such as pictures, documents, spreadsheets, or charts; each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the SharePoint list. You can only have one Attachment field per web table	Varies based on SharePoint list and site settings
Hyperlink	A link "address" to a document or file on the World Wide Web, on an intranet, on a local area network (LAN), or on your local computer	SharePoint Hyperlink fields can only store 255 characters for the Uniform Resource Locator (URL) and 255 characters for the description
Calculated	You can create an expression that uses data from one or more fields. You can designate different result data types from the expression.	Dependent on the data type of the Result Type property. Text data type result can have up to 243 characters. Memo, Number, Yes/No, and Date/Time should match their respective data types

For each field in your table, select the data type that is best suited to how you will use that field's data. For character data, you should normally select the Text data type. You can control the maximum length of a Text field by using a field property, as explained later in this tutorial. Use the Memo data type only for long strings of text that might exceed 255 characters or that might contain formatting characters such as tabs or line endings (carriage returns).

The Date/Time data type is useful for calendar or clock data and has the added benefit of allowing calculations in seconds, minutes, hours, days, months, or years. For example, you can find out the difference in days between two Date/Time values.

Use the Date/Time data type to store any date and time value. It's useful to know that SharePoint lists, like Access 2010, store the date as the integer portion of the Date/ Time data type and the time as the fractional portion-the fraction of a day, measured from midnight that the time represents. For example, 6:00:00 A.M. internally is 0.25. In SharePoint Date/Time fields, the day number is actually the number of days since January 1, 1900, and cannot display dates prior to that date. The Date/Time data type in Access client tables starts with December 30, 1899, and can be a negative number for dates prior to that date. If you are working in a web database with web tables, however, you cannot store any date values prior to January 1, 1900, because SharePoint Date/ Time fields do not support data before that date.

You should generally use the Currency data type for storing money values. Currency has the precision of integers, but with exactly four decimal places.

Use the Yes/No data type to hold Boolean (true or false) values. This data type is particularly useful for flagging accounts paid or not paid or orders filled or not filled. SharePoint Boolean fields store True values as 1, unlike Access Boolean fields, which store True as -1. To work around this disparity, you should only use check boxes to display Boolean fields in web databases so the users of your database are not looking at an integer value for the field. (When you create a Boolean field in a web table, you'll notice Access only gives you the option to display check boxes.) If you attempt to use any restrictions, filters, or comparisons on the integer value of Boolean fields on server forms, you'll see a runtime error. Note that it is still possible to use the integer value of Boolean fields in restrictions, filters, and comparisons in Access client with data published to the server; however, we recommend you always use True and False constants when working with Boolean fields in web databases to ensure consistent behavior in both client and server.

The Hyperlink data type lets you store a simple or complex "link" to an external file or document. (Internally, Hyperlink is a memo data type with a special flag set to indicate that it is a link.) This link can contain a URL that points to a location on the World Wide Web or on a local intranet. It can also contain the Universal Naming Convention (UNC) name of a file on a server on your LAN or on your local computer drives. The link can point to a file that is in Hypertext Markup Language (HTML) or in a format that is supported by an ActiveX application on your computer. If you are using existing data in a Hyperlink field before publishing your database to a SharePoint server, be careful about how many characters are stored in your hyperlinks. Hyperlink fields in client tables (and web tables before publishing to the server) can support more than 255 characters; however, Hyperlink fields in SharePoint lists support only up to 255 characters. Access prevents you from publishing your data to the server if any Hyperlink fields contain more than 255 characters. You need to edit or remove any hyperlinks that contain more than 255 characters before you can successfully publish your web database to the server.

The Attachment data type, introduced in Access 2007, allows you to store multiple attachments in a single record. These files are stored in a binary field in a hidden system table. For the Attachment data type, Access compresses each file, if it isn't already, and uses the original file rather than a generated thumbnail to minimize the amount of database bloat. You are restricted to only having one Attachment field per web table in web databases. Access displays an error message if you try to create more than one Attachment field in a single web table. SharePoint lists have a default size limit of 50 MB for each attachment. If you have attachments larger than 50 MB, you might encounter problems publishing your application to the server or syncing your data to the server. The attachment size limit is an administrative configurable setting. Contact your SharePoint server administrator if you are experiencing issues publishing larger attachments.

The Calculated data type, newly introduced in Access 2010, can also be published to the server. When you publish a web table to the server that includes a Calculated data type, Access creates a Calculated column in the SharePoint list and sets the expression to match what you defined in Access client. Calculated fields allow you to create a calculated result using an expression. The expression you use can include data from one or more fields in the same table. If you have a number field, for example, that holds quantity information for products purchased and a currency field that holds the price of a product, you can create a calculated field that multiplies the quantity and price fields and stores it with a result type of currency. You could also create a calculated field that concatenates a first name, middle name, and last name fields and stores it with a result type of text for a field called Full Name. Access recalculates the value of the calculated field any time the dependent fields are changed.

You might have noticed that you cannot create an AutoNumber field in web tables. All SharePoint lists include an ID field which increments sequentially like Access AutoNumber data types. Whenever you create a new web table, Access automatically creates an ID field for you because SharePoint lists require it. You cannot delete the ID field from your web tables; however, you can rename the field if you choose.

In client databases, you have a hard limit of 255 fields allowed in a table, but for web databases, you have a hard limit of 220 fields (including the ID field) because SharePoint lists have many hidden fields. Unlike Access tables, SharePoint lists have default limits on the number of columns for each data type. SharePoint lists also have a logical row-size limit of 8,000 KB and a configurable limit of physical rows per list item. This means that you might encounter publishing errors if you have more fields of one data type allowed in a web table, you are over the limit of row size for all fields combined, or a combination of the two. As a baseline, you can publish 48 Date/Time fields, 71 Number or Currency fields, 96 Yes/ No fields, 191 Hyperlink fields, 191 Memo fields, or 219 Text fields in a single web table with default SharePoint list settings.