query with changing parameters ...

Ok..from reading here I've discovered how to do a query using a cell value
as a parameter. (query odbc datasource).

However...I have a list of parts and I need to get the Quantity on hand for
each (and only these parts) - so the parameter is different in each
query. I figured I just put the query on each row - changing the parameter
location. Well..the legacy computer doesn't like this - I think I'm running
out of
memory or something. It gets to query 24 and gives an error. (first
[ODBCLINK Host][Allbase]ALLBASE/SQL shared memory lock allocation failed in
DBCore. (DBERR 4008)
then : [Microsoft][ODBC Driver Manager]Drivers's SQLSetConnectAttr failed)

There is a fix for the first error by changing settings im my sql database.
I was hoping that someone here could suggest something different. I just
dont believe that it's efficient to do that many separate queries.


I noticed the querytable option. I copied some code into my excel sheet
from microsoft - but it doesn't like many of the item types (i.e. Workspace)
from the example code.

Am I going in the right direction with querytable?

See response in querydao.


--
**** Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

kellie jones wrote:
> Ok..from reading here I've discovered how to do a query using a cell
> value as a parameter. (query odbc datasource).
>
> However...I have a list of parts and I need to get the Quantity on
> hand for each (and only these parts) - so the parameter is different
> in each query. I figured I just put the query on each row - changing the
> parameter location. Well..the legacy computer doesn't like this - I
> think I'm running out of
> memory or something. It gets to query 24 and gives an error. (first
> [ODBCLINK Host][Allbase]ALLBASE/SQL shared memory lock allocation
> failed in DBCore. (DBERR 4008)
> then : [Microsoft][ODBC Driver Manager]Drivers's SQLSetConnectAttr
> failed)
> There is a fix for the first error by changing settings im my sql
> database. I was hoping that someone here could suggest something
> different. I just dont believe that it's efficient to do that many
> separate queries.
>
> I noticed the querytable option. I copied some code into my excel
> sheet from microsoft - but it doesn't like many of the item types
> (i.e. Workspace) from the example code.
>
> Am I going in the right direction with querytable?