469,125 Members | 1,607 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

How do I automate the comparison process of two tables?


I am currently trying to automate the process of updating my list on SharePoint from the weekly outputs of a forecast model I use on Excel. I also, need the information on Access.

As of now in Access, I have a table(Table A) that is linked to a list I have in SharePoint and another table (Table B) that is linked to a table I have in my Excel Forecast model.

My goal, is to write out a process in VBA that would automatically compare Table B to Table A and update Table A's values to Table B's, as well as create values in Table A that aren't there.

I was wondering what the best logical approach to this would be?

Thank you very much for your help.
Oct 29 '14 #1
2 928
3,653 Expert Mod 2GB

This can be tricky, because you do not mention how accurate your data is and how much on the Spreadsheet may be different from the SharePoint List.

Here's a general process that I would start with:

1A. If you have a unique index for these records, you can insure accuracy much better than if not. This should be a standard practice anyway. If you don't have indexes, this could become very sticky.

2A. Create a recordset for the Spreadsheet. Then cycle through the records one by one. As you cycle through, use the index of the record in the Spreadsheet as a search criteria for a second recordset based on the SharePoint list. If it finds a record, update all the values in the Spreadsheet to the SharePoint List. If there is no corresponding record, then add a new record to the SharePoint List.

1B. If you do not have indexes, this can get ugly very quickly. Create two recordsets--one for the Spreadsheet, one for the SharePoint list. Make sure that the queries used to create these recordsets are sorted identically (to the maximum number of levels possible, so all records should correspond).

2B. Cycle through both recordsets record by record and hope that the records match. You will have to evaluate each set of records individually. However, you may have problems with new records in the Spreadsheet. Would all the new records be at the end? Will there ever be a case in which the SharePoint list has new records that are not in the Spreadsheet?

Lots of complexities in the second scenario.

Give us more detail and try some things out and see what you come up with. We'll be glad to troubleshoot and work toward a solution.
Oct 30 '14 #2
5,400 Expert Mod 4TB
+ Version of Office?
+ You lack details of the table structures such as primary keys, data types, etc...

Really no VBA needed:
+ Look at the wizards, there is a wizard the will compare two tables for missing entries. It writes a fairly decent query. Set it up to compare your tbl_sharepointlink against tbl_excelfilelink, to return values from tbl_excelfilelink.
+ Once you are sure the proper values are being returned by the select-query, convert it to an append-query, appending to tbl_sharepointlink.
+ sync the database to sharepoint.
+ You can, of course, have a VBA script that runs these steps for you

Oct 30 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Elhanan | last post: by
4 posts views Thread by Cirene | last post: by
1 post views Thread by Snayak | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.