SeanDamnit
Board Regular
- Joined
- Mar 13, 2011
- Messages
- 151
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:
The VBA code is:
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.
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:
Code:
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:
Code:
Sub GetArray()
Call BuildEmpLast90Array(Date - 1)
End Sub
Code:
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.