Dec 30 2021 06:57 AM
Dec 30 2021 10:17 AMSolution
Given that this type of field isn't supported in Access, and can't be reliably used, my suggestion would be to avoid it entirely for the purposes of your Access relational database application.
This is part of a larger pattern, IMO, in which SharePoint lists are a world unto themselves. We can't reasonably count on them outside of that environment. If you stick to SharePoint, of course, they're fair game.
I actually like the idea of SharePoint lists as a backend for some specialized Access solutions because of the fact that you can use them off-line as well as while connected, and because they are reachable via an internet connection from multiple locations.
That said, and repeating myself, because of their unique internal construction, I would avoid any but the basic datatypes like numbers, text and dates.
Your experience may vary, but that's how I see it.
Dec 30 2021 10:32 AM
Dec 30 2021 10:42 AM
Within OFFICE itself (Word, Excel, Outlook, Powerpoint and Access) integration is pretty good. They're all dependent on VBA, for example. Otherwise, it's not so much. Another aspect is, no doubt, the fact that SharePoint Lists may be built on SQL Server, but they're heavily customized for the SP environment. I know Access/SQL Server devs who go so far as to reject SP lists as anything to do with relational design.
You mention Lookups. Another Soap Box™ of mine. For reason outside the scope of this forum, I have been trying to learn enough about PowerApps to work with them to create mobile hybrid apps (run in Access, with extended functionality on a smart device). Long story short, I used SP instead of SQL Server or SQL Azure for the back end. I chronicle one of my adventures in a YouTube video series about figuring out how Lookup fields really work in PowerApps. I'd love to be proven wrong, but my current opinion is that I'm going to bite the bullet and pay extra for SQL Azure in future PowerApps applications.
Dec 30 2021 11:24 AM
Dec 30 2021 01:43 PM