Run Time Error 1004 when trying to create a Query Table

Please Login or Register  to view this content.

strConnect and dest look correct to me. The destination worksheet is empty.

Any ideas why I get Run-time error '1004': Application-defined or object-defined error???

Hi Robert,

Thanks for your reply. Much appreciated.

I've attached an Excel 2007 test workbook which should illustrate my approach. BTW, "SQL" is a named range in the workbook which contains my desired SQL, so I can quickly change/debug it.

I think I've isolated the problem to the connection string, but 1) the same connection string works with ADO, and 2) it's pretty much copied from http://www.connectionstrings.com/ace...-0/xlsb-files/

Run the two "Test" macros in the below workbook. The ADO approach works, the QT approach does not. I'd prefer to use the QT approach as I believe the ADO approach would hit the ADO memory leak bug. https://support.microsoft.com/kb/319998

I'd *really* like to get SQL queries working in my Excel application. I need to create a dynamically updated worksheet that summarizes data from 4 other worksheets. I feel the SQL approach is much simpler than complex LOOKUPS (esp. since I know SQL more than VBA).

Thanks again...

Originally Posted by Izandol

I have not looked at the file but for a querytable that connection must begin with "OLEDB;"

Hi Izandol,

1) I've updated the workbook. Still doesn't work. See attached.
2) This documentation of the QueryTable.Add method by Microsoft does not indicate that OLEDB is required, only for ODBC. I'm not saying you're incorrect, but if not, it would be great if MS would fix their documentation.
3) MS error trapping and error messaging is ahem "less than optimal"

Here is the code:

Please Login or Register  to view this content.

Originally Posted by Norie

Scott

Why not store the data in an external data source like another workbook, or database?

And if I do that, does it fix the error 1004?

Originally Posted by Izandol

"OLEDB;" must precede "Provider=" not be part of provider information.

Doh! I knew this, I just didn't pay proper attention to my coding.

It's working now. Thanks for the help, very much appreciated.

Just a couple more questions:

1) Is it possible to create the QT from the user interface, i.e. Data -> Get External Data, ...? It's not a biggie; I can just have a private routine, called manually from the VBE, to create/re-create the QT. During normal operations, I just need to retrieve the existing QT object and call the Refresh method. The SQL code is static and would never change, unless the workbook structure changes.

2) Is it possible to have the QT be a source for its own SQL? For example, if the QT is on a worksheet named "Money", the SQL code contains "... LEFT JOIN [Money$] ...". I'm not sure how to get this to work initially, since the SQL would fail. Perhaps get it to work once, then edit the SQL? I'm also not sure how stable this would be.

2A) Alternatively, just create a new sheet, and have the QT output to a separate worksheet.

Re: #2 - My final QT consolidates data from 4 other worksheets, but has a couple columns that require user input. It would be a better end user experience if s/he could input that data inline with the rest of the QT output. But 2A would also be acceptable.

3) Some of the QT output would have simple formulas, like C1 - A1 (actually it's like Money[#ThisRow],[Agreed] - Money[#ThisRow][Paid] for the Outstanding balance). What's the best approach:

A) Derive the columns via the SQL, then have the QT refresh on say the Worksheet_Activate event? (The edits would be on other worksheets)

B) Or post-process the QT to add formulas? If so, I assume the best approach is the have the SQL output "blank" placeholder columns, which are then filled with formulas via post-processing. As opposed to also trying to insert new columns into the table post-processing.

Thanks!

Originally Posted by Izandol

4. I believe you are using the wrong program. if you must use Excel, the source data is better in a separate workbook.

I totally agree! But any IT professional has run into the situation where the technical architecture is dictated by a non-technical manager, or else by budget constraints. Perhaps I would benefit from a course in Negotiation and Influencing .

I'd rather do this in Access, SQL Server, or a "proper" database, but that's not an option.

Ok, so my actual query is more like this:

Please Login or Register  to view this content.

where Agreed could be Empty. If specified, it will always be less than Applicable. Outstanding = MIN(Agreed,Applicable)-Paid.

Excel is choking on the more advanced SQL. Google says Excel and Access don't support CASE WHEN (isn't CASE WHEN ANSI-standard SQL?), and to use SWITCH or IIF instead. I've tried both, neither work.

The SQL works if I remove WriteOff and Outstanding, or just SELECT * (i.e. the joins work ok).

Originally Posted by Izandol

3. B. the Listobject may have formulas added to it - they will automatically fill as the query table adjusts.

Perhaps I can get this working by adding formulas for the WriteOff and Outstanding calculations.

Googling now...