Query SQL Server with concatenated text from Excel

[ryanmcwh]

Hi,

I've found a module that will let me query SQL Server databases via
ODBC. Below I've created a module which does exactly what I want it to
do using concatenated cells in a worksheet. My issue however, is that
now that I've gotten the code correct, excel tells me it doesn't have
the resources necessary to execute the command. My example below
searches for 2 UPCs, in reality I'll have several hundred, possibly
thousands. If I type these values in manually they work just fine, and
I've even tried executing the code on the SQL server itself with no
luck. Is there a more efficient way to do this? I can't simply query
everything then do a lookup table because the results exceed excels
max dimensions. I'm essentially trying to see if these UPCs are in our
system.

Thanks!

<----begin code------>

Sub what()

Dim qt As QueryTable

sqlstring = "select vm.vendor, im.upc_ean, im.description,
vi.vendor_item, vi.case_pack from vendor_item vi, vendor_master vm,
item_master im where vi.record_status <> 3 and vm.record_status<3 and
im.record_status<3 and vi.item_id = im.item_id and vi.v_id =vm.v_id
and vi.vi_block_from_pos=0 and vendor='" & Sheet3.Range("A1") & "' and
upc_ean like '" & Sheet3.Range("A3") & ""

connstring = _
"ODBC;DSN=tciinstore;UID=administrator;PWD=!
password;Database=tciinstore"

With Sheet1.QueryTables.Add(Connection:=connstring,
Destination:=Range("A1"), Sql:=sqlstring)
.Refresh
End With

Sheet1.Range("A1").Select
End Sub


<----end code------>

 

[Jean-Yves]

Hi Ryan,

To start with , there is a string lenght limitation using this type of query
To overcome this limitation, the string has to be passed as an arraysee
below for microsft function
regards
JY

varSql = StringToArray(strSql)
Range("A2").Select
With Range("A2").QueryTable
.Connection = _
"OLEDB;Provider=MSDAORA.1;Password=XXX;User ID=pdmspub;Data
Source=XXXX"
.CommandType = xlCmdSql
.CommandText = varSql
.Refresh BackgroundQuery:=False
End With

End Sub


Function StringToArray(Query As String) As Variant

Const StrLen = 127 ' Set the maximum string length for
' each element in the array to return
' to 127 characters.
Dim NumElems As Integer
Dim Temp() As String
Dim i
' Divide the length of the string Query by StrLen and
' add 1 to determine how many elements the String array
' Temp should contain, and redimension the Temp array to
' contain this number of elements.

NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String

' Build the Temp array by sequentially extracting 127
' segments of the Query string into each element of the
' Temp array.

For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i

' Set the function StringToArray to the Temp array so it
' can be returned to the calling procedure.

StringToArray = Temp

End Function

 

[ryanmcwh]

Hi Ryan,

To start with , there is a string lenght limitation using this type of query
To overcome this limitation, the string has to be passed as an arraysee
below for microsft function
regards
JY

varSql = StringToArray(strSql)
Range("A2").Select
With Range("A2").QueryTable
.Connection = _
"OLEDB;Provider=MSDAORA.1;Password=XXX;User ID=pdmspub;Data
Source=XXXX"
.CommandType = xlCmdSql
.CommandText = varSql
.Refresh BackgroundQuery:=False
End With

End Sub

Function StringToArray(Query As String) As Variant

Const StrLen = 127 ' Set the maximum string length for
' each element in the array to return
' to 127 characters.
Dim NumElems As Integer
Dim Temp() As String
Dim i
' Divide the length of the string Query by StrLen and
' add 1 to determine how many elements the String array
' Temp should contain, and redimension the Temp array to
' contain this number of elements.

NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String

' Build the Temp array by sequentially extracting 127
' segments of the Query string into each element of the
' Temp array.

For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i

' Set the function StringToArray to the Temp array so it
' can be returned to the calling procedure.

StringToArray = Temp

End Function

Thanks Jean...i'm having trouble figuring out where to put everything
though. Do I combine this with my code? Thanks!

 

[Sam Wilson]

Try changing your code so that the string sqlstring contains teh following
expression: "LIKE %xxx%" rather than "LIKE 'XXX' "

Sam