This is my first time asking a question in forum so be gentle.
As background, the sql (admittedly un-elegant) query vba code below has been used for months and previously tested to work. Suddenly it doesn't and throws the error noted in the title to this question when VBA execution hits the sql query line of code.
Troubleshooting reveals that the length of the character string stored in variable 'CriteriaString' seems to be what causes the error. Seems like if the length of the string stored in 'CriteriaString', gets over about 150, I get the noted error.
Code Overview: This code pulls data from an inventory server database. You will note a userform is presented where the user selects specific values to be searched for in up to three different fields. The user form contains check boxes so multiple values can be selected in any of the three fields. The values in those fields are returned from the userform and captured in variables named: 'SelectedCatCode', 'SelectedVendorName' and 'SelectedYear'. After checking to see if no values have been selected in a given field, I construct the WHERE statement string in variable 'CriteriaString' and then form up the sql query.
Here are samples of the contents of 'CriteriaString' when the code runs and when it doesn't.
- When 'CriteriaString' contains either of the following, the code works:
'CriteriaString' = USER_VI_BM_AVAIL_STOCK_STAT.CATEG_COD IN ('1') AND USER_VI_BM_AVAIL_STOCK_STAT.ATTRIBUTE_CODE_1 IN ('2014')
'CriteriaString' = USER_VI_BM_AVAIL_STOCK_STAT.VEND_NAM IN ('ATOMIC SKI USA INC.') AND USER_VI_BM_AVAIL_STOCK_STAT.ATTRIBUTE_CODE_1 IN ('2014')
- when 'CriteriaString' contains the following, I get the Type 13 error
'CriteriaString' = USER_VI_BM_AVAIL_STOCK_STAT.CATEG_COD IN ('1') AND USER_VI_BM_AVAIL_STOCK_STAT.VEND_NAM IN ('ATOMIC SKI USA INC.') AND USER_VI_BM_AVAIL_STOCK_STAT.ATTRIBUTE_CODE_1 IN ('2014')
You can see from above, any two of the three fields seem to work fine. But when the string length gets too long, above about 150, I get the error.
Any clue what's causing the error to be thrown when it hits the sql query line of code or how I can better trouble shoot it? From reading, it sounds like these Error 13's can be a bit mysterious. Many thanks for the help in advance!
' Import_Text Macro
' Nov-2011 - Made updates to interface directly with CP Database via ODBC connect - JJR
'
'Filename Selection algorithm from Sell Thru Report File
Dim WBName As String 'Set variable for Template Workbook filename
Dim Workbook_Name As String
Dim WSName As String
Dim fName As String
Dim sFile, i As Integer
Dim Start_Time As Single
Dim SelectedCatCode As String
Dim SelectedVendorName As String
Dim SelectedOperator As String
Dim SelectedYear As String
Dim LastRowColA As Long
Dim CatCodCrit As String
Dim VendNameCrit As String
Dim YearCrit As String
Dim CriteriaString As String
Dim NumChar As Integer
'Save Start time
Start_Time = Timer
'Clear Data Tab of content and formats
Sheets("Data").Select
Cells.Select
Selection.ClearContents 'Clear Data from tab
Selection.Clear 'Clear all formats
Cells.Select 'Clear all data validation
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'Query User for Category Code, Vendor Name and year to Download from CP
TryAgain:
UserForm1.Show 'Open user form to capture Category Code to download
If Worksheets("Lookups").Cells(6, 18).Value = "True" Then 'If bln.Cancel set true, then Cancel button selected
Unload UserForm1
Sheets("Cntrl").Select
End
End If
'Capture inputs from Lookups Tab
SelectedCatCode = Worksheets("Lookups").Cells(2, 18).Value 'Capture selected catccodes from the global variable named CatCode (see module 11)
SelectedVendorName = Worksheets("Lookups").Cells(3, 18).Value 'Capture selected Vendor Names from the global variable named Selected_VendorName (see module 11)
SelectedYear = Worksheets("Lookups").Cells(5, 18).Value 'Capture selected Years from the global variable named Selected_Year (see module 11)
'Look for combination where all fields have NONE checked
If SelectedCatCode = "MT" And SelectedVendorName = "MT" And SelectedYear = "MT" Then
MsgBox ("Looks like you haven't selected any value in either the CatCode, Vendor Name or Year list boxes. Please select at least one value in one of the list boxes to query. Please try again.")
GoTo TryAgain
End If
'Verify the Query with User
Response = MsgBox("Your query is summarized below (where MT means empty):" & Chr(13) & Chr(10) & Chr(10) _
& "CatCodes Selected are: " & SelectedCatCode & Chr(13) & Chr(10) & Chr(10) _
& "Vendor Names Selected are: " & SelectedVendorName & Chr(13) & Chr(10) & Chr(10) _
& "Years Selected are: " & SelectedYear & Chr(13) & Chr(10) & Chr(10) _
& "Complex queries can take longer to execute and produce large volumes of data." & Chr(13) & Chr(10) & Chr(10) _
& "Are you ready to proceed?" & Chr(13) & Chr(10) & Chr(10) _
& "Click YES to proceed or NO to redefine your query. Click CANCEL to Abort.", vbYesNoCancel, "Validate Query")
If Response = vbNo Then GoTo TryAgain ' User selected No; start query over
If Response = vbCancel Then
Sheets("Cntrl").Select 'Go to Control tab
Range("A1").Select
Exit Sub ' User selected Cancel; end macro
End If
If SelectedCatCode = "MT" Then 'SelectedCatCode is empty, then make CatCodCrit null string ""
CatCodCrit = ""
Else 'Else, build criteria string
'CatCodCrit = "(USER_VI_BM_AVAIL_STOCK_STAT.CATEG_COD IN (" & SelectedCatCode & "))" & " AND " & ""
CatCodCrit = "USER_VI_BM_AVAIL_STOCK_STAT.CATEG_COD IN (" & SelectedCatCode & ")" & " AND " & ""
End If
If SelectedVendorName = "MT" Then 'SelectedVendorName is empty, then make LocIDCrit null string ""
VendNameCrit = ""
Else 'Else, build criteria string
'VendNameCrit = "(USER_VI_BM_AVAIL_STOCK_STAT.VEND_NAM IN (" & SelectedVendorName & "))" & " AND " & ""
VendNameCrit = "USER_VI_BM_AVAIL_STOCK_STAT.VEND_NAM IN (" & SelectedVendorName & ")" & " AND " & ""
End If
If SelectedYear = "MT" Then 'SelectedYear is empty, then make LocIDCrit null string ""
YearCrit = ""
Else 'Else, build criteria string
'YearCrit = "(USER_VI_BM_AVAIL_STOCK_STAT.ATTRIBUTE_CODE_1 IN (" & SelectedYear & "))" & " AND " & ""
YearCrit = "USER_VI_BM_AVAIL_STOCK_STAT.ATTRIBUTE_CODE_1 IN (" & SelectedYear & ")" & " AND " & ""
End If
'Build Criteria String
CriteriaString = CatCodCrit & VendNameCrit & YearCrit 'Now build creteria statement for use in WHERE statement of INVENTORY sql query
CriteriaString = Left(CriteriaString, Len(CriteriaString) - 5) 'remove last 5 chars. Should be "_AND_"
NumChar = Len(CriteriaString)
Worksheets("Lookups").Cells(10, 18).Value = CriteriaString
'Name of view queried for transfer data is: USER_VI_BM_AVAIL_STOCK_STAT
Sheets("Data").Select 'Go to Data tab to conduct query
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DRIVER=SQL Server;SERVER=BUCKMANSSQL;UID=YY;PWD=XXX;APP=Microsoft Office 2010;WSID=CORP207;DATABASE=BUCKMANS" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT USER_VI_BM_AVAIL_STOCK_STAT.CATEG_COD, USER_VI_BM_AVAIL_STOCK_STAT.LOC_ID, " _
, _
"USER_VI_BM_AVAIL_STOCK_STAT.ITEM_NO, USER_VI_BM_AVAIL_STOCK_STAT.VEND_NAM, " _
, _
"USER_VI_BM_AVAIL_STOCK_STAT.VEND_ITEM_NO, USER_VI_BM_AVAIL_STOCK_STAT.DESCR, " _
, _
"USER_VI_BM_AVAIL_STOCK_STAT.DIM_1_UPR, USER_VI_BM_AVAIL_STOCK_STAT.DIM_2_UPR, " _
, _
"USER_VI_BM_AVAIL_STOCK_STAT.DIM_3_UPR, USER_VI_BM_AVAIL_STOCK_STAT.QTY_AVAIL, " _
, _
"USER_VI_BM_AVAIL_STOCK_STAT.QTY_ON_PO, USER_VI_BM_AVAIL_STOCK_STAT.YTD_SALES, " _
, _
"USER_VI_BM_AVAIL_STOCK_STAT.AVG_COST, USER_VI_BM_AVAIL_STOCK_STAT.ATTRIBUTE_CODE_1, " _
, _
"USER_VI_BM_AVAIL_STOCK_STAT.LST_COST, USER_VI_BM_AVAIL_STOCK_STAT.PRC_1" & Chr(13) & "" & Chr(10) & _
"FROM BUCKMANS.dbo.USER_VI_BM_AVAIL_STOCK_STAT USER_VI_BM_AVAIL_STOCK_STAT" & Chr(13) & "" & Chr(10) & "" _
, _
"WHERE " & CriteriaString & "" & _
"ORDER BY USER_VI_BM_AVAIL_STOCK_STAT.CATEG_COD, USER_VI_BM_AVAIL_STOCK_STAT" _
, _
".ITEM_NO, USER_VI_BM_AVAIL_STOCK_STAT.LOC_ID")
.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_from_CounterPoint"
.Refresh BackgroundQuery:=False
End With
'Write DB Query Criteria to Control Tab
Worksheets("Cntrl").Range("C8").Value = SelectedCatCode
Worksheets("Cntrl").Range("C9").Value = SelectedVendorName
Worksheets("Cntrl").Range("C10").Value = SelectedYear
'Undo table formating and clear all formats
ActiveSheet.ListObjects( _
"Table_Query_from_CounterPoint").Unlist
Selection.ClearFormats
'Find number of rows on Data tab
Sheets("Data").Select 'Clear data on Control tab
LastRowColA = Range("A400000").End(xlUp).Row 'Find last row of data and load into LastRowColA
Worksheets("Cntrl").Range("A7").Value = LastRowColA - 1 & " Records"
'Insert query definitions as comments in header fields: Catcode, Vendors, Year
Sheets("Data").Select 'Goto data tab
Range("A1").Select 'select cell A1, Catcode header
ActiveCell.AddComment ("Selected CatCodes are: " & SelectedCatCode)
Range("N1").Value = "YEAR"
Range("N1").Select 'Select Year field Header
ActiveCell.AddComment ("Selected Years include: " & SelectedYear)
Range("D1").Select 'select cell A1, Catcode header
ActiveCell.AddComment ("Selected Vendor Name are: " & SelectedVendorName)
'Update Control tab with data
Sheets("Cntrl").Select 'Clear data on Control tab
Range("F2").Value = Now() 'Date
Range("F3").Value = Now() 'Time
Range("F4").Value = Timer - Start_Time 'Processing time
Range("B8:C10").Select
With Selection.Font
.Name = "Arial Narrow"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
'Put up Message Box to save file
'Response = MsgBox("Data has been downloaded from the CounterPoint database and placed on tab named DATA. There are: " & LastRowColA & "rows on the data tab. If you are ready to process this data, click the Prep Data button.", 0)
Response = MsgBox("Data has been downloaded from the CounterPoint Database" & Chr(13) & Chr(10) _
& "and placed on the tab named DATA. The query downloaded: " & Chr(13) & Chr(10) & Chr(10) _
& " " & LastRowColA - 1 & " rows." & Chr(13) & Chr(10) & Chr(10) _
& "If you are ready to process this data, click OK and " & Chr(13) & Chr(10) _
& "then click the Prep Data button." & Chr(13) & Chr(10) & Chr(10) _
& "If more than 60,000 rows have been downloaded, consider" & Chr(13) & Chr(10) _
& "re-defining your Query. ", vbOKOnly, "Summarize Query")
End SubThanks for adding the code tags pike. Thought I did it correctly but obviously not.