SQL and VBA weirdness

[SeanDamnit]

Hello Internet,

I have a SQL Query that I'm attempting to pull in to an array. I've done this successfully many times before using a template I found here some time back.

The SQL Query is:

SELECT
     iQmetrix_Employees.Employee_Name as 'EmployeeName'
    ,Loc.FieldText as 'LOCATION'
    ,Dis.FieldText as 'DISTRICT'
    ,Reg.FieldText as 'REGION'
    ,DATEADD(wk,DATEDIFF(wk,6,iQclerk_SaleInvoices.DateCreated),6) as 'WEEK'
    ,(SUM(iQclerk_SaleInvoicesAndProducts.UnitPrice*iQclerk_SaleInvoicesAndProducts.Quantity)-SUM(iQclerk_SaleInvoicesAndProducts.UnitCost*iQclerk_SaleInvoicesAndProducts.Quantity)+SUM(ISNULL(iQclerk_VendorRebateAdjustmentsAndProducts.UnitPrice-iQclerk_SaleInvoicesAndProducts.UnitPrice,0))) AS 'GP' 
FROM 
    iQclerk_SaleInvoices 
    INNER JOIN iQclerk_SaleInvoicesAndProducts 
        ON iQclerk_SaleInvoicesAndProducts.SaleInvoiceID = iQclerk_SaleInvoices.SaleInvoiceID 
    LEFT JOIN iQclerk_VendorRebateAdjustmentsAndProducts 
        ON iQclerk_SaleInvoicesAndProducts.SaleInvoiceID = iQclerk_VendorRebateAdjustmentsAndProducts.SaleInvoiceID 
        AND iQclerk_SaleInvoicesAndProducts.GlobalProductID = iQclerk_VendorRebateAdjustmentsAndProducts.GlobalProductID 
        AND iQclerk_SaleInvoicesAndProducts.SerialNumber = iQclerk_VendorRebateAdjustmentsAndProducts.SerialNumber 
        AND iQclerk_SaleInvoicesAndProducts.Priority = iQclerk_VendorRebateAdjustmentsAndProducts.Priority 
    INNER JOIN iQmetrix_Employees 
        ON iQclerk_SaleInvoices.EmployeeID1 = iQmetrix_Employees.Id_Number 
    INNER JOIN iQclerk_Stores 
        ON iQmetrix_Employees.DefaultLocation = iQclerk_Stores.StoreID 
    INNER JOIN iQclerk_Districts 
        ON iQclerk_Districts.DistrictID = iQclerk_Stores.DistrictID 
    INNER JOIN iQmetrix_Regions 
        ON iQmetrix_Regions.RegionID = iQclerk_Districts.RegionID 
    INNER JOIN LanguageTranslations Loc 
        ON Loc.ReferenceID = iQclerk_Stores.StoreNameID 
    INNER JOIN LanguageTranslations Dis 
        ON Dis.ReferenceID = iQclerk_Districts.DistrictNameID 
    INNER JOIN LanguageTranslations Reg 
        ON Reg.ReferenceID = iQmetrix_Regions.RegionNameID 
WHERE 
    iQclerk_SaleInvoices.DateCreated >= N'04/14/2013 00:00:00' 
    AND iQclerk_SaleInvoices.DateCreated < N'07/15/2013 00:00:00' 
    AND Reg.FieldText <> 'CORPORATE' 
GROUP BY 
    iQmetrix_Employees.Employee_Name
    ,DATEADD(wk,DATEDIFF(wk,6,iQclerk_SaleInvoices.DateCreated),6)
    ,Loc.FieldText
    ,Dis.FieldText
    ,Reg.FieldText

The VBA code is:

Sub GetArray()
Call BuildEmpLast90Array(Date - 1)
End Sub

Private EmpLast90() As Variant
Private EmpLast90Headers() As Variant

