Executing ADO recordset for ODBC connection in VBA Excel; Joining multiple tables with SELECT using WHERE and ORDER BY; Error '3001'

I'm trying to return a recordset using the following code:

"sql" is what I really want to execute, the other 2 strings are trial and error to figure out where the problem is arising.

I generated sql and sql1 in Access and sql2 in Microsoft Query.

The error: Run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict wth one another.

I don't have much experience with SQL and ADO.

If the problem has something to do with ODBC driver is there a workaround?

Is it then possible to break up the query by running several simple queries then running queries on the recordsets?

Sub Revenue_Report()
    Dim conn As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    Dim sql As String, sql1 as string, sql2 as string
   
    conn.Open "DSN=ACCOUNTS"

    sql = "SELECT INVOICE_HEADER.Territory, INVOICE_DETAIL.ProductGroup, INVOICE_DETAIL.TaxableAmt " & _
          "FROM ((CUSTOMER_MASTER INNER JOIN " & _
          "(INVOICE_DETAIL INNER JOIN INVOICE_HEADER ON (INVOICE_DETAIL.DocType = INVOICE_HEADER.DocType) AND " & _
          "(INVOICE_DETAIL.InternalDocNum = INVOICE_HEADER.InternalDocNum)) ON CUSTOMER_MASTER.Code = INVOICE_HEADER.Code) " & _
          "INNER JOIN PRODUCT_MASTER ON INVOICE_DETAIL.Code = PRODUCT_MASTER.Code) " & _
          "INNER JOIN PRODUCTGROUP_NAME ON PRODUCT_MASTER.ProductGroup = PRODUCTGROUP_NAME.Code" & _
          "WHERE (((INVOICE_DETAIL.LineType) <> 9) And ((INVOICE_DETAIL.DocType) <= 3)) " & _
          "ORDER BY INVOICE_HEADER.Territory, INVOICE_DETAIL.ProductGroup"
   
    sql1 = "SELECT INVOICE_HEADER.Territory, INVOICE_DETAIL.ProductGroup, INVOICE_DETAIL.TaxableAmt " & _
           "FROM INVOICE_DETAIL " & _
           "INNER JOIN INVOICE_HEADER ON (INVOICE_DETAIL.DocType = INVOICE_HEADER.DocType AND INVOICE_DETAIL.InternalDocNum = INVOICE_HEADER.InternalDocNum) " & _
           "WHERE INVOICE_DETAIL.DocType <> 9 And INVOICE_DETAIL.LineType <= 3" & _
           "ORDER BY INVOICE_HEADER.Territory, INVOICE_DETAIL.ProductGroup"
   
    sql2 = "SELECT INVOICE_DETAIL.ProductGroup, INVOICE_DETAIL.TaxableAmt, INVOICE_HEADER.Territory " & _
           "FROM INVOICE_DETAIL INVOICE_DETAIL, INVOICE_HEADER INVOICE_HEADER " & _
           "WHERE INVOICE_DETAIL.InternalDocNum = INVOICE_HEADER.InternalDocNum And INVOICE_HEADER.DocType = INVOICE_DETAIL.DocType " & _
           "And ((INVOICE_DETAIL.DocType <> 9) And (INVOICE_DETAIL.LineType <= 3)) " & _
           "ORDER BY INVOICE_HEADER.Territory, INVOICE_DETAIL.ProductGroup"
   
    rec.Open SplitToArray(sql1, 255), conn
   
    Sheets("Sheet2").Cells(1, 1).CopyFromRecordset rec
   
    rec.Close
    conn.Close

End Sub
SassManAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leonstrykerCommented:
You need a space at the end of this line:

"INNER JOIN PRODUCTGROUP_NAME ON PRODUCT_MASTER.ProductGroup = PRODUCTGROUP_NAME.Code" & _

Like this:

"INNER JOIN PRODUCTGROUP_NAME ON PRODUCT_MASTER.ProductGroup = PRODUCTGROUP_NAME.Code " & _

Leon
SassManAuthor Commented:
Still doesn't work.

Sorry I was experimenting with the code by commenting lines out to see where it was falling and forgot to put the space back in before I posted. In my code there is a space at the end of each line before continuation.

I'm confused because queries that work with Querytables don't seem to work with ADO.

