Web Queries

[mark]

Hi.

I'm using a Microsoft Web Query to access some data that is being retrieved
by Oracle's Discoverer Web version.

Sometimes, when I change the query's parameter, it flashes the data up
almost instantaneously. Other times, it drags on for well more than a minute.

Working with it here this evening, a couple of times, it has flashed through
the 10 data retrievals I'm using in about 10 seconds. Other times, it takes
it more than 10 minutes. Yet other times, it quits processing and brings up
that Continue End Debug Cancel dialog box.

I've thought of the possibility of these queries getting behind other
database jobs. Perhaps that's all it is.

Can anyone offer any other ideas? When I open the file, it asks if I want
to enable or disable automatic refresh... for a bit, I thought maybe the
difference was in my answer to that, but now it doesn't seem so.

It's the same code. But vastly different return time.

Code below for reference.

Thanks.
Mark

'******************

Sub main()

'dimension variables

Dim rgList As Range
Dim rgItem As Range
Dim rgDest As Range
Dim qryVendHist As QueryTable
Dim i As Integer


'assign variables

Set rgList = Sheets("List").Range("a1")
Set rgItem = Sheets("Item").Range("b1")
Set rgDest = Sheets("List").Range("c1")
Set qryVendHist = Sheets("Query").QueryTables(1)
i = 1


'clear query data, put in new part, refresh
qryVendHist.BackgroundQuery = False

While rgList.Offset(i, 0).Value <> ""

Sheets("Query").Cells.Clear
rgItem.Value = rgList.Offset(i, 0).Value
qryVendHist.Refresh
rgDest.Offset(i, 0).Value = Sheets("Query").Range("b2").Value
rgDest.Offset(i, 1).Value = Sheets("Query").Range("c2").Value
i = i + 1

Wend

End Sub

 

[mark]

and sometimes, at the qryVendHist.refresh command, I get the dialog box that
starts out:

"Run-time error '1004':

The file could not be accessed. Try one of the following:

Make sure the specified folder exists.
Make sure the folder that contains the file is not read-only.
Make sure the file name does not contain any of the following
characters: < > ? [ } : | or *
Make fure the file/ath name doesn't contain more than 218
**********

I don't know what possible sense that makes in this context, but if I click
Debug, and then tell it to continue, it usually finishes up fine.

I tried putting in a DoEvents before and after the qryVendHist.refresh ,
but it's still doing it.