Q-Built Solutions Makes Quick, Quality-Built Software For Your Business Needs!
 

Q-Built Solutions Web Statistics
.

 
Statistics As Of:
26 Mar. '07

Number of unique visitors since 20 March '04:
204,793

Number of Web pages served since 20 March '04:
429,683

Our Most Popular Web Pages:

 1.  How-To Tips
 2.  Technical Articles
 3.  Gem Tips
 4.  VBA
 5.  Free Stuff
 6.  Links
 7.  Free Downloads
 8.  Our Custom Microsoft Access Products
 9. Forms
10.  FAQ's
 

Sharepoint Services and Access

By D. C. Conlin

Working with Sharepoint Services can make collaborating with distant colleagues easy.

You've set up Sharepoint Services on your network, and now you and your colleagues can collaborate together on projects at the same time, even though they're halfway across the country.  You've exported your Access database tables to Sharepoint only to find out the OLE columns didn't export and the decimal places in numbers are truncated. How did this happen? Sharepoint Services tables aren't exactly the same as Jet or ACE tables in Access, so some conversion needs to take place. Take a look at the following tables to see what the converted data types and property settings will be when you export into, import from, or link to tables in Sharepoint Services.

How Access data types are converted when exporting a table to SharePoint.

The following table lists how Access (Jet or ACE) data types are converted when exported into a SharePoint table.

Access Data Type	SharePoint Data Type	Default Field Property Settings	Comments
Text	Single line of text	Column Name - Same as the Field Name setting in Access. Description - Same as the Description setting in Access. Required - Same as the Required setting in Access. Maximum number of characters - Same as the Field Size setting in Access. Default Value - Same as the Default Value setting in Access, if it is not an expression. Otherwise, it's blank. Add to Default View - Yes	.
Memo	Multiple lines of text	Column Name - Same as the Field Name setting in Access. Description - Same as the Description setting in Access. Required - Same as the Required setting in Access. Number of Lines to Display - 5 Add to Default View - Yes	The text will be truncated if the number of characters exceeds 232.
Number	Number	Column Name - Same as the Field Name setting in Access. Description - Same as the Description setting in Access. Required - Same as the Required setting in Access. Min - Blank Max - Blank The following list shows 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 - Same as the Default Value setting in Access if it is not an expression.  It's blank otherwise. Add to Default View - Yes Show as percentage - Yes, if the Format property is set to Percentage.	Sharepoint Services uses a maximum setting of five decimal places.
Date/Time	Date/Time	Column Name - Same as the Field Name setting in Access. Description - Same as the Description setting in Access. Required - Same as the Required setting in Access. Date and time format - Set to "Date Only" if the Format Property is set to Short Date. Otherwise, it's set to Date & Time. Calendar Type - Hijri if the Use Hijri option is checked. Otherwise, it's set to Gregorian. The following list shows 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 - Same as the Field Name setting in Access Description - Same as the Description setting in Access Required - Same as 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 - Same as the Default Value setting in Access if it is not an expression.  It's blank otherwise. Add to Default View - Yes Currency Type - Same as the Format setting in Access.	.
AutoNumber	Number	Column Name - Same as the Field Name setting in Access. Description - Same as the Description setting in Access. Required - Same as 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 - Same as the Field Name setting in Access. Description - Same as the Description setting in Access. Required - Same as the Required setting in Access. Maximum number of characters - 38 Default Value - Blank Add to Default View - Yes	.
Yes/No	Yes/No	Column Name - Same as the Field Name setting in Access. Description - Same as the Description setting in Access. Default Value - Same as the Default Value setting in Access if it is not an expression. It's blank otherwise. Add to Default View - Yes	.
OLE Object	N/A	.	The column is not exported to Sharepoint.
Hyperlink	Hyperlink	Column Name - Same as the Field Name setting in Access. Description - Same as the Description setting in Access. Required - Same as the Required setting in Access. Format URL as - Hyperlink Add to Default View - Yes	.

How Access converts data types when linking to or importing the contents of a SharePoint table.

Each Windows SharePoint Services data type is converted to a specific Jet or ACE data type when linked to or imported into Microsoft Office Access 2003 or 2007.  The data types convert as follows:

SharePoint Data Type	Access Data Type	Default Field Property Settings	Comments
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 - Same as 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 - Same as Required setting in SharePoint.	Access can display up to 64 KB of data in a Text Box. The displayed results are truncated if the column contains more that 64 KB of text. The Number of lines to display property is ignored.
Number	Number	Field Size - Double Decimal Places - Same as the number of decimal places setting in SharePoint. Default Value - Same as the Default Value setting in SharePoint. Required - Same as Required setting in SharePoint. The following list shows how the Format property is set according to the Default Value 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 - Same as Default Value setting in SharePoint Decimal Places - Same as the number of decimal places setting in SharePoint Format - Same as the Currency format setting in SharePoint Required - Same as 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	Required - Same as 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 shows how the Default Value property is set according to the Default Value setting in SharePoint. SharePoint Setting Access Setting (None) blank Today's Date =Date() Static date Same as 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 - Same as 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 - Same as Default Value setting in SharePoint Required - Same as 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 - Same as Required setting in SharePoint.	The Choices, Start number, and End Number settings are ignored.
Yes/No	Yes/No	Display Control - Check Box Default Value - Same as Default Value setting in SharePoint.	.
Hyperlink	Hyperlink	Required - Same as Required setting in SharePoint.	The "Format URL as" setting is ignored.
Attachment/Picture	Hyperlink (Read-only)	Display Control - Text Box Required - Same as Required setting in SharePoint.	.
Computed	Data type varies.  The field is read-only.	.	Data type can be one of the following: Text Number Currency Date/Time Yes/No.
Rich Text	Memo	Display Control - Text Box Required - Same as 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.

You can read about your favorite expert contributors.

Copyright © 2007 Q-Built Solutions.  All rights reserved.

 Top

Visitors since 18 Feb. '07: