How to update Query on sharepoint

[theDBguy]

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.

Okay, I'll have a play with it in a while and let you know what I find out (unless somebody else beats me to it). Cheers!

 

[theDBguy]

Okay, I'll have a play with it in a while and let you know what I find out (unless somebody else beats me to it). Cheers!

Okay, forget what I said for now. I did a quick test and wasn't able to update the SharePoint data from Excel without using code. I'll keep playing and post any updates, if I discover anything else. Cheers!

 

[biofaku]

Oh, it would be great to update sharepoint from Excel, that would simplify a lot my work.

And programming on vba could be possible, it should check a lot of conditions on each project then fill a field on the table but that also could work perfectly. (To be clear, it has 22 possible stats for the projects).

By the way, I was trying to use the update query feature to see if it was possible to update the linked table, but ended up with clearing all the data I had on that table lol

 

[theDBguy]

Oh, it would be great to update sharepoint from Excel, that would simplify a lot my work.

And programming on vba could be possible, it should check a lot of conditions on each project then fill a field on the table but that also could work perfectly. (To be clear, it has 22 possible stats for the projects).

By the way, I was trying to use the update query feature to see if it was possible to update the linked table, but ended up with clearing all the data I had on that table lol

Hi. No need to give up yet. As was already mentioned, Access can update SharePoint. So, if it was me, I would store the data in SharePoint, update it with Access. And if Excel has to calculate new values, grab it from Excel using Access, and then update SharePoint.

 

[biofaku]

Hi. No need to give up yet. As was already mentioned, Access can update SharePoint. So, if it was me, I would store the data in SharePoint, update it with Access. And if Excel has to calculate new values, grab it from Excel using Access, and then update SharePoint.

Yes, I could do it manually, but the idea was to dedicate the least amount of work possible. Any suggestion to achieve that?

 

[theDBguy]

Yes, I could do it manually, but the idea was to dedicate the least amount of work possible. Any suggestion to achieve that?

I'm not sure what you mean by manually. Where is the data coming from and how is it normally updated? If you have the data in Access now, all I am saying is move it to SharePoint, and everything should stay the same.

 

[biofaku]

Ok, got the update query to work perfectly, so now I got to do only two things to have my sharepoint list always up to date:
1- Open the excel file to recalculate any possible change
2- Execute the Update query and click the 1203123 boxes saying "Yes I want to update".

Any suggestion to improve this?

 

[theDBguy]

Ok, got the update query to work perfectly, so now I got to do only two things to have my sharepoint list always up to date:
1- Open the excel file to recalculate any possible change
2- Execute the Update query and click the 1203123 boxes saying "Yes I want to update".

Any suggestion to improve this?

Perhaps, you could skip step 1, if you can automate Excel from Access. I can't see your setup to be able to comment on what to do with step 2. Cheers!

 

[biofaku]

Perhaps, you could skip step 1, if you can automate Excel from Access. I can't see your setup to be able to comment on what to do with step 2. Cheers!

Gonna look up the solution for one then, hope to find something that I can apply.
For two the only problem is the promt box that appears to make sure that you want to update the table, I was thinking to add a button to a form that closes the DB and at the same time runs the query. The problem still is the boxes asking for validation, if I could skip that somehow would be great.

 

[theDBguy]

Gonna look up the solution for one then, hope to find something that I can apply.
For two the only problem is the promt box that appears to make sure that you want to update the table, I was thinking to add a button to a form that closes the DB and at the same time runs the query. The problem still is the boxes asking for validation, if I could skip that somehow would be great.

Look into CurrentDb.Execute method.

 

[biofaku]

Thanks DBguy, you have been really helpful! I am going to try that, if I come to a solution going to come back later to tell you!

 

[theDBguy]

Thanks DBguy, you have been really helpful! I am going to try that, if I come to a solution going to come back later to tell you!

Thanks! Good luck!