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------