SQL subquery causing odbc connection failure

[bobmac-]

Hi
The following SQL results in a quick result;

SELECT WRHUB_PROD_V_SRW_MID_PARCEL_SP.SERVICE_POINT_DESC
FROM WRHUB_PROD_V_SRW_MID_PARCEL_SP
WHERE WRHUB_PROD_V_SRW_MID_PARCEL_SP.SERVICE_POINT_DESC IN ("MC.4033", "MC.678");

However, if I use a subquery as follows;

SELECT WRHUB_PROD_V_SRW_MID_PARCEL_SP.SERVICE_POINT_DESC
FROM WRHUB_PROD_V_SRW_MID_PARCEL_SP
WHERE WRHUB_PROD_V_SRW_MID_PARCEL_SP.SERVICE_POINT_DESC IN
(SELECT meter FROM tblMeter);

It seems to time out giving me an ODBC call failure message.

The first table is actually a linked view into a SQLSERVER database and is very large.

Why does ODBC seem to time out when using the subquery?

Cheers

 

[boblarson]

Is the field in the subquery set as TEXT in the SQL Server table? I had the same issue a while ago and found that changing the field's datatype from TEXT to VARCHAR(x) where x is whatever size you want - we chose VARCHAR(500) and then the problem went away.

 

[namliam]

More likely the tblMeter is a local table inside access...

The "stand alone" query returns the first couple (hundred?) of records from the actual table where as the second query with the subselect has to pull the full table before doing the IN construct in your PC memory.

Doing a construct like this with a table in SQL Server and Access is NOT recomended and will be horribly slow (if it doesnt crash due to extending over the ODBC time out).
You have 2 options
1)
Construct the IN like you have in the first query from code and push the first query (constructed in code) into the SQL Server.

2)
Upload your data in the tblMeter into the SQL Server, then do NOT use the in construct... but rather do a 'proper' join

Both will work though both depend on your personal environment and the possibilities within that environment.

 

[bobmac-]

Thanks to both Bob & Namliam.

I don't really have access to the SQL Server so I'll try the first of Namliam's options.

I'll let it be known how I fair shortly.

Cheers
Bob

 

[bobmac-]

Hi Thanks for the info
I've created a loop in vba for each service_point_desc. I'll then insert the result into a temporay data table. I will then simply AddItem from the the temporay data table into the list box. All good

I thank you all
Cheers
bob