Factless Fact Table

here

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

house analogy

build one house that fulfills 100 people's design requests - that's software