如何修复 中的运行时错误'5'。命令文本数组(...选择....)陈述

Question

我在上个月使用QODBC驱动程序将QuickBooks的每月填充费用从QuickBooks中获得。它可以正常工作，直到我从客户记录中添加另一个字段。它因运行时错误'5'而失败：无效的过程调用或参数。

带有字段名称客户的选择语句。fullname是其中之一，它可以正常工作。如果我用customer.isactive字段替换customer.fullname，它也可以正常工作。但是，如果我添加字段" customer.fullname，customer.isactive"，则执行SELECT语句会产生错误。我研究了这个问题，但找不到解决问题或如何解决问题的原因。

此代码失败：

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
    "ODBC;DSN=QuickBooks Data QRemote;SERVER=QODBC;OptimizerDBFolder=%UserProfile%QODBC Driver for QuickBooksOptimizer;OptimizerAllowDirtyReads" _
    ), Array("=N;SyncFromOtherTables=N;ForceSDKVersion=<default SDK>")), _
    Destination:=Range("$A$1")).QueryTable
'        .CommandText = CmdString
'        .CommandText = Array(SelClause, FromClause, WhereClause)
    .CommandText = Array( _
    "SELECT Charge.CustomerRefListID, Customer.FullName, Customer.IsActive, Charge.TxnDate, Item.FullName, Charge.Rate, Charge.""Desc""" & Chr(13) & "" & Chr(10) & _
     "FROM Charge Charge, Customer Customer, Item Item" & Chr(13) & "" & Chr(10) & _
     "WHERE Charge.CustomerRefListID = Customer.ListID AND Charge.ItemRefListID = Item.ListID AND " _
    , "((Charge.TxnDate=" & formateddate & ") AND (Item.SalesOrPurchaseAccountRefFullName='Weekly Services'))")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table_Query_Charges_from_QuickBooks_Data"
    .Refresh BackgroundQuery:=False
End With

---

以下代码中此选择语句的工作正常。

    .CommandText = Array( _
    "SELECT Charge.CustomerRefListID, Customer.FullName, Charge.TxnDate, Item.FullName, Charge.Rate, Charge.""Desc""" & Chr(13) & "" & Chr(10) & _
     "FROM Charge Charge, Customer Customer, Item Item" & Chr(13) & "" & Chr(10) & _
     "WHERE Charge.CustomerRefListID = Customer.ListID AND Charge.ItemRefListID = Item.ListID AND " _
    , "((Charge.TxnDate=" & formateddate & ") AND (Item.SalesOrPurchaseAccountRefFullName='Weekly Services'))")

---

这也有效。

    .CommandText = Array( _
    "SELECT Charge.CustomerRefListID, Customer.IsActive, Charge.TxnDate, Item.FullName, Charge.Rate, Charge.""Desc""" & Chr(13) & "" & Chr(10) & _
     "FROM Charge Charge, Customer Customer, Item Item" & Chr(13) & "" & Chr(10) & _
     "WHERE Charge.CustomerRefListID = Customer.ListID AND Charge.ItemRefListID = Item.ListID AND " _
    , "((Charge.TxnDate=" & formateddate & ") AND (Item.SalesOrPurchaseAccountRefFullName='Weekly Services'))")

---

两个字段，customer.fullname和customer..Sactive是有效的字段，并且Select语句与其中一个效果很好。如何使其与Select语句中的两个字段一起使用？

Answer 1

我做了更多的实验，然后将select语句从array((更改为字符串正常工作。......

   CmdString = "SELECT Charge.CustomerRefListID, Customer.FullName, Customer.IsActive, Charge.TxnDate, Item.FullName, Charge.Rate, Charge.""Desc"" FROM Charge Charge, Customer Customer, Item Item WHERE Charge.CustomerRefListID = Customer.ListID AND Charge.ItemRefListID = Item.ListID AND ((Charge.TxnDate=" & formateddate & ") AND (Item.SalesOrPurchaseAccountRefFullName='Weekly Services'))"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
    "ODBC;DSN=QuickBooks Data QRemote;SERVER=QODBC;OptimizerDBFolder=%UserProfile%QODBC Driver for QuickBooksOptimizer;OptimizerAllowDirtyReads" _
    ), Array("=N;SyncFromOtherTables=N;ForceSDKVersion=<default SDK>")), _
    Destination:=Range("$A$1")).QueryTable
    .CommandText = CmdString
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table_Query_Charges_from_QuickBooks_Data"
    .Refresh BackgroundQuery:=False
End With

........