How do I automate the comparison process of two tables?

[zzzz243]

Hello,

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.

[twinnyfo]

zzzz243,

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.

[zmbd]

+ 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

(^_^)