Forum Discussion

Carl_Williams's avatar
Carl_Williams
Iron Contributor
Oct 02, 2021

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...
office 365
sharepoint
George_Hepworth's avatar
George_Hepworth
Silver Contributor
Oct 03, 2021
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.
  • Carl_Williams's avatar
    Carl_Williams
    Iron Contributor
    Oct 04, 2021

    George_Hepworth 

    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.

     

     

    • Carl_Williams's avatar
      Carl_Williams
      Iron Contributor
      Oct 04, 2021
      It seems that you cant create a query with referential integrity using linked tables.
      • Carl_Williams's avatar
        Carl_Williams
        Iron Contributor
        Oct 04, 2021

        What I really want, which is both the easiest yet likely most difficult (SURPRISE!!!! MSFT STUFF), is to simply export the query report to SharePoint as a list and create the PowerApps app off that.
        Although, it should be semi-automated. Sure, run some code to export BUT like I said previously, the SharePoint list is given an incremented name (:() which is the most ridiculous and most unwanted process anyone would want in this scenario. So nowwwwww you have to go to SharePoint, delete the list then re-export it from Access then likely go into PowerApps and reconnect the data source. Everything else you do in this !@#$%#@$%@# framework you're asked to verify that you want to perform an action or at least provide a choice to do this or that such as write over!!!!

        Oh yes, we can ask for that in the special "wish list". The list that goes on and on and on and the most functional things and most required, what one would think is normal/expected functionality never get addressed. This is yet another example of oversight. And I dont mean oversight as management/supervisory. Oversight as in mismanagement of "EXPECTED" features in an "integrated" framework.
        Yes sireeee. This what I call the ultimate in integration and data management in 2021-2022. Not.

Resources