Export and Link Access Query to SharePoint Online

[Carl_Williams]

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:

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

[#2]

@Carl_Williams 

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.

[#3]

@Carl_Williams 

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.

[#4]

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.

[#5]

Thanks George, reading on ...

[#6]

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

[#7]

It seems that you cant create a query with referential integrity using linked tables.

[#8]

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.

[#9]

@Carl_Williams I think you are actually making this harder than it has to be. 

I see no point to recreating a new list repeatedly. Why? What is that query supposed to be?

In this case, the idea of differentiating lists with some sort of suffix is quite fundamental to all good data management, for example. If that didn't happen, people could accidentally overwrite existing data, losing it with no chance to recover when the same list or table name was used for a new version. That seems to me like it would be a far more serious problem for a lot more people.

It appears that what you want is to repopulate an existing list with fresh data, and you are doing that by creating a new version of the list instead and changing the links and names of the lists. Instead of that approach, why not simply delete records from the already existing list and appending new records from your "Report Query" back to it? Unless you are also changing the structure of the fields as well, that is a lot quicker and less hassle to implement.

Remember, your list, once it exists, can be linked to Access and anything you would do with a local table, like Deleting and Appending records to it, will be the same process. The only real difference is that the data is stored in a different physical location. Is there any reason you couldn't do that?

Finally, I also think we all sometimes expect things to work "the way I want to do it" and complain when they work differently. More often than not, however, it's less stressful to just get on with a solution and save the harsh words for after the job is over.

. 

[#10]

@George Hepworth 

No, not trying to make anything more difficult than need be. I simply want a query in Access to update a SharePoint list.

The query includes a junction table. You cant create a report query using a junction table (many to many) AND linked tables. I've tested every scenario I can think of. As is typical you get 75% there then a wall.

I've been working with MSFT stuff for decades in a federal government setting so am quite familiar with the products and know all to well there is much that has been left to workarounds and hacks. Case and point. So I'm quite correct and justified about my "complaints".

IF I simply take the data from the report query and export it to SharePoint, there's no other way to update the list in SharePoint other than deleting and reexporting. 

The technique you recommend is all well and good IN Access.

[#11]

@Carl_Williams 

At this point, I think I'll offer one more suggestion and bow out. I really don't think bashing MS is going to help you solve the problem.

"The query includes a junction table. You cant create a report query using a junction table (many to many) AND linked tables. I've tested every scenario I can think of."

I would love to see the tables in which you can't create the query you need. But first, what do you consider a "report query"? How is that different from a "select query", for example? It means something to you, but it's not one of the standard terms I've heard over the prior 25 + years of my career. Unless you are simply talking about a query that creates a recordset to be used as the recordsource for a report....

As I noted, you most certainly CAN create a query using local and linked tables, albeit less performant than all local tables or all linked tables. As long as you can identify the Primary and Foreign Key fields in all of the tables involved, and designate those joins in the SQL of the query, it'll work. 

However, if you can provide some specifics to back up this proposition, I'd love to learn something new, as well.

[#12]

@George Hepworth 

Yes, sorry, I was thinking that as I wrote it. Yes the Report query is meaningful to me. I'll resend the images and name them.

• "Report-Main.jpg" image is the select query that I've referred to as a report query.
• It is the result of the image named "ReportQuery-MainDB.jpg"

Those, the queries work fine within the database. Once you link the tables from SharePOint then try the same select query, you get the image "Report-LinkTestDB.jpg"

So I tend to disagree with you in that you can't create what I have locally by linking tables from SharePoint to Access and basing the query off those linked tables and the junction table.

I've tried, several times! I've also included the design of the junction table "JunctionTable.jpg" shown in the relationship build as "tbl_TunePlayerPartJunction".

Hope this helps. 

And, if MSFT would complete their products fore once, many of us, and there are many, wouldnt need to bash. This is nort an isolated incident. As I said from the outset, EVERYTHING one tries to build always has a msft gotch. I've got discussions going back decades to back it up. Discussions where their support don't understand or know how to do and these are/were simple, exepected, everyday functionality one sees in other products in other companies.

[#13]

As I said, bashing and complaining are actually counter-productive. I understand that MS is not perfect. But constantly laying your problems off on them is not going to resolve those problems. To be blunt, I've worked with Access for over 25 years now. I've had difficulties and great successes, so I can't accept that "EVERYTHING one tries to build always has a msft gotch. [sic]" It's simply not true in my experience.

I would much rather have the actual SQL from your queries than screenshots. However, I'll take a look at them.

[#14]

Thanks George. I disagree however. Bashing and complaining equates to (or does in other parts of life) "the squeaky wheel gets the grease".
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 databases, I too have multiple years experience with Access & SharePoint and the fact that we're still talking about this after two other departments dropped the ball (didn't answer me) tells one, what I want is not possible with this framework and app. Sure it works for simple stuff but in my experience, typically, once you attempt to implement something with "real life" functionality there's your wall with workarounds and hacks.
It doesnt seem possible to export and link an udatable query based on a junction table with three related tables. Standard stuff locally but doesn't work in "integration" mode.

[#15]

@Carl_Williams 

I guess we have to agree to disagree. At this point, I've seen three screen shots and no SQL and I'm unable to even hazard a guess as to what your problem is. You've failed to be specific about the actual problem other than that it's about 75% there or something to that effect.

You want to create a new SharePoint list from a query? And have that List be updateable? That's something I'd have to SEE to be able to comment on at all. 

One last attempt, though. If you can provide a copy of the accdb with enough sample data to work with and see what is supposed to happen, I could try to explore it, but in the absence of specifics, it's not possible. I'd also need that SQL from that Query, and no just a screen shot of the tables in the designer. 

[#16]

@George Hepworth 

@George Hepworth 

I'll include here a small db with only pertinent data.

1. The SQL you'll be able to see in the query: "qry_TunePlayerPartJunction".
2. At this point I'm happy with just exporting that query as a list to SharePoint which is simple enough other than not being able to update that list when data is updated in Access. Then I'd be able to create a PowerApps app off that. But if the list is not updatable in SharePoint when data in Access is updated then it's kind of a moot point to export it to SharePoint.
3. What I was referring to previously with the larger issue, I had the three tables:
   1. tbl_Parts
   2. tbl_Player
   3. tbl_Tunes

linked from SharePoint (after having exported them to SP, deleted them in the db then link them). The query "qry_TunePlayerPartJunction" then would no longer display data!

[#17]

It's not letting me include the tiny db. No surprise there. Again.
I've tried 6 times and each time I'm told to fix highlighted errors of which there are none.

[#18]

@George Hepworth 

So 45 minutes later trying several times to simply upload a db for you. Last thing I'm going to try before closing shop and completely moving away from all msft products. I attempted to rename the db SyncTest.jpg so you could rename it to SyncTest.accdb. Did that work?? Nooooo. It's only 616KB 

As a database:

1. The attachment's synctest.accdb content type (image/jpeg) does not match its file extension and has been removed. Then renaming to .jpg
2. The attachment's synctest.jpg content type (image/jpeg) does not match its file extension and has been removed.

This is crazy, I give up with this nonsense. See, no matter what you try to do there's always a Microsoft fly in the ointment. Thanks for trying to help but that proverbial wall has shown up yet again.

[#19]

@Carl_Williams 

This is not a "Microsoft" problem. The problem is that -- as the error message plainly states -- the CONTENT of the file is not an image, i.e. not a jpeg. Changing the extension doesn't masquerade that. See, the website is actually smart enough to analyze the file content and identify it as being other than an image file.

Compress the accdb into a ZIP file. That should work.

[#20]

Yes I get that, however, you indicated you needed a copy of the accdb file and that was what I was trying to do. I figured since you asked you would've already known that and that that particular error may've been related another issue I wasn't aware of.

P.S. I'm trying to get something figured out here and aside from being frustrated with this, I don't need smart remarks from people trying to provide support. It's not you, and you're not Microsoft so you don't need to act as though I've hurt your personal company or your feelings by me being frustrated with the company's product. 

I get it, your a db expert. That's great, I was too at one point in time years ago. I just figured things would've changed by now. Sadly I'm wrong. Same old same old.