Sub BuildEmpLast90Array(EndDate As Date)

    'Declare variables'
        Dim vSelect As String
        Dim vFrom As String
        Dim vGroup As String
        Dim vSQL As String
        Dim vWhere As String
        Dim vCol As Integer
        Dim vRow As Integer
        Dim LastDay As String
        Dim FirstDay As String
        Dim FirstofWeek As Date
        
    'Toggle for Early-Binding'
        'Set objMyConn = New ADODB.Connection
        'Set objMyRecordset = New ADODB.Recordset
        
    'Toggle for Late-Binding'
        Dim objMyCmd As Object
        Dim objMyConn As Object
        Dim objMyRecordset As Object
        Set objMyConn = CreateObject("ADODB.Connection")
        Set objMyRecordset = CreateObject("ADODB.Recordset")
        
    'Set Date Variables
        FirstofWeek = (EndDate - 90) - WorksheetFunction.Weekday((EndDate - 90), 1) + 1 'this gives the first day of the week (Sunday) for whatever week was 90 days ago
        LastDay = "N'" & Format(Month(EndDate), "0#") & "/" & Format(Day(EndDate), "0#") & "/" & Year(EndDate) & " 00:00:00'"
        FirstDay = "N'" & Format(Month(FirstofWeek), "0#") & "/" & Format(Day(FirstofWeek), "0#") & "/" & Year(FirstofWeek) & " 00:00:00'"


    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;DRIVER=SQL Server;SERVER=xxx;UID=xxx;PWD=xxx;APP=2007 Microsoft Office system;DATABASE=xxx"
        objMyConn.Open


    'Create Command Variables'
        vSelect = _
            "SELECT " & _
            "iQmetrix_Employees.Employee_Name as 'EmployeeName'" & _
            ",Loc.FieldText as 'LOCATION'" & _
            ",Dis.FieldText as 'DISTRICT'" & _
            ",Reg.FieldText as 'REGION'" & _
            ",DATEADD(wk,DATEDIFF(wk,6,iQclerk_SaleInvoices.DateCreated),6) as 'WEEK'" & _
            ",(SUM(iQclerk_SaleInvoicesAndProducts.UnitPrice*iQclerk_SaleInvoicesAndProducts.Quantity)-SUM(iQclerk_SaleInvoicesAndProducts.UnitCost*iQclerk_SaleInvoicesAndProducts.Quantity)+SUM(ISNULL(iQclerk_VendorRebateAdjustmentsAndProducts.UnitPrice-iQclerk_SaleInvoicesAndProducts.UnitPrice,0))) AS 'GP' "
        vFrom = _
            "FROM iQclerk_SaleInvoices " & _
            "INNER JOIN iQclerk_SaleInvoicesAndProducts ON iQclerk_SaleInvoicesAndProducts.SaleInvoiceID = iQclerk_SaleInvoices.SaleInvoiceID " & _
            "LEFT JOIN iQclerk_VendorRebateAdjustmentsAndProducts ON iQclerk_SaleInvoicesAndProducts.SaleInvoiceID = iQclerk_VendorRebateAdjustmentsAndProducts.SaleInvoiceID AND iQclerk_SaleInvoicesAndProducts.GlobalProductID = iQclerk_VendorRebateAdjustmentsAndProducts.GlobalProductID AND iQclerk_SaleInvoicesAndProducts.SerialNumber = iQclerk_VendorRebateAdjustmentsAndProducts.SerialNumber AND iQclerk_SaleInvoicesAndProducts.Priority = iQclerk_VendorRebateAdjustmentsAndProducts.Priority " & _
            "INNER JOIN iQmetrix_Employees ON iQclerk_SaleInvoices.EmployeeID1 = iQmetrix_Employees.Id_Number " & _
            "INNER JOIN iQclerk_Stores ON iQmetrix_Employees.DefaultLocation = iQclerk_Stores.StoreID " & _
            "INNER JOIN iQclerk_Districts ON iQclerk_Districts.DistrictID = iQclerk_Stores.DistrictID " & _
            "INNER JOIN iQmetrix_Regions ON iQmetrix_Regions.RegionID = iQclerk_Districts.RegionID " & _
            "INNER JOIN LanguageTranslations Loc ON Loc.ReferenceID = iQclerk_Stores.StoreNameID " & _
            "INNER JOIN LanguageTranslations Dis ON Dis.ReferenceID = iQclerk_Districts.DistrictNameID " & _
            "INNER JOIN LanguageTranslations Reg ON Reg.ReferenceID = iQmetrix_Regions.RegionNameID "
        vWhere = _
            "WHERE " & _
            "iQclerk_SaleInvoices.DateCreated >= " & FirstDay & " " & _
            "AND iQclerk_SaleInvoices.DateCreated < " & LastDay & " " & _
            "AND Reg.FieldText <> 'CORPORATE' "
        vGroup = _
            "GROUP BY " & _
            "iQmetrix_Employees.Employee_Name,DATEADD(wk,DATEDIFF(wk,6,iQclerk_SaleInvoices.DateCreated),6),Loc.FieldText,Dis.FieldText,Reg.FieldText"
            
        vSQL = vSelect & vFrom & vWhere & vGroup
        'Debug.Print vSelect & Chr(13) & vFrom & Chr(13) & vWhere & Chr(13) & vGroup


    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open vSQL
        
    'Copy Data to Array
        Erase EmpLast90Headers
        For i = 0 To objMyRecordset.Fields.Count - 1
            ReDim Preserve EmpLast90Headers(i)
            EmpLast90Headers(i) = objMyRecordset.Fields(i).Name
        Next i
        Erase EmpLast90
        EmpLast90 = objMyRecordset.GetRows()
        vCol = UBound(EmpLast90, 1)
        vRow = UBound(EmpLast90, 2)
                
    'Copy Data to Sheet
        'ActiveSheet.Cells(1, 1).CopyFromRecordset objMyRecordset


