How Access converts between Access and SharePoint data types

ShowHow 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.

SharePoint Setting Access Setting
Show as percentage Percentage
Decimal Places (when set to a number) 0
Decimal Places (when set to Automatic) blank
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
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.

SharePoint Setting Access Setting
(None) blank
Today's Date =Date()
Static date Mirrors Default Value setting in SharePoint
Computed value blank
 
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 Required 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 Required 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 Required setting in SharePoint The Format URL as setting is ignored.
Attachment/Picture Hyperlink (Read-only) Display Control - Text Box
Required - Mirrors Required 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 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.

ShowHow 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.

Access Setting SharePoint Setting
Auto Automatic
0-5 0-5
6-15 5


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.

Access Setting SharePoint Setting
=Date() Today's Date
Field set to a specific date Field set to a specific date


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.

Access Setting SharePoint Setting
Auto Automatic
0-5 0-5
6-15 5


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 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 		 
 
 
Applies to:
Access 2003