Export and Link Access Query to SharePoint Online

Carl Williams 236 Reputation points
2021-10-01T00:13:46.86+00:00

Hello.

I asked support this question but..........................ya, I won't go there. Did research annnnnd Zippo.

Have an Access database with 3 tables linked to a junction table for a many to many relationship.
I want to either:

  1. 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)
  2. Export/link the data directly to a PowerApps app.

So, Microsoft 365 is apparently very "integrated". However, I'm not really seeing that 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.

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.

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,737 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
825 questions
Accepted answer
  1. Carl Williams 236 Reputation points
    2021-10-02T19:39:51.057+00:00

    Thanks Julie. I actually have a case number with support, actual "Support". Shhhhhh, there's no response from them. All they seem to be able to do in support is rephrase your question then.........................................gone!!

    I will endeavor to try one more place then, if Microsoft is no help, well then I guess I wont be anu help in terms of ongoing subscriptions. I'll be like their support team: "Gone", "Missing in Inaction".

    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. C Data 6 Reputation points
    2021-10-05T21:20:24.44+00:00

    am in 2019/365 ... if you're in a different edition the user interface may be different.

    in tab: Database Tools one has the option to 'move data' to SharePoint ... this is the wizard for initially splitting a database from all local tables to being a front end and a back end.

    or in tab: External Data one has the option to link a 'New Data Source' using 'From Online Services'

    definitely Access can link to a sharepoint list in the cloud... it can have issues in tables/lists as the record count gets high in terms of lags

    there are some videos online tutorials on setting this up

    0 comments
  2. Carl Williams 236 Reputation points
    2021-10-06T15:12:02.073+00:00

    Thanks CData, yes I'm familiar with that although I didnt want to split it just yet. I simply wanted a query exported to SharePoint that was linked (updatable both ways) so I could create a PowerApps app.
    I know how to link and export and split but MS doesnt seem to know how to export and link an yodatable query based on a junbction table with three related tables. Standard stuff locally but doenst work in "integration" mode.

    0 comments
  3. C Data 6 Reputation points
    2021-10-06T15:30:14.187+00:00

    well not clear what you seek when saying: export and link an update-able query based on a junction table with three related tables

    that's multiple things

    if the issue is that a query is not update-able - that's a stand alone issue. it will, or will not be, dependent on the relationship of the tables involved and not related to sharepoint linking.

    it is a very common dilemma - an update/append query can be very picky in requiring joined tables to involve only unique primary key fields. a common work-around when the query itself won't permit append/update is instead to write the data to a temp table. then use the temp table to append/update the destination tables.

    0 comments
  4. Carl Williams 236 Reputation points
    2021-10-06T19:58:01.93+00:00

    Tx CData. Yes it's a simple select query, things that've been done back in the pioneer days. YET, the integration I'm looking for is not to be had with this..................set of ...................products.
    Thanks for trying but I'm done with this company. Like I said every time you try to implement something it works half-way than there's a wall and all it does is raise your blood pressure.

    0 comments