Compare rows in a spreadsheet with records in a database

Question

I have an Excel spreadsheet provided as a report, when really its more of a database, with the row number acting as primary key. I need to compare some columns in this set with a different set of records from an Access 2007 database. To make matters more complicated, this needs to be done automatically, without user input, on a regular schedule.

What is the best way to get the data out of the spreadsheet, and what is the best way to run the comparison. As for getting data out of the spreadsheet, ACEDAO seems the best option, since this is Excel 2007, but MSDN has no information I could find on how to query Excel using DAO/ACEDAO, only to export to Excel using DAO/ACEDAO. As for running the comparison, beyond comparing each column of each entry, I can't figure out a better way to do it.

Answer 1

If the Excel worksheets are structured as lists then ADO might be the way to go - more info here

---

edit: there are several different possible approaches here

• use ADO in Excel to read from the Access database and use VBA to compare the returned values to the Excel worksheet. Probably only going to be useful for simple comparisons on relatively small recordsets

• as described in Robert Harvey's answer, make the Excel worksheet a linked table in the Access database and query with SQL using Access itself

• using ADO and ADOX make the Excel worksheet a linked table in the Access database and query with SQL using ADO in Excel

I didn't realise that DAO was back in use with Access 2007. As I use Excel to connect to databases, DAO has been deprecated for years (with ADO being the preferred connection method). I now know better

Answer 2

Using ACE you can query the workbook directly e.g.

SELECT S1.seq AS seq_ACE, 
       S2.seq AS seq_Excel
  FROM Sequence AS S1
       LEFT OUTER JOIN [Excel 12.0;Database=C:\Test.xlsx;].Sequence AS S2
          ON S1.seq = S2.seq;

Answer 3

You can create an ODBC data source for the Excel data and another for the database data and then use an ODBC data comparison tool such as CompareData to compare the data

Answer 4

I know I am gonna get slammed for this because it isn't very "programmer-y", but if this is a one-off and the resulting Access table is just a temporary working copy of the data for a few queries you can just copy/paste the data directly into a table in datasheet view.

It is a quick, dirty, no-frills way to do it, but it works and requires very little extra work.

Note: If the table is REALLY large or is actively in use by others, you might not want to do this. It can be slow and introduce a lot of locking.