Automatically or easily updating my database

Question

I have available to me a Report that is generated in Microsoft SharePoint, and it holds the quantities for certain items. The reports can be exported as excel documents, but if it is possible i would like to avoid that.

In my Access database I have all the same items but with additional data concerning special requests and item identification in the item's respective documentation folders.

I am looking for a way to have the select few columns that represent the quantities and some other factors, to be automatically updated in my database.

How can I go about this? Is there a specific terminology for what I am attempting to do, I am unable to find it on Google?

score 0 · Accepted Answer · answered May 09 '13 at 22:20

0

So to clarify ... you have item data exported from SharePoint and item data in Access and ideally you'd like to merge both and store the results in Access.

Or maybe another way of putting it, you would like to compliment the data in Access with the data from SharePoint.

If the database that powered the SharePoint report ran in Access as well, the word you are looking for is replication. You want to automatically replicate the data from one server/database to another.

Unfortunately I don't know of any software that replicates data to Access.

Your best bet would be to write a program that scheduled the running of the SharePoint report and then imported that data into Access.

answered May 09 '13 at 22:20

ryan1234

7,237
6
25
36

Yes that is pretty much what I mean. I am currently writing a little program to match up the data sets and then update the associated row. – JahKnows May 10 '13 at 12:28
The user CAN choose replication with SharePoint. In fact this is the DEFAULT setup when using linked tables to SP. Then a query could be used that joins in the additional table with the quanity columns etc. So you are 100% wrong that replication is not an option when in fact it is the default. Replication would need caution since any changes to the local access table would go back up to SharePoint and that may not be desired or even allowed. In this case I would thus simply import the SharePoint tables local and again use a join based on a PK to the tables with quanity etc. that is local. – Albert D. Kallal May 11 '13 at 19:28

score 0 · Answer 2 · answered May 10 '13 at 12:39

I'm happy to give you the terminology of what to Google for. Just don't make me use SharePoint and Access. :) If you have the same items in a report in SharePoint and in Access hopefully there is a field that uniquely identifies each item and is used in each table (a unique key). If these items (typically we would say 'records' or 'tuples' in database circles) are inventory SKUs or product numbers would be examples of potential unique keys. If you re taking the information in two tables and merging them together using a unique key we call it a 'Natural Join'. I know Access and SharePoint both support SQL and using SQL this would be done using a SELECT statement.

I would try googling: Natural Join tables in SharePoint and Accesss Or: SQL SELECT between SharePoint and Access

Hope this helps.

score 0 · Answer 3 · answered May 11 '13 at 19:33

If you choose linked tables to SharePoint (as opposed to importing them local), then you will always have a live copy of the data. In fact this is replicated model in Access 2010. Then a query could be used that joins in the additional table columns with quanity etc. Replication would need caution since any changes to the local access table would go back up to SharePoint and that may not be desired or even allowed.

In this case I would thus simply import the SharePoint tables local and again use a join based on a PK to the tables with quanity etc. that is local. Note that the local copy + cache runs very fast in 2010, and prior to Access 2010 + SharePoint 2010 the speed of such a setup is not so good compared to Access 2010.

If you are using an older version of Access + SharePoint then I would suggest you continue your approach of important the SharePoint tables (as opposed to being linked to the live tables on SharePoint). You then again simply use a query that joins in the additional columns you wish to display in your reports.

Such a results query would not only be of use for reports, but you could export that query into Excel or word.

Best regards.

Automatically or easily updating my database

3 Answers3