Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-04-13
7
Medium Priority
?
3,591 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:SassMan
  • 3
  • 2
  • 2
7 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 16454695
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
 

Author Comment

by:SassMan
ID: 16458199
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 16458527
>>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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:leonstryker
ID: 16468720
>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16474473
>>Would make it easier to read as well.<<
Too true.
0
 

Author Comment

by:SassMan
ID: 16486967
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
 
LVL 29

Accepted Solution

by:
leonstryker earned 1600 total points
ID: 16487413
What is this SplitToArray thinggy?  The line to return a recordset should look something like this:

rec.Open sql, conn, adOpenStatic, adLockOptimistic

LEon
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month21 days, 6 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question