2

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.

Andrew Scagnelli
  • 1,584
  • 4
  • 18
  • 26
  • Is this is a one-time process? If so you could import the Excel sheet into the Access DB using Access and create SQL queries using the Access QBE facility to produce the desired report. – Bob Mc Jun 30 '09 at 18:23
  • This isn't a one-time process; it repeats on a set schedule. – Andrew Scagnelli Jun 30 '09 at 18:26

4 Answers4

3

If memory serves, you can create a linked table to an Excel spreadsheet. Once it is linked into Access, you can treat it like any other table.

The query to do the comparison looks something like this (see picture below). It will return rows from the Excel spreadsheet that do not match (right-click on the picture and save it to your computer for a clearer view).

As far as the automation goes, if you're willing to leave a copy of Access running you can set up a Timer in a form. If not, you will have to use Office Automation. See here for a "hello world" example in C#:
http://rnarayana.blogspot.com/2008/02/access-2007-office-automation-using-c.html

alt text

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
  • Thanks for the idea of Office Automation. I was planning on running the Macro powering the updates at startup, and launching that DB in Access with a batch file. – Andrew Scagnelli Jun 30 '09 at 20:00
  • I've created this structure in a query (JOIN LEFT from spreadhseet to table, where ID Is NULL); however, it fails the Is Null test and returns all records, not ones absent from the table. – Andrew Scagnelli Jul 01 '09 at 17:41
  • 1
    In order for a row to be omitted from the output set, each of the joins must match values on both sides. Is it possible one of the joins points to the wrong field on one side? Start with a single join, observe the empty set, and add additional joins one at a time, testing as you go. – Robert Harvey Jul 01 '09 at 19:33
  • The above structure works, the error came from a different source (the test data rather than the SQL query). However, how would it be possible to go in the other direction (return all the records in a table that are not in a linked table) while there are additional columns in the table that are not in the linked table. – Andrew Scagnelli Jul 01 '09 at 19:48
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

barrowc
  • 10,444
  • 1
  • 40
  • 53
  • In a previous question, I asked whether ADO or DAO would be better, and DAO was the response, since ACEDAO is the standard for ACEDB in Access 2k7: http://stackoverflow.com/questions/1039224/is-it-better-to-use-ado-or-dao-in-access-2007/1041717 – Andrew Scagnelli Jul 01 '09 at 02:37
  • In answer to *that* question I answered (paraphrasing) that you shouldn't ACEDAO exclusively or ADO exclusively but use whichever one makes most sense in any given case. In better than 99% of cases there is no significant difference between the two. This is software engineering, not philosophy :) – onedaywhen Jul 01 '09 at 11:33
  • Are ADO & ACEDAO recordsets compatible? – Andrew Scagnelli Jul 01 '09 at 12:28
  • 'compatible'? The answer is no but why do you think that would matter? – onedaywhen Jul 01 '09 at 14:34
  • If they are compatible (ie: mixing them lets an ADO object read an ACEDAO recordset), then the distinction doesn't matter; but for the current project, it does matter since one needs to be used. – Andrew Scagnelli Jul 01 '09 at 19:46
  • An ACEDAO recordset cannot read an ACEDAO recordset. Why do you think it does i.e. what are you doing here? – onedaywhen Jul 02 '09 at 06:00
  • Because you can fabricate an ADO recordset then you can read into it data in memory data and that in-memory data can be an ACEDAO recordset, ADO recordset, whatever. So an ADO recordset is more 'compatible', for what that's worth. But I'm not sure what you end goal is. – onedaywhen Jul 03 '09 at 08:58
1

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;
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
0

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.

JohnFx
  • 34,542
  • 18
  • 104
  • 162
  • It can also have unpredicatable results in regard to target data types. But you're right to add it into the mix for one-offs. – David-W-Fenton Aug 23 '10 at 19:45
  • I've used it a million times for requests like "Can you delete all of the items from TableX with one of the product codes listed in the attached spreadsheet?" – JohnFx Aug 23 '10 at 19:59