EG. The following works if I use QueryTable but doesn't if I use ADO:

    sql3 = "SELECT INVOICE_DETAIL.Territory, INVOICE_DETAIL.ProductGroup, PRODUCTGROUP_NAME.Description, SUM(INVOICE_DETAIL.TaxableAmt) ""Actual""" & _
           "FROM CUSTOMER_MASTER CUSTOMER_MASTER, INVOICE_DETAIL INVOICE_DETAIL, INVOICE_HEADER INVOICE_HEADER, PRODUCT_MASTER PRODUCT_MASTER, PRODUCTGROUP_NAME PRODUCTGROUP_NAME " & _
           "WHERE INVOICE_DETAIL.InternalDocNum = INVOICE_HEADER.InternalDocNum And INVOICE_HEADER.DocType = INVOICE_DETAIL.DocType And CUSTOMER_MASTER.Code = INVOICE_HEADER.Code And PRODUCT_MASTER.ProductGroup = PRODUCTGROUP_NAME.Code And INVOICE_DETAIL.ProductGroup = PRODUCT_MASTER.ProductGroup And ((INVOICE_DETAIL.DocType <> 9) And (INVOICE_DETAIL.LineType <= 3)) " & _
           "GROUP BY INVOICE_DETAIL.Territory, INVOICE_DETAIL.ProductGroup, PRODUCTGROUP_NAME.Description " & _
           "ORDER BY INVOICE_DETAIL.Territory, INVOICE_DETAIL.ProductGroup"

From my previous examples the first SQL (sql) doesn't work even if I comment out the WHERE and ORDER BY whereas the second two (sql1 and sql2) will work.
Anthony PerkinsCommented:
>>I'm confused because queries that work with Querytables don't seem to work with ADO.<<
Simple put: they are not the same.

1. Consider replacing the double quotes in ""Actual""" with single quotes and adding a space as in:
SUM(INVOICE_DETAIL.TaxableAmt) 'Actual' " & _

2. Replace all joins using the old deprecated join syntax with the newer and more standard Inner Join

3. You are aliasing a table with the same name of the table.  This is harmless, but does not make a lot of sense.  Change:
PRODUCTGROUP_NAME PRODUCTGROUP_NAME
to:
PRODUCTGROUP_NAME

4.  Let's focus on one query that fails and the resulting error message, rather than four.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

leonstrykerCommented:
>3. You are aliasing a table with the same name of the table.  This is harmless, but does not make a lot of sense.  Change:
>PRODUCTGROUP_NAME PRODUCTGROUP_NAME
>to:
>PRODUCTGROUP_NAME

Actually, making it something like

PRODUCTGROUP_NAME PR_NM

Would make it easier to read as well.
Anthony PerkinsCommented:
>>Would make it easier to read as well.<<
Too true.
SassManAuthor Commented:
Thanks for your feedback so far.

Sorry acperkins, I tried providing as much information in my first post with the hope that it would make it easier to see what I was missing.

I was reading some examples on setting up simple queries in VBA and a suggestion was to use either MS query or Access to help build more complex queries.

So the following query was setup in Access (note it works in Access):
    sql = "SELECT INVOICE_HEADER.Territory, INVOICE_DETAIL.ProductGroup, INVOICE_DETAIL.TaxableAmt " & _
          "FROM ((CUSTOMER_MASTER INNER JOIN " & _
          "(INVOICE_DETAIL INNER JOIN INVOICE_HEADER ON (INVOICE_DETAIL.DocType = INVOICE_HEADER.DocType) AND " & _
          "(INVOICE_DETAIL.InternalDocNum = INVOICE_HEADER.InternalDocNum)) ON CUSTOMER_MASTER.Code = INVOICE_HEADER.Code) " & _
          "INNER JOIN PRODUCT_MASTER ON INVOICE_DETAIL.Code = PRODUCT_MASTER.Code) " & _
          "INNER JOIN PRODUCTGROUP_NAME ON PRODUCT_MASTER.ProductGroup = PRODUCTGROUP_NAME.Code " & _
          "WHERE (((INVOICE_DETAIL.LineType) <> 9) AND ((INVOICE_DETAIL.DocType) <= 3)) " & _
          "ORDER BY INVOICE_HEADER.Territory, INVOICE_DETAIL.ProductGroup"

When I run the macro the macro crashes at the following line:
    rec.Open SplitToArray(sql, 255), conn

The error it returns:
The error: Run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict wth one another.

However, if I run the same query using Querytable, it works!

Why does it work using Querytable and not using ADO?



leonstrykerCommented:
What is this SplitToArray thinggy?  The line to return a recordset should look something like this:

rec.Open sql, conn, adOpenStatic, adLockOptimistic

LEon

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.