Forum Discussion
Export and Link Access Query to SharePoint Online
Hello.
I asked support this question but they've gone missing in "Inaction". I've done research on this issue and get the typical lame hits about linking tables. Hopefully A.I. will be properly able to answer our questions. ANYWAY:
I have an Access database with 3 tables linked to a junction table for a many to many relationship.
I want to either:
- link it to SharePoint Online so I can create an updatable PowerApps app AND/OR (meaning when data is updated in Access or SharePoint, the data is synced)
- Export/link the data directly to a PowerApps app. (probably cant happen, it's only 2021 and even though Microsoft 365 is integrated.............it's actually not.
I'm not really seeing any advanced integration other than a simple export of a table to SharePoint or creating the table in Access then exporting it to SharePoint then deleting it in Access and importing the SharePoint list back into Access as a linked table. (?????really??? No that's not convoluted at all).
I take it it's integrated for the sake of putting the buzz word in ads but in reality, the integration is not really that sophisticated.
Moreover, I don't see a connection available to Microsoft Access in Microsoft PowerApps either. Facetiously, thank god there's a connection available to Google Sheets, Marketo or some other non-Microsoft source. Yes sireee.
Obviously I have a query I can "export" to SharePoint and then create a PowerApps app based on that but as is typical with MSFT stuff, anytime you want to take something to the next level, you can't.
When data is updated in Access, the query doesnt get updated in SharePoint. If you save the export process and run that off code or macro welllllll, you don't get to update the list in SharePoint. Now you get to have a new list with a sequenced number in the title. NOT AT ALL USEFUL OR FUNCTIONAL. WHO WOULD NEED THAT?????
Am I asking a data question for something that's far too sophisticated for this Microsoft 365 Business in 2021?? Looks like I'm siloed into Access.
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.
20 Replies
- One more thought. In SharePoint, the mechanism by which Referential Integrity is enforced is the Lookup Field. In Access, of course, we can designate Primary and Foreign Keys and use the relationship window to establish one-to-many relationships and enforce RI on them. While that's not possible in SharePoint per se, the Lookup Field mechanism does, in a practical way, amount to the same thing and does support "Referential Integrity". So, while SP lists are a bit more hassle in some ways, they can work satisfactorily on both sides.
Thanks for the input George!!
So, the junction table is very important in this case to ensure referential integrity. The linked tables in my test db, LinkedTableTestDB and even the tables in the main db are not directly associated with one another. I will attach pictures for you.I'll show you the query setup and results in both databases.
- It seems that you cant create a query with referential integrity using linked tables.
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.
In a nutshell, your first scenario is exactly the way you need to go about this. Convoluted or not, you export your tables to SharePoint Lists, then link the accdb to those lists and, optionally delete the original tables. You COULD also just create a new accdb and link the lists to it, instead, but that probably isn't really worthwhile.
You might get some useful ideas from this AUG presentation. It's aimed at using Access as the Front End, but you could connect to those same SP lists from a PowerApps application and have a more versatile hybrid application. This AUG presentation introduces some of the concepts I personally think Access devs need to consider when adding PowerApps to their portfolio of tools. And I created a set of YouTube videos on managing larger data sets (exceeding the 2000 record delegation limit) in PowerApps).
All in all, I am convinced you are likely to find extending the reach of your robust Access relational database applications to the mobile environment a great strategy.
- Thanks George, reading on ...
