Update Query (1 Viewer)

[sajarac]

Hello community.

I have a silly question and I don't know if this is possible?

I have a share point list, that list is basically a copy of an excel file. I would like to make in a daily basis changes in my excel file and at the end of the day run a query that takes my excel file and copy the records/rows changed or added I don't want to copy the entire table just the records mentioned.

Can this be done?

Thanks in advance for point me in the right direction.

 

[Isaac]

You might be able to link to the sharepoint list in Access, so it becomes a Linked Table object. Also link to the Excel file, so it becomes a Linked Table object. Create an Update query in Access that joins the two tables on a common key column. Update the Sharepoint column(s) with the column(s) from the Excel table.

Probably there is no drawback to updating "all" rows, or, you can come up with some other way of identifying which rows you want to update.

Updating Sharepoint from within Access is fraught with problems and hurdles. If you have People fields in your Sharepoint list, you may need to create a Sharepoint VIEW that does NOT contain People fields, then link to that View in Access, in order to make the Access linked table object reliably updateable.

 

[sajarac]

Thank you very much for your prompt response.

could you please explain a little bit this portion?

Create an Update query in Access that joins the two tables on a common key column. Update the Sharepoint column(s) with the column(s) from the Excel table.

Sorry, I am not good in Access

 

[Isaac]

Once you go in the Create > Query Design on the ribbon, you'll notice that Update is one of the query types you can choose. You may want to stop at this point and do a little reading up on the general process of creating queries in Access

 

[Pat Hartman]

This sounds pretty scary. Which "database" is the system of record? i.e. which one is the MASTER? Is Excel the master or is SharePoint the master? It is really poor practice to update data in two different systems and try to figure out which record is correct and current.

 

[sajarac]

Hey, thanks for your prompt response, in this case excel is the master. Share point will be a mirror file

 

[arnelgp]

Create an Update query in Access that joins the two tables on a common key column.

can you update an excel linked table?

 

[Isaac]

can you update an excel linked table?

You wouldn't update the excel linked table. You would update the Sharepoint linked table by joining the two tables and using the excel field as the "update to" criteria.

 

[Pat Hartman]

If the Excel spreadsheet is the "master" database (pretty scary concept when relational databases are so accessible). Just delete the data from SharePoint and replace it. This type of process is best done in the wee hours of the morning when no one is working.