VBA Incomplete Data Source Error

[playern07]

I have been working on a code to automate running a particular set of queries. I keep running into an incomplete datasource error. Below you will find the code along with the highlighted problem area. Any help is appreciated.

Sub UpdateWeeklyValues()
'clear old query table from StoreVolumeData sheet
Sheet1.Select
Columns("A:I").Select
Selection.ClearContents
Range("A1").Select
 
'build query in text format
 
BreadQRY = "(SELECT " & _
"STIL.FACILITY_ID, " & _
"STIL.SALES_TRANS_DT, " & _
"'02-Bread ' as Category, " & _
"'Afternoon' as TimePeriod, " & _
"( 'Between_' || Trim(Min(STIL.SALES_TRANS_TM)) || '_' || Trim(Max(STIL.SALES_TRANS_TM)) ) as TimeCriteria, " & _
"Sum(STIL.ITEM_QTY) as TotalItemQty " & _
"FROM " & _
"SALES_TRANS_ITEM_LINE as STIL, " & _
"ITEM_DIM as ID " & _
"WHERE " & _
"STIL.ITEM_ID = ID.ITEM_ID " & _
"AND STIL.UPC_SALES_TRANS_TYPE_CD In ('0','2','8') " & _
"AND STIL.FACILITY_ID = 356 " & _
"AND STIL.SALES_TRANS_DT = '2011-07-18' " & _
"AND STIL.SALES_TRANS_TM Between 700 and 1517 " & _
"AND ID.CATEGORY_ID = 1402 " & _
"GROUP BY " & _
"1,2,3,4) " & _
"UNION ALL "
 
 
ButterQRY = "(SELECT " & _
"STIL.FACILITY_ID, " & _
"STIL.SALES_TRANS_DT, " & _
"'03-Butter' as Category, " & _
"'Afternoon' as TimePeriod, " & _
"( 'Between_' || Trim(Min(STIL.SALES_TRANS_TM)) || '_' || Trim(Max(STIL.SALES_TRANS_TM)) ) as TimeCriteria, " & _
"Sum(STIL.ITEM_QTY) as TotalItemQty " & _
"FROM " & _
"SALES_TRANS_ITEM_LINE as STIL, " & _
"ITEM_DIM as ID " & _
"WHERE " & _
"STIL.ITEM_ID = ID.ITEM_ID " & _
"AND STIL.UPC_SALES_TRANS_TYPE_CD In ('0','2','8') " & _
"AND STIL.FACILITY_ID = 356 " & _
"AND STIL.SALES_TRANS_DT = '2011-07-18' " & _
"AND STIL.SALES_TRANS_TM Between 700 and 1526 " & _
"AND ID.CATEGORY_ID = 1403 " & _
"GROUP BY " & _
"1,2,3,4) " & _
"UNION ALL "
 
 
CreamersQRY = "(SELECT " & _
"STIL.FACILITY_ID, " & _
"STIL.SALES_TRANS_DT, " & _
"'05-Creamers' as Category, " & _
"'Afternoon' as TimePeriod, " & _
"( 'Between_' || Trim(Min(STIL.SALES_TRANS_TM)) || '_' || Trim(Max(STIL.SALES_TRANS_TM)) ) as TimeCriteria, " & _
"Sum(STIL.ITEM_QTY) as TotalItemQty " & _
"FROM " & _
"SALES_TRANS_ITEM_LINE as STIL, " & _
"ITEM_DIM as ID " & _
"WHERE " & _
"STIL.ITEM_ID = ID.ITEM_ID " & _
"AND STIL.UPC_SALES_TRANS_TYPE_CD In ('0','2','8') " & _
"AND STIL.FACILITY_ID = 356 " & _
"AND STIL.SALES_TRANS_DT = '2011-07-18' " & _
"AND STIL.SALES_TRANS_TM Between 700 and 1411 " & _
"AND ID.CATEGORY_ID = 1405 " & _
"GROUP BY " & _
"1,2,3,4) " & _
"UNION ALL "
 
