Hello,

I have an MS Query that will hang reading data after
performing the following steps 2 times:

1. Changed the CommandText statement to retrieve new data
2. Called the Refresh Method

Sometimes the data will read if I wait for a few minutes,
but other times Excel seems to have locked up entirely.
This only happens after I have performed the above steps
twice. However, if I close the workbook (the query has
been saved and is being reused), the query works when I
reopen it.

I have tried different data sources (created a new Access
DB and used that), but it still gives the same results.
The only thing I have noticed is that their is a join in
the query. If I am querying from only 1 table, then
there isn't any problem.

Is there some way to reset the connection/recordset or
whatever it is that Excel does when it closes the file?

Thanks,

Malcolm

Hard to say without seeing code but sounds like it might
be a "cleanup" issue. Open your connection, command, and
recordset, do what you have to do, close the connection
and recordset, and then set all of them to Nothing.

I'd agree that it might be a cleanup issue. However, I
am using MS Query (Get External Data) in Excel, so it
does not seem I have access to those objects.

Here is my code to generate the query:

*** Begin Code ***

strBCQ2 = "SELECT PS_BC.Parent_Part,
PS_BC.Component_Part, PS_BC.Qpa, IPL_BC.Qty_On_Hand,
IPL_BC.Bin_Code FROM PS_BC INNER JOIN IPL_BC ON
PS_BC.Component_Part = IPL_BC.ID WHERE
((PS_BC.Parent_Part='" & PartNum(0) & "') OR
(PS_BC.Parent_Part='" & PartNum(1) & "')) AND
(IPL_BC.Bin_Code Is Null OR IPL_BC.Bin_Code<'B')"

Sheets("wsQUERY").QueryTables("BCQ2").CommandText =
strBCQ2

Sheets("wsQUERY").QueryTables("BCQ2").Refresh

*** End Code ***

The "PartNum" array was used to input 2 part numbers from
the user. Like I said before, I can only do this twice
before it will lock up. I have noticed that if I pull
data from only one table (and hence, there is no JOIN)
then it will run fine as many times as I desire.


-----Original Message-----
Hard to say without seeing code but sounds like it might
be a "cleanup" issue. Open your connection, command,
and
recordset, do what you have to do, close the connection
and recordset, and then set all of them to Nothing.

.

Make sure your join and filtering fields are properly
indexed (i.e. - IPL_BC.ID, PS_BC.Parent_Part, and
IPL_BC.Bin_Code).