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?
 
leonstrykerCommented:
What is this SplitToArray thinggy?  The line to return a recordset should look something like this:

rec.Open sql, conn, adOpenStatic, adLockOptimistic

LEon
0
 
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
0
 
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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.
0
 
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.
0
 
Anthony PerkinsCommented:
>>Would make it easier to read as well.<<
Too true.
0
 
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?



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.