Forum Discussion
Export and Link Access Query to SharePoint Online
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.
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.
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.
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.
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:
- The attachment's synctest.accdb content type (image/jpeg) does not match its file extension and has been removed. Then renaming to .jpg
- 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.
- 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. I'll include here a small db with only pertinent data.
- The SQL you'll be able to see in the query: "qry_TunePlayerPartJunction".
- 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.
- What I was referring to previously with the larger issue, I had the three tables:
- tbl_Parts
- tbl_Player
- 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!
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.
- 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. - 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.