Updating a Share Point Table (1 Viewer)

Hi there, I hope someone can point me in the right direction:

I have a query in MS Access, that query was exported to a share point list, once the query was in share point list I've imported and linked the same table in my MS Access. basically that linked table feeds my share point list. my problem is every time that I update my MSAccess database I have to manually copy and paste into my linked table to updated Share point data.

I've tried with update query, but this option basically was duplicating the same records every single time.

My query table is = "MainQuery" my linked sharepoint table is = "Mainquery1"

Any way to create a macro, query etc, to update "MainQuery1 based on my MainQuery table without duplicate records?

thanks in advance for any response

Regards

I had something similar (with a pass through query) the other day.
It turns out that access doesn't like the query to be on the server.

From my limited understanding, you should be able to create (and use) the query from the access front-end.

This may help
https://www.youtube.com/watch?v=vIm5d8j67Zo

Hello there, and thank you very much for your prompt reply, however I think that the video is not my case.

In my case the main table is located in my Access Database, with that table I've created a query, that query was exported into a Share
Point list, after I imported and linked the same table into my access database.

so the main point of data is MS Access, Share point I am only using to share data with users using power apps.

I've tried several choices but none of then works.


Regards

Hi Sajarac.
If I understand you correctly, then you have two identical tables. One in the Access database and one in Sharepoint. . . .and you want to synchronise them.
Provided that your users are not changing the data on sharepoint, then you could use a query (or two) to update Sharepoint.

If you are not adding records on the access end, then you could run a simple update query to the sharepoint table.

If you are adding records on the access side, then (I believe) that the easiest way is a two step process.
One query to DELETE all the data on the SP side and then another query to INSERT the data from access to SP.

Hello there,thanks for your reply.

Yes I have two identical tables and my users don't change data in share point, they only can see the data.

So the main database and I am responsible for the daily updates is located in MS Access.

I tried to export the table again to my Sp list but I get another table or a copy of the table i.e table ---> table1

2. after this I've delete the SP list and export the tables again. but I had to recreate the connection again for my powerapps.

3. I did an update query but everytime that I run the query let's says for instance the main query has 500 records, after the update I get 1000 record, then 1500 records and so on.

So at this point I have no clue on how to get my SP list updated.

Thanks

Hi Sajara

sajarac said:

2. after this I've delete the SP list and export the tables again. but I had to recreate the connection again for my powerapps.

Click to expand...

As I understand it, if you run a DELETE query on the SP table, this should delete all the records, but keep the table structure in tact so that you should not need to rejoin the table.

sajarac said:

3. I did an update query but everytime that I run the query let's says for instance the main query has 500 records, after the update I get 1000 record, then 1500 records and so on.

Click to expand...

The above DELETE query will empty all your records before you do the update.

Hope that helps.

Good Morning and thank you very much for your reply, I never used a delete query before but I will start investigating.

Thanks again!!!

Regards