Calculated columns in sharepoint opened in access

If you have a calculated column thus

=IF([Status] ="Completed", TODAY(),"")

and the column is of type date

this is no issue for share point , the column will be empty if Status <> Completed

BUT access will read this as #error (but I have seen sometimes it reading it as 12/30/1899 (min date) and I do not know when or how this happens)

if you try iserror function it will not return yes, rather #error

any other function (len etc) will do that also - return #error

if you explicitly calculate like this 

=IF([Status] ="Completed", TODAY(),0)


access will work fine

but then again share point will have 12/30/1899 

access list boxes - stop selection

use Locked property


if you use the enabled property then the scrollbars wont work

Access and SharePoint Interactions

GetLookupDisplayValues

When you create with VBA an attached SharePoint list in access, you are given the option of getting lookup display values (if you attach manually the value will be False).

expression.TransferSharePointList(TransferType, SiteAddress, ListID, ViewID, TableName, GetLookupDisplayValues)

GetLookupDisplayValues

Optional

Variant

Specifies whether to transfer display values for Lookup fields instead of the ID used to perform the lookup.

This setting will determine if SharePoint Created by and Modified by columns are lookups which will use UserInfo lookup table or plain text values (e.g. herby ).

Strangely enough, this will not help for user defined columns (such as escalated to)

Likewise, if querying “modified by”, the query window is acting like a lookup by being in drop down

And raises the following error

Number of Rows Limit

This will happen when GetLookupDisplayValues = true

If this is the case - set  cache settings  

Number of lookups limit

SharePoint has a limit of 12 lookup columns.

This bubbles up to Access

Non-Solutions to deal with this in Access

·        Query less columns through a query object

·        Set GetLookupDisplayValues to true

·        changing cache options

Solutions to deal with this in Access

·        Create a view

For example, in the “herby” list I have created a view called herby1”.

With this you gain not including Microsoft lookup columns like “Taxonomy Catch All Column”

When you connect to SharePoint, use view id (this image is from my test platform)

 

So you can now connect to the list

UDF in access-sql

must have a type "as String etc.". otherwise you will get internal database engine error

Lookup columns in sharepoint to access

if allow multiple values the ado.field type will be 109(complex text) if not then it's a regular 10 (text)


many bugs

"in" in access

in ssql server this

select * from

(select 1 as p union select 2 as p union select 3 as p ) as main

where p in (1,1)

will give you  1 row

in access - you will get 2 rows

sharepoint lookup threshold hit in access

set no caching in current database settings

ACCESS vba not running

make sure content enabled