Odd behaviour of MS Query

Marc Hillman · External Since: Aug 03, 2007 Posts: 4

I have two Excel files - one we'll call "Data" and the other "UI". Data
contains large quantities of raw Data. UI contains a couple of MS Queries to
present data to the users.

When Data is not open by anyone on the network, all works perfectly fine.
Query results in UI are displayed correctly.

If Data is open by anyone on the network, UI queries cause the Data sheet to
be displayed, and the query results are wrong.

How can I make the operation of queries consistant no matter whether the
query data source is open or not ?

Leo Rod · External Since: Aug 07, 2007 Posts: 10

Marc

By any chance do you have the document named as "Data" as a shared document?
When you say here "wrong" is because it does not query or because the output
data is garbled?

Try referring the information on "Data" to an intermediate query/file that
interlaces with "UI".
Let me know how it works.

"Marc Hillman" <7owsx1j02 RemoveThis @sneakemail.com> wrote in message
news:1DE5242E-68FC-453A-A559-FADF6B361955@microsoft.com...
>I have two Excel files - one we'll call "Data" and the other "UI". Data
>contains large quantities of raw Data. UI contains a couple of MS Queries
>to present data to the users.
>
> When Data is not open by anyone on the network, all works perfectly fine.
> Query results in UI are displayed correctly.
>
> If Data is open by anyone on the network, UI queries cause the Data sheet
> to be displayed, and the query results are wrong.
>
> How can I make the operation of queries consistant no matter whether the
> query data source is open or not ?

Marc Hillman · External Since: Aug 03, 2007 Posts: 4

Not sure what you mean by "Shared" document. Multiple people can open it
simultaneously, but the problem happens when a single instance is open by
anybody, including oneself.

The data is not garbled, but just incorrect.

I have found a brutal fix. In the Calculate event method of the UI Worksheet
I check to see if a sheet called "Data" is open, and if so I close it. This
fixes the problem, but isn't terribly elegant. Still looking for the
'correct' solution.

"Leo Rod" <leo@rod_com> wrote in message
news:OkkNzDT2HHA.3760@TK2MSFTNGP03.phx.gbl...
> Marc
>
> By any chance do you have the document named as "Data" as a shared
> document?
> When you say here "wrong" is because it does not query or because the
> output data is garbled?
>
> Try referring the information on "Data" to an intermediate query/file that
> interlaces with "UI".
> Let me know how it works.
>
> "Marc Hillman" <7owsx1j02.DeleteThis@sneakemail.com> wrote in message
> news:1DE5242E-68FC-453A-A559-FADF6B361955@microsoft.com...
>>I have two Excel files - one we'll call "Data" and the other "UI". Data
>>contains large quantities of raw Data. UI contains a couple of MS Queries
>>to present data to the users.
>>
>> When Data is not open by anyone on the network, all works perfectly fine.
>> Query results in UI are displayed correctly.
>>
>> If Data is open by anyone on the network, UI queries cause the Data sheet
>> to be displayed, and the query results are wrong.
>>
>> How can I make the operation of queries consistant no matter whether the
>> query data source is open or not ?
>
>

Leo Rod · External Since: Aug 07, 2007 Posts: 10

Marc,

I believe the file is modified while you run the query and of course is not
identical when retrieving and matching data.
Try modifying the settings of the excel shared file "Data" to be saved it in
shorter time periods (tools /shared workbook / advanced / automatically save
every "x" minutes) or the calculated event on UI to save this document
before running the query instead of closing it.
Together with this have the file "Data" save a backup copy by default
everytime is saved (file / save as / tools / general options / create
backup) and reroute the query to query this backup data.

Let me know if it works.

"Marc Hillman" <7owsx1j02.DeleteThis@sneakemail.com> wrote in message
news:6B466738-DABC-44D0-AD1E-0398C16731BA@microsoft.com...
> Not sure what you mean by "Shared" document. Multiple people can open it
> simultaneously, but the problem happens when a single instance is open by
> anybody, including oneself.
>
> The data is not garbled, but just incorrect.
>
> I have found a brutal fix. In the Calculate event method of the UI
> Worksheet
> I check to see if a sheet called "Data" is open, and if so I close it.
> This
> fixes the problem, but isn't terribly elegant. Still looking for the
> 'correct' solution.
>
> "Leo Rod" <leo@rod_com> wrote in message
> news:OkkNzDT2HHA.3760@TK2MSFTNGP03.phx.gbl...
>> Marc
>>
>> By any chance do you have the document named as "Data" as a shared
>> document?
>> When you say here "wrong" is because it does not query or because the
>> output data is garbled?
>>
>> Try referring the information on "Data" to an intermediate query/file
>> that interlaces with "UI".
>> Let me know how it works.
>>
>> "Marc Hillman" <7owsx1j02.DeleteThis@sneakemail.com> wrote in message
>> news:1DE5242E-68FC-453A-A559-FADF6B361955@microsoft.com...
>>>I have two Excel files - one we'll call "Data" and the other "UI". Data
>>>contains large quantities of raw Data. UI contains a couple of MS Queries
>>>to present data to the users.
>>>
>>> When Data is not open by anyone on the network, all works perfectly
>>> fine. Query results in UI are displayed correctly.
>>>
>>> If Data is open by anyone on the network, UI queries cause the Data
>>> sheet to be displayed, and the query results are wrong.
>>>
>>> How can I make the operation of queries consistant no matter whether the
>>> query data source is open or not ?
>>
>>
>