In Access 2003 I used to import special xls files provided to us by a third party. I used ADODB for this, which worked perfectly.
ADODB is no longer an option in Access 2007/2010, and I don't think you can "query" a spreadsheet using DAO or ADO.
So, are there any alternatives?
In your VBA project in Access click on Tools / References then find "Microsoft Excel 14.0 Object Library". This will give you access to all of the VBA Excel objects so you can do whatever you want to the spreadsheet.
As others have said running queries on the sheet is not an option. If you're used to thinking in terms of databases think about running a check on the template to make sure the headings match so you more than likely have a good file you're reading from. Then once you've verified the document setup SQL inserts on the lines with records on them into a table in an Access Database then run your queries from there. Check here for writing your SQL code:
If you want to get more advanced in your VBA if your files are small you could run searches by setting up an object with setters/getters and store the records into a collection class then iterate across it with the data you're looking for.
I do this in some projects where I know the lists are small, gets away from reading/writing to tables when I really just want to read the data and run some real basic calculations like count the number of a particular widget and place the count on a report. Collection classes are great for this. Here's a good link for some more info on collections:
Good luck!
I don't think you can "query" a spreadsheet using DAO or ADO.
You can indeed query an Excel workbook using ADO with the OLE DB provider for Access (Jet, whatever). See this MSDN article for more details.
ADODB is no longer an option in Access 2007/2010,
That is not the case. I think you must have misunderstood something, which is hardly surprising considering there is a lot of nonsense out there on the interwebs surrounding "ADO vs DAO" in Access.
I'm not exactly sure what happened when ADO classic was introduced to the Access2000 community but it seems that a lot of old-timers were left with hurt egos/pride. The marketing message from Microsoft was in effect, "DAO is now the old way of working and ADO is the new." In a deliberate move by MS,, new engine features were generally only available via ADO.
As is often the case when entrench positions are threatened, there was a counter-movement by long time Access+DAO fans. This often took the form of, "throw enough mud and some will stick." People newly arrived in AccessLand would be confused by the mixed messages. In the newsgroups, aspiring Access MVPs emulated existing Access MVPs' anti-ADO stance.
Between the 200 and 2007 releases, the Access product became IMO somewhat stale as regards the engine, responsibility for which had been handed to the SQL Server team, who had for all practical purposes given up on it: their attempts to make it comply with entry-level Standard SQL-92 were thwarted by the Windows team, whose components relied on features that flew in the face of the Standard. The ADO classic team had similarly been disbanded. Visual Basic COM, which effectively shared VBA libraries with the Office suite, was killed off in favour of VB.NET and ADO.NET was born,
The Access2007 team shook things up again. They reclaimed the engine from the SQL Server and Windows teams by taking a private branch. They ditched user level security, presumably because it was too complex for them to maintain. They added multi-valued types that arguably violate 1NF. Brave stuff! While some features could be accommodated in ADO classic by authoring a new OLE DB provider for Access, others could not. For example, full support (e.g. updating via SQL) for multi-valued data types in ADO would require a change to the ADO classic libraries which the Access team does not own.
Because DAO was owned by Access, the new version (called ACEDAO) got features the new OLE DB provider with the old ADO classic libraries would lack (although ADO was required to use the engine on 64 bit machines). However, what they did not do was to retrospectively fix DAO to accommodate the Access2000-era functionality that went into ADO only.
Naturally, the Access team promoted ACEDAO as first class citizen. Of course, the long-time Access+DAO fans were delighted: "ADO has been deprecated in favour of ADO.NET," they crowed. In truth, the net result is a mixed bag. See this thread for full details.
