How Access converts between Access and SharePoint data types
How Access converts data types when linking to or importing the contents of a SharePoint table.
The following table lists how each Windows SharePoint Services data type is converted when you link to or import a Windows SharePoint Services in Microsoft Office Access 2003.
| SharePoint Data Type | Access Data Type | Default Field Property Settings | Notes | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ID | AutoNumber | Field Size - Integer New Values - Increment Indexed - Yes (No Duplicates) | |||||||||||
| Modified, Created | Date/Time (Read-only) | ||||||||||||
| Modified by, Created by | |||||||||||||
| Single line of text | Text | Field Size - 255 Default Value - Mirrors the Default Value setting in SharePoint. | The Validation Rule property will reflect the values set for the Min and Max properties in SharePoint. | ||||||||||
| Multiple Lines of Text | Memo | Required - Mirrors Required setting in SharePoint. | Access can display up to 64 kb of data in a Text Box. The displayed results are truncated if the fields contains more that 64 kb of text. The Number of lines to display property is ignored. | ||||||||||
| Number | Number | Field Size - Double Decimal Places - Mirrors the Number of decimal places setting in SharePoint. Default Value - Mirrors the Default Value setting in SharePoint. Required - Mirrors Required setting in SharePoint. The following list illustrates how the Format property is set according to the DefaultValue setting in SharePoint.
| The Validation Rule property will reflect the values set for the Min and Max properties in SharePoint. | ||||||||||
| Currency | Currency | Default Value - Mirrors Default Value setting in SharePoint Decimal Places - Mirrors the Number of decimal places setting in SharePoint Currency Format - Mirrors the Currency format setting in SharePoint Required - Mirrors Required setting in SharePoint. | The Validation Rule property will reflect the values set for the Min and Max properties in SharePoint. | ||||||||||
| Date/Time | Date/Time | RequiredMirrors the Required setting in SharePoint The Format property is set to Short Date if the Date and Time Format is set to Date Only in SharePoint. Otherwise, the Format property is blank. The following list illustrates how the DefaultValue property is set according to the DefaultValue setting in SharePoint.
| |||||||||||
| Lookup | Number | Field Size - Long Integer Display Control - Combo Box Required - No Row Source Type - Table/Query Row Source=SELECT ID, <Lookup Field> FROM <Lookup Table> Order By <Lookup Field>; Bound Column - 1 Column Count - 2 Column Heads - No Column Widths - 0 List Rows - 8 List Width - Auto Limit To List=Yes | |||||||||||
| Choice (single) | Text | Field Size - 255 Default Value - Mirrors Default Value setting in SharePoint Display Control - Combo Box Row Source Type - Item List Row Source="<choice 1>";"<choice 2>";..."<choice N>" | |||||||||||
| Choice (multiple) | Memo (Read-only in a linked table) | Display Control - Text Box Default Value - Mirrors Default Value setting in SharePoint Required - Mirrors Default Value setting in SharePoint | The Choices, Display choices using, and Allow Fill-in choices settings are ignored. | ||||||||||
| Grid Choice | Memo (Read-only in a linked table) | Display Control - Text Box Required - Mirrors Default Value setting in SharePoint | The Choices, Start number, and End Number settings are ignored. | ||||||||||
| Yes/No | Yes/No | Display Control - Check Box Default Value - Mirrors Default Value setting in SharePoint | |||||||||||
| Hyperlink | Hyperlink | Required - Mirrors Default Value setting in SharePoint | The Format URL as setting is ignored. | ||||||||||
| Attachment/Picture | Hyperlink (Read-only) | Display Control - Text Box Required - Mirrors Default Value setting in SharePoint | |||||||||||
| Computed | Can be one of the following data types: Text Number Currency Date/Time Yes/No The field is Read-only | ||||||||||||
| Rich Text | Memo | Display Control - Text Box Required - Mirrors Default Value setting in SharePoint | Access can display up to 64 kb of data in a Text Box. The displayed results are truncated if the fields contains more that 64 kb of text. The Number of lines to display property is ignored. |
How Access data types are converted when exporting a table to SharePoint.
The following table lists how Access data types are converted when you export an Access table to SharePoint.
| Access Data Type | SharePoint Data Type | Default Field Property Settings | Notes | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| Text | Single line of text | Column Name
- Mirrors the Field Name setting in Access Description - Mirrors the Description setting in Access Required - Mirrors the Required setting in Access Maximum number of characters - Mirrors the Field Size setting in Access Default Value - Mirrors the Default Value setting in Access if it is not an expression, blank otherwise Add to Default View - Yes | |||||||||
| Memo | Multiple lines of text | Column Name
- Mirrors the Field Name setting in Access Description - Mirrors the Description setting in Access Required - Mirrors the Required setting in Access Number of Lines to Display - 5 Add to Default View - Yes | The text will be truncated if it is longer than 2^32 characters in length. | ||||||||
| Number | Number | Column Name
- Mirrors the Field Name setting in Access Description - Mirrors the Description setting in Access Required - Mirrors the Required setting in Access Min - Blank Max - Blank The following list illustrates how the Number of decimal places property is set according to the Decimal Places setting in Access.
Default Value - Mirrors the Default Value setting in Access if it is not an expression, blank otherwise Add to Default View - Yes Show as percentage - Yes if the Format property is set to Percentage. | |||||||||
| Date/Time | Date/Time | Column Name
- Mirrors the Field Name setting in Access Description - Mirrors the Description setting in Access Required - Mirrors the Required setting in Access Date and time format - Set to Date Only if the Format Property is set to Short Date. Otherwise, set to Date & Time. Calendar Type - Hijri if the Use Hijri option is checked, otherwise Gregorian. The following list illustrates how the Default Value property is set according to the Default Value setting in Access.
Add to Default View - Yes | |||||||||
| Currency | Currency | Column Name
- Mirrors the Field Name setting in Access Description - Mirrors the Description setting in Access Required - Mirrors the Required setting in Access Min - Blank Max - Blank The following list illustrates how the Number of decimal places property is set according to the Decimal Places setting in Access.
Default Value - Mirrors the Default Value setting in Access if it is not an expression, blank otherwise Add to Default View - Yes Currency Type - Mirrors the Currency Format setting in Access | |||||||||
| AutoNumber | Number | Column Name
- Mirrors the Field Name setting in Access Description - Mirrors the Description setting in Access Required - Mirrors the Required setting in Access Min - Blank Max - Blank Number of decimal places - Automatic Add to Default View - Yes | |||||||||
| AutoNumber where the Field Size property is set to Replication ID | Single line of text | Column Name
- Mirrors the Field Name setting in Access Description - Mirrors the Description setting in Access Required - Mirrors the Required setting in Access Maximum number of characters - 38 Default Value - Blank Add to Default View - Yes | |||||||||
| Yes/No | Yes/No | Column Name
- Mirrors the Field Name setting in Access Description - Mirrors the Description setting in Access Default Value - Mirrors the Default Value setting in Access if it is not an expression, blank otherwise Add to Default View - Yes | |||||||||
| OLE Object | The field is not exported | ||||||||||
| Hyperlink | Hyperlink | Column Name
- Mirrors the Field Name setting in Access Description - Mirrors the Description setting in Access Required - Mirrors the Required setting in Access Format URL as - Hyperlink Add to Default View - Yes |