Forum Discussion
Export and Link Access Query to SharePoint Online
I wanted to address this paragraph separately because it seems like it might not reflect actual experience.
"If I export all the tables to SharePoint Lists, I can then go through the hassle of linking to them from Access but now I'm locked because you can't create a query with a local junction table and linked tables. As per usual, EVERYTIME, (and I mean EVERYTIME) I try and create anything, there are ALWAYS these little gotchas that require some ridiculous hack or workaround."
You certainly CAN handle all Linked SharePoint Lists in Access as you would those local tables. However, it's probably going to work better as an all-or-nothing choice. Either move ALL of the local tables into SharePoint list, or NONE of them. It is possible to join local and linked tables in a query in an accdb; I don't know why you would not have been able to do it. There are limitations, such as performance drags and possibly lack of updateability. That said, a select query will, no doubt work. But I understand that there may be some nuance you are referring to that isn't clear in the general statement. If you can be more specific, perhaps we can help address the problem.
On the other hand, if you are intending to create a PowerApps extension, you should just migrate your tables--all of them--to SharePoint lists and be done with it. You can use any or all of those lists in your PowerApps, and any or all of them as Linked tables in your Access relational database application.
This is an area I am really beginning to explore as a long-time Access developer so I would love to learn more about the specific problems you have surfaced.