Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query SQL Server with concatenated text from Excel
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------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query SQL Server with concatenated text from Excel
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 wrote in message ... 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------ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query SQL Server with concatenated text from Excel
On Jan 22, 4:48 am, "Jean-Yves" wrote:
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 wrote in message ... 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------ Thanks Jean...i'm having trouble figuring out where to put everything though. Do I combine this with my code? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query SQL Server with concatenated text from Excel
Try changing your code so that the string sqlstring contains teh following
expression: "LIKE %xxx%" rather than "LIKE 'XXX' " Sam " wrote: 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------ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel with SQL Query (*.sql) and SQL Server | Excel Discussion (Misc queries) | |||
Excel:Get concatenated text to be recognised as formula not text? | Excel Discussion (Misc queries) | |||
Query SQL Server from Excel | Excel Worksheet Functions | |||
Query SQL Server from Excel | Excel Programming | |||
How do I convert a text array to a concatenated text cell? Excel. | Excel Worksheet Functions |