We help IT Professionals succeed at work.

What about using ADO with Excel 2007

shambalad
shambalad asked
on
As I survey the literature in books and online for developing applications in Excel 2007, I see little about using ADO to create recordsets of worksheets and ranges. It seems to work fairly well. albeit with some limitations, such as it doesn't care much for binary (xlsb) workbooks.
I find myself wonderintg, is there any compelling reason I should avoid using ADO with Excel 2007?
Comment
Watch Question

Commented:
Hi,

First of all, why should you? Unless you planning to use PivotTable, Excel functions for calculations or creating graph.

Depending on your needs, if you are planning to store data only or need to have nice interface for user to input data (records) then you might need Access for these.

But if you need to perform things on like pivottable or doing complex calculation using Excel functions then you might want to consider of reading data using ADO from data sources, eg SQL Server into Excel to perform actions above.

So in this case, there are situations that you "MUST" use ADO for data retrieval into Excel and there are situations where ADO is not needed at all when all data needed already in that Excel file.

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
What problem do you have with xlsb files?
Top Expert 2011
Commented:
I don't see why you should not use it - I do quite a lot for complex selection based on multiple criteria, and for some simple applications so that they can be distributed to third parties without the need for them to deal with a full-scale access solution.  It can be really useful for aggregation inside VBA without the need to create a full pivot table sheet and read the answer from it.
In general I tend only to use SELECT functionality - I've found the Update, Delete and Insert unreliable or not working at all, and you don't normally need them as it's relatively easy to do it using VBA. The main pain I find is with dates - ADODB wants to revert dates to US format at all possible opportunities - but looking at your time zone I'm guessing that won't be an issue for you.

Author

Commented:
khairil -
Actually, ADO has served a purpose for me already with an Access application I have been working on. Without going too far into the architecture of this particular application, the executing procedure is in one database file (accdb) and the Access table being loaded with the Excel row data is in another database file (accdb). In fact, the 'executing' database has no tables at all. Normally, one would pull a range of data into a table in the local database using a TransferSpreadsheet command. But that wasn't going to be practical in the above environment. Using ADO to open a recordset over a dynamically created range of the used data in part of a worksheet fit the bill quite well.

rorya -
If I try to open a range in a binary workbook, I get the following ADO error:
ADO Error: -2147467259
Description: System resource exceeded.
Native Error: -66192371
Source: Microsoft Office Access Database Engine
SQL State: 30353035

Andrew -
That would seem to be my experience with using ADO so far. Thanks for the heads-up on potential pitfalls.

Todd
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Do you get that error with any range, or just large ones, or what? (haven't had that issue myself)

Regards,
Rory

Author

Commented:
Rory - I'm going to open a new question on the binary files.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.