How to update Query on sharepoint (1 Viewer)

biofaku

Member
Local time
Today, 08:53
Joined
May 15, 2020
Messages
66
Hi, I think this is my first post here so greetings everyone!
I was looking for some help with the following, I never used Access with Sharepoint before and I'm trying to implement some ideas for my organization, one of those ideas would be to have access to a query from a link on a QR Code, but before getting there I know I need to learn a few things.
One of those things would be the possibility of uploading ONLY that query to my organization's Online Sharepoint and to keep them up to date with the data we load from the forms that we have on access. It's that possible?


Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:53
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

One way is to "keep" the data in SharePoint and simply use Access to add/edit them.
 

biofaku

Member
Local time
Today, 08:53
Joined
May 15, 2020
Messages
66
Hi DBguy, thanks for the help.

To clarify a little: you are saying that I should upload the tables too?
 

Isaac

Lifelong Learner
Local time
Today, 04:53
Joined
Mar 14, 2017
Messages
8,738
It's possible, but Access doesn't play nearly as nice with Sharepoint as it did in years past. You should be able to update simple SP lists from Access, yes.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:53
Joined
Oct 29, 2018
Messages
21,358
Hi DBguy, thanks for the help.

To clarify a little: you are saying that I should upload the tables too?
Yes, if your specs fit within SharePoint's capabilities. Otherwise, no.
 

biofaku

Member
Local time
Today, 08:53
Joined
May 15, 2020
Messages
66
The list I want to upload it's not that complex, only has a linked field through a foreign key, if that works I'm happy.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:53
Joined
Oct 29, 2018
Messages
21,358
The list I want to upload it's not that complex, only has a linked field through a foreign key, if that works I'm happy.
The specs I'm talking about is the number of records you have in the table. If it's less than 5000, I say go for it.
 

biofaku

Member
Local time
Today, 08:53
Joined
May 15, 2020
Messages
66
No, it should never go that far, I don't think its going to be more than 500, maybe 1000 so I'm going to give it a try.
Thanks for everything!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:53
Joined
Oct 29, 2018
Messages
21,358
No, it should never go that far, I don't think its going to be more than 500, maybe 1000 so I'm going to give it a try.
Thanks for everything!
You're welcome. Let us know how it goes. Good luck!
 

biofaku

Member
Local time
Today, 08:53
Joined
May 15, 2020
Messages
66
Hi again, well to keep you updated with this I did the following: Now I have an Excel Table that Is linked to my access db because the excel has some formulas to make it a dynamic Status tracker.
So now, I want to upload this Excel linked table to Sharepoint Online, that is fine. But I'd like to know if there is a way to also keep it linked. So, for example, if I edit something on Excel, it reflects back on the Sharepoint.
Maybe I'm doing this very complicated without reason but it's just the best way to keep up to date the status on sharepoint without too much user imput that I have found yet.
So, rephrasing a little: It's possible to achieve that flow of information? Excel -> Access -> Sharepoint?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:53
Joined
Oct 29, 2018
Messages
21,358
Hi again, well to keep you updated with this I did the following: Now I have an Excel Table that Is linked to my access db because the excel has some formulas to make it a dynamic Status tracker.
So now, I want to upload this Excel linked table to Sharepoint Online, that is fine. But I'd like to know if there is a way to also keep it linked. So, for example, if I edit something on Excel, it reflects back on the Sharepoint.
Maybe I'm doing this very complicated without reason but it's just the best way to keep up to date the status on sharepoint without too much user imput that I have found yet.
So, rephrasing a little: It's possible to achieve that flow of information? Excel -> Access -> Sharepoint?
Hi. I think, since you want to "share" the information/data, then the best place for it would be in SharePoint. You can then link your Excel and Access files to the SharePoint data, and whoever uses either file will get the latest data.
 

biofaku

Member
Local time
Today, 08:53
Joined
May 15, 2020
Messages
66
Mmm... I don't know if that is what I want. I was looking forward having that list updated so later I could make a reference to each item on a QR code. Well I guess I'm gonna have to think another way to have linked data on sharepoint or something like that. Thanks for your help DBguy!
 

