How Access converts between Access and SharePoint data types - Access

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

How Access converts between Access and SharePoint data types

Applies to: Microsoft Office Access 2003

Show 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

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

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

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

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

Display Control - Check Box
Default Value - Mirrors Default Value setting in SharePoint

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.

Show 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^³² characters in length.

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

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

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

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

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