End Sub

The query is supposed to take sales data for the last ~90 days (it rounds the Start date down to the closest Sunday), and group by Employee/Week.

I get an error when I run this through the VBA: "Run-Time error '-2147217871 (80040e31)': Automation Error". This is usually the error I get when there's a problem in the actual SQL Query...however the query runs just fine when run through a Report Builder software, or Excel's Data Connections wizard.

I originally thought I forgot a comma or quotation mark or something somewhere, however that isn't the case. If I change the FirstOfWeek variable to go back 70 days instead of 90, the script runs...but it will give an error for anything that goes back 71 or more days. This is strange, because as I mentioned before the query works just fine going back 90 days or even more when run outside of VBA. I expect ~6200 line items from a 90 day run, and about 5000 line items from a 70 day run.

I'm not sure what I'm doing wrong here. If anyone has any advice, I'd appreciate it.

 

[Kyle123]

Just to rule out the basics, have you tried passing your dates as yyyy/mm/dd hh:mm:ss, I'd neverpass dates in a regional format to sql server, it leads to all kinds of naughtiness

 

[SeanDamnit]

Just to rule out the basics, have you tried passing your dates as yyyy/mm/dd hh:mm:ss, I'd neverpass dates in a regional format to sql server, it leads to all kinds of naughtiness

Just tried it, and it's the same issue - works great if the date range is 70 days or less, but nothing higher

 

[SeanDamnit]

Well, I got this figured out. Looks like adding 'CommandTimeout = 0' was the key. Hopefully this is helpful for anyone else with the issue.

 

[junaid]

Well, I got this figured out. Looks like adding 'CommandTimeout = 0' was the key. Hopefully this is helpful for anyone else with the issue.

Hi Sean,

I am facing this issue while accessing MS SQL DB from VBA. May I know where you added 'CommandTimeout = 0' so the issue got resolved?
Thanks.

 

[SeanDamnit]

Hi Sean,

I am facing this issue while accessing MS SQL DB from VBA. May I know where you added 'CommandTimeout = 0' so the issue got resolved?
Thanks.

Sorry if this is a little late in response, I don't check this site daily, but I added it just after I set the ActiveConnection, but before I put in the Open command. In my code it looks like this:

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyConn.CommandTimeout = 0
        objMyRecordset.Open vSQL