YogurtQRY = "(SELECT " & _
"STIL.FACILITY_ID, " & _
"STIL.SALES_TRANS_DT, " & _
"'06-Yogurt' as Category, " & _
"'Afternoon' as TimePeriod, " & _
"( 'Between_' || Trim(Min(STIL.SALES_TRANS_TM)) || '_' || Trim(Max(STIL.SALES_TRANS_TM)) ) as TimeCriteria, " & _
"Sum(STIL.ITEM_QTY) as TotalItemQty " & _
"FROM " & _
"SALES_TRANS_ITEM_LINE as STIL, " & _
"ITEM_DIM as ID " & _
"WHERE " & _
"STIL.ITEM_ID = ID.ITEM_ID " & _
"AND STIL.UPC_SALES_TRANS_TYPE_CD In ('0','2','8') " & _
"AND STIL.FACILITY_ID = 356 " & _
"AND STIL.SALES_TRANS_DT = '2011-07-18' " & _
"AND STIL.SALES_TRANS_TM Between 700 and 1445 " & _
"AND ID.CATEGORY_ID = 1406 " & _
"GROUP BY " & _
"1,2,3,4) " & _
"UNION ALL "
 
DessertsQRY = "(SELECT " & _
"STIL.FACILITY_ID, " & _
"STIL.SALES_TRANS_DT, " & _
"'07-Desserts' as Category, " & _
"'Afternoon' as TimePeriod, " & _
"( 'Between_' || Trim(Min(STIL.SALES_TRANS_TM)) || '_' || Trim(Max(STIL.SALES_TRANS_TM)) ) as TimeCriteria, " & _
"Sum(STIL.ITEM_QTY) as TotalItemQty " & _
"FROM " & _
"SALES_TRANS_ITEM_LINE as STIL, " & _
"ITEM_DIM as ID " & _
"WHERE " & _
"STIL.ITEM_ID = ID.ITEM_ID " & _
"AND STIL.UPC_SALES_TRANS_TYPE_CD In ('0','2','8') " & _
"AND STIL.FACILITY_ID = 356 " & _
"AND STIL.SALES_TRANS_DT = '2011-07-18' " & _
"AND STIL.SALES_TRANS_TM Between 700 and 1529 " & _
"AND ID.CATEGORY_ID = 1407 " & _
"GROUP BY " & _
"1,2,3,4) " & _
"UNION ALL "
 
EggsPicklesQRY = "(SELECT " & _
"STIL.FACILITY_ID, " & _
"STIL.SALES_TRANS_DT, " & _
"'08,12,13-Eggs/Pickles' as Category, " & _
"'Afternoon' as TimePeriod, " & _
"( 'Between_' || Trim(Min(STIL.SALES_TRANS_TM)) || '_' || Trim(Max(STIL.SALES_TRANS_TM)) ) as TimeCriteria, " & _
"Sum(STIL.ITEM_QTY) as TotalItemQty " & _
"FROM " & _
"SALES_TRANS_ITEM_LINE as STIL, " & _
"ITEM_DIM as ID " & _
"WHERE " & _
"STIL.ITEM_ID = ID.ITEM_ID " & _
"AND STIL.UPC_SALES_TRANS_TYPE_CD In ('0','2','8') " & _
"AND STIL.FACILITY_ID = 356 " & _
"AND STIL.SALES_TRANS_DT = '2011-07-18' " & _
"AND STIL.SALES_TRANS_TM Between 700 and 1400 " & _
"AND ID.CATEGORY_ID In(1408,1412,1413) " & _
"GROUP BY " & _
"1,2,3,4) " & _
"UNION ALL "
 
MilkQRY = "(SELECT " & _
"STIL.FACILITY_ID, " & _
"STIL.SALES_TRANS_DT, " & _
"'10,11-Milk' as Category, " & _
"'Afternoon' as TimePeriod, " & _
"( 'Between_' || Trim(Min(STIL.SALES_TRANS_TM)) || '_' || Trim(Max(STIL.SALES_TRANS_TM)) ) as TimeCriteria, " & _
"Sum(STIL.ITEM_QTY) as TotalItemQty " & _
"FROM " & _
"SALES_TRANS_ITEM_LINE as STIL, " & _
"ITEM_DIM as ID " & _
"WHERE " & _
"STIL.ITEM_ID = ID.ITEM_ID " & _
"AND STIL.UPC_SALES_TRANS_TYPE_CD In ('0','2','8') " & _
"AND STIL.FACILITY_ID = 356 " & _
"AND STIL.SALES_TRANS_DT = '2011-07-18' " & _
"AND STIL.SALES_TRANS_TM Between 700 and 1358 " & _
"AND ID.CATEGORY_ID In(1410,1411) " & _
"GROUP BY " & _
"1,2,3,4) " & _
"UNION ALL "
 
