Hi

Thanks for your response!


"Jamie Collins" wrote in message
ups.com...

Arvi Laanemets wrote:
when the query is run, the source workbook is
opened (as Read-only). And another oddity: after the query finishes,
and I
try to close the source workbook, the Excel attempts to close the
target
workbook instead

Has someone a clue, what is the reason for such behaviour, and how to
avoid
it.

Are you querying an open workbook?

No. The source workbook was closed (both source and target workbooks are on
shared network resource on server). I checked and the source workbook wasn't
in use, when the problem occurred, but we had a power breakdown tonight, and
today the query worked normally on my computer !!!.

The workbook KuuRepOleg.xls contains several queries from another (source)
workbook (GalvCalc.xls), each
one on separate sheet. All of those are single-table queries, most of them
return 1-2 columns of data (SELECT field ... , or SELECT DISTINCT field
....), 2 of them return grouped data. And on separate sheet is an union
query, which merges all data from 2 query result tables with grouped data to
single table. I have the feeling, that the problems started after the union
query was
created.

But, as I just found out, I again can't open KuuRepOleg.xls from other
computers -
whenever I try this, I'm asked to select KuuRepOleg.xls (This was the case
at start on previous week
too - and after I several times pointed to same file as source, I finally
got problems with the workbook on my own computer too). And it looks like
the union query is cause for it - I'm asked to select the source, when I try
to run the union query from any other computer. When I simply press OK
without selecting the file, or I select there the file again, an error
message "ODBC Excel Driver Login Failed" + "Unrecognized database format
´Q:\Pinnakatete_jaoskond\KuuRepOleg.xls´" is returned. When I then press OK,
the computer hangs, when I press Cancel, the query remains unrefreshed.

After such manipulations on another computer, when I try to open the
workbook, the Excel in my computer crashes (assumingly on union query) with
error message "Excel" + "This program has performed an illegal operation and
will be shut down". To make the workbook to open normally on my computer, I
have to open it without refreshing external data, and save it.

The querystring of union query is
SELECT Toot1Tbl.Kuu, Toot1Tbl.Klient, Toot1Tbl.Detail, _
Toot1Tbl.Operatsioon, Toot1Tbl.Kogus, Toot1Tbl.Pind, Toot1Tbl.Summa _
FROM `Q:\Pinnakatete_jaoskond\KuuRepOleg`.Toot1Tbl Toot1Tbl _
WHERE (Toot1Tbl.Kuu Is Not Null) _
UNION (SELECT Toot2Tbl.Kuu, Toot2Tbl.Klient, Toot2Tbl.Detail, _
Toot2Tbl.Operatsioon, Toot2Tbl.Kogus, _
Toot2Tbl.Pind, Toot2Tbl.Summa _
FROM `Q:\Pinnakatete_jaoskond\KuuRepOleg`.Toot2Tbl Toot2Tbl _
WHERE (Toot2Tbl.Kuu Is Not Null)) _
ORDER BY 1, 2, 3, 4

Toot1Tbl is fixed named range on sheet Tootmine1, with a query on it:
SELECT qtbTootmine.Kuu, qtbTootmine.Klient, qtbTootmine.Detail, _
qtbTootmine.Op1 AS 'Op', Sum(qtbTootmine.`Kogus (kokku)`) AS 'Kogus', _
Sum(qtbTootmine.`KoguPind (m2)`*100) AS 'Pind', _
SUM(qtbTootmine.`Reali-satsioon`*qtbTootmine.K1) AS 'Summa' _
FROM `Q:\Pinnakatete_jaoskond\GalvCalc`.qtbTootmine qtbTootmine _
WHERE (qtbTootmine.Op1 Is Not Null) _
GROUP BY qtbTootmine.Kuu, qtbTootmine.Klient, _
qtbTootmine.Detail, qtbTootmine.Op1

Toot2Tbl is fixed named range on sheet Tootmine2, with a query on it:
SELECT qtbTootmine.Kuu, qtbTootmine.Klient, qtbTootmine.Detail, _
qtbTootmine.Op2 AS 'Op', Sum(qtbTootmine.`Kogus (kokku)`) AS 'Kogus', _
Sum(qtbTootmine.`KoguPind (m2)`*100) AS 'Pind', _
SUM(qtbTootmine.`Reali-satsioon`*qtbTootmine.K2) AS 'Summa' _
FROM `Q:\Pinnakatete_jaoskond\GalvCalc`.qtbTootmine qtbTootmine _
WHERE (qtbTootmine.Op2 Is Not Null) _
GROUP BY qtbTootmine.Kuu, qtbTootmine.Klient, _
qtbTootmine.Detail, qtbTootmine.Op2

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


I occasionally see such behaviour
when I inadvertently query (with ADO) a workbook I have opened and
locked in another instance of Excel. Querying an open workbook is an
absolute no-no with ADO and something I still would not recommend when
using another data access tool e.g. MSQuery.

Jamie.

--