question

CarlWilliams-4816 avatar image
0 Votes"
CarlWilliams-4816 asked CarlWilliams-4816 answered

Export and Link Access Query to SharePoint Online

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.





office-sharepoint-onlineoffice-access-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@CarlWilliams-4816,

Per my knowledge, there is no direct connection between Access and PowerApps, we could only link Access table to a SharePoint list, then use Microsoft PowerApps to customize a SharePoint list form, since I am not familar with junction table in Microsoft Access, we recommend that you ask your issue in the dedicated access forum:

https://techcommunity.microsoft.com/t5/access/ct-p/Access_Cat

0 Votes 0 ·
CarlWilliams-4816 avatar image
0 Votes"
CarlWilliams-4816 answered

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".

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CData-3904 avatar image
0 Votes"
CData-3904 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarlWilliams-4816 avatar image
0 Votes"
CarlWilliams-4816 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CData-3904 avatar image
0 Votes"
CData-3904 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarlWilliams-4816 avatar image
0 Votes"
CarlWilliams-4816 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.