CreamChesseQRY = "(SELECT " & _
"STIL.FACILITY_ID, " & _
"STIL.SALES_TRANS_DT, " & _
"'15-Cream Cheese' as Category, " & _
"'Afternoon' as TimePeriod, " & _
"( 'Between_' || Trim(Min(STIL.SALES_TRANS_TM)) || '_' || Trim(Max(STIL.SALES_TRANS_TM)) ) as TimeCriteria, " & _
"Sum(STIL.ITEM_QTY) as TotalItemQty " & _
"FROM " & _
"SALES_TRANS_ITEM_LINE as STIL, " & _
"ITEM_DIM as ID " & _
"WHERE " & _
"STIL.ITEM_ID = ID.ITEM_ID " & _
"AND STIL.UPC_SALES_TRANS_TYPE_CD In ('0','2','8') " & _
"AND STIL.FACILITY_ID = 356 " & _
"AND STIL.SALES_TRANS_DT = '2011-07-18' " & _
"AND STIL.SALES_TRANS_TM Between 700 and 1416 " & _
"AND ID.CATEGORY_ID =1415 " & _
"GROUP BY " & _
"1,2,3,4) " & _
"UNION ALL "
 
OtherQRY = "(SELECT " & _
"STIL.FACILITY_ID, " & _
"STIL.SALES_TRANS_DT, " & _
"ID.CATEGORY_NM as Category, " & _
"'Afternoon' as TimePeriod, " & _
"( 'Between_' || Trim(Min(STIL.SALES_TRANS_TM)) || '_' || Trim(Max(STIL.SALES_TRANS_TM)) ) as TimeCriteria, " & _
"Sum(STIL.ITEM_QTY) as TotalItemQty " & _
"FROM " & _
"SALES_TRANS_ITEM_LINE as STIL, " & _
"ITEM_DIM as ID " & _
"WHERE " & _
"STIL.ITEM_ID = ID.ITEM_ID " & _
"AND STIL.UPC_SALES_TRANS_TYPE_CD In ('0','2','8') " & _
"AND STIL.FACILITY_ID = 356 " & _
"AND STIL.SALES_TRANS_DT = '2011-07-18' " & _
"AND STIL.SALES_TRANS_TM Between 700 and 1439 " & _
"AND ID.CATEGORY_ID In(1409,1416,1495) " & _
"GROUP BY " & _
"1,2,3,4 " & _
")) as C " & _
"WHERE C.Weeks > 13 GROUP BY 1,2,3,4,5 ORDER BY 1,2,3,4 "
 
AllItemsQRY = BreadQRY & ButterQRY & CreamersQRY & YogurtQRY & DessertsQRY & EggsPicklesQRY & MilkQRY & CreamChesseQRY & OtherQRY
 
 
'connect to ODBC and run query
 
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=Teradata Production;;; AUTHENTICATION=;AUTHENTICATONPARAMETER=;" _
, Destination:=Sheet1.Range("$A$1")).QueryTable
.CommandText = ALLWeeklyQRY
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "AllItemsQRY"
End With
 
Range("A1").Select
[COLOR=red]Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False[/COLOR]

Columns("A:I").Select
Selection.Copy
Columns("L:T").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("L1").Select
End Sub

 

[Ruddles]

Have you examined the contents of each part of the query string after you have built it?

If you don't know how to do this, insert a Debug.Print BreadQRY statement after you've built BreadQRY, repeat for the other query strings, then run the code and check what's printed out in the Immediate window (Ctrl-G).

Does the SQL all look valid to you?

 

[playern07]

I will try the method you recommended. The SQL ran great in Acces. All I did was convert it directly to VBA.

 

[Ruddles]

My suggestion isn't intended to highlight issues with the original SQL, it's to highlight problems with the conversion process!

 

[Ruddles]

With particular attention to the Between...Trim...TimeCriteria bit.