Isaac

Lifelong Learner
Local time
Today, 04:53
Joined
Mar 14, 2017
Messages
8,738
Pardon me for jumping in. Just to throw one more clarification out: Access > Sharepoint update is possible. Excel > Sharepoint [directly, anyway] update is not.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:53
Joined
Oct 29, 2018
Messages
21,358
Mmm... I don't know if that is what I want. I was looking forward having that list updated so later I could make a reference to each item on a QR code. Well I guess I'm gonna have to think another way to have linked data on sharepoint or something like that. Thanks for your help DBguy!
Sorry, unless I completely understand what you want, I don't think I can give you the right advise. Good luck! Please let us know what you end up doing.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:53
Joined
Oct 29, 2018
Messages
21,358
Pardon me for jumping in. Just to throw one more clarification out: Access > Sharepoint update is possible. Excel > Sharepoint [directly, anyway] update is not.
I don't see why not. I do it all the time.
 

Isaac

Lifelong Learner
Local time
Today, 04:53
Joined
Mar 14, 2017
Messages
8,738
Well, I think the devil is in the details, what I meant when I said "directly". Not just linking a table like Access. I'm sure anything can be done with enough code.
But can you elaborate on the method(s) you are referring to?
 

biofaku

Member
Local time
Today, 08:53
Joined
May 15, 2020
Messages
66
Sorry, unless I completely understand what you want, I don't think I can give you the right advise. Good luck! Please let us know what you end up doing.
Maybe I have to start from scratch: I made a DB for my sector, part of the data that I collected there allows me, through an Excel Formula on a Table to see the progress of projects. Because the forms on access are on daily use and I open the Excel sheet everyday, the Status its up to date. That Status Table for every project it's what I want to update to Sharepoint but have it linked, because what I'd like its to have it there updated constantly and also available to link it to powerapps.

I don't know how to come to a Status similar to what I made on excel through VBA on Access, that would take a load off my back, and since I don't know how I gotta look a workaround to update the excel to sharepoint.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:53
Joined
Oct 29, 2018
Messages
21,358
Well, I think the devil is in the details, what I meant when I said "directly". Not just linking a table like Access. I'm sure anything can be done with enough code.
But can you elaborate on the method(s) you are referring to?
Sure. What I do is store the data in SharePoint and link to it in Excel. If that's also what you mean, I guess I'll have to give it another try because it's been a while since the last time I did that.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:53
Joined
Oct 29, 2018
Messages
21,358
Maybe I have to start from scratch: I made a DB for my sector, part of the data that I collected there allows me, through an Excel Formula on a Table to see the progress of projects. Because the forms on access are on daily use and I open the Excel sheet everyday, the Status its up to date. That Status Table for every project it's what I want to update to Sharepoint but have it linked, because what I'd like its to have it there updated constantly and also available to link it to powerapps.

I don't know how to come to a Status similar to what I made on excel through VBA on Access, that would take a load off my back, and since I don't know how I gotta look a workaround to update the excel to sharepoint.
The only way I will fully understand your setup is if I can see it. What sorts of formula are you using in Excel? Maybe we can duplicate it in Access or SharePoint.
 

Isaac

Lifelong Learner
Local time
Today, 04:53
Joined
Mar 14, 2017
Messages
8,738
Sure. What I do is store the data in SharePoint and link to it in Excel. If that's also what you mean, I guess I'll have to give it another try because it's been a while since the last time I did that.
All I meant was the basic built in process of this:
  • Access: Link a sharepoint table, open the table, type in the fields >> Updates back to sharepoint.
  • Excel: Export a sharepoint list to Excel, a data connection is created, type in Excel cells >> Does not update back to sharepoint. That's all.
I am sure there are ways to create mechanisms of updating sharepoint from excel. Which it sounds like you are about to divulge yours - and would be valuable to me - and might help OP too.
 

Users who are viewing this thread

Top Bottom