Query Hangs or Slow Performance after 2 updates

M

Malcolm

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
 
M

Mike

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

Malcolm

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

Guest

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