Hi

I use quite often ODBC queries to Excel files. Recently some such queries
started to behave strangely - 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 - I have to activate the target workbook, reactivate the
source workbook, and only then I can close it.

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

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

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

--

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.

--

Arvi Laanemets wrote:
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.

If I've followed correctly, you have a union query that operates on
sheets within the same workbook. I think I would have to classify that
scenario as 'querying an open workbook', although 'open' is admittedly
a very loose term in this context <g. Have you tried moving the union
query to a separate workbook to see if this resolves the problem?

Jamie.

--

Hi


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

If I've followed correctly, you have a union query that operates on
sheets within the same workbook. I think I would have to classify that
scenario as 'querying an open workbook', although 'open' is admittedly
a very loose term in this context <g. Have you tried moving the union
query to a separate workbook to see if this resolves the problem?



I'll give it a try tomorrow. And I have another option too - really I used
it before for similar tasks, but I decided for more compact solution, as
number rows in source tables isn't limited this time. I'll place both
grouped queries on same sheet, p.e. first one starting from row 2, and
second one starting p.e. from row 10000 (I have to estimate maximal possible
number of rows returned by first query jet), set in query properties that
result tables are inserted without headers, enter manually headers into row
1, and define a named range p.e. from row 1 to row 20000, which will be the
source table for final query - this time a simple one with condition in
WHERE clause to exclude empty rows.


Arvi Laanemets

Arvi Laanemets wrote:
And I have another option too -
I'll place both
grouped queries on same sheet, p.e. first one starting from row 2,
and
second one starting p.e. from row 10000 (I have to estimate maximal
possible
number of rows returned by first query jet), set in query properties
that
result tables are inserted without headers, enter manually headers
into row
1, and define a named range p.e. from row 1 to row 20000, which will
be the
source table for final query - this time a simple one with condition
in
WHERE clause to exclude empty rows.

You shouldn't need to create a defined Name. You can query the
worksheet as a table e.g.

SELECT my_col FROM [MySheet$]
WHERE my_col IS NOT NULL;

This queries the UsedRange (but at a much lower level than VBA) so will
pick up all rows on the sheet.

You can also query the sheet without headers if you put HDR=NO in the
connection string e.g.

SELECT F1 AS my_col FROM
[Excel
8.0;HDR=NO;Database=\\MyServer\MyShare\MyBook.xls;].[MySheet$]
WHERE F1 IS NOT NULL;

In lieu of explicit column headers, Jet assigns default names as F1,
F2, F3 etc.

Jamie.

--

Hi

All in vain! I put 2 queries into same sheet, and from another sheet
queried the result table to consolidate it (remove empty rows). On my
computer it works, on another asks for source again :-(((

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


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

Arvi Laanemets wrote:
And I have another option too -
I'll place both
grouped queries on same sheet, p.e. first one starting from row 2,
and
second one starting p.e. from row 10000 (I have to estimate maximal
possible
number of rows returned by first query jet), set in query properties
that
result tables are inserted without headers, enter manually headers
into row
1, and define a named range p.e. from row 1 to row 20000, which will
be the
source table for final query - this time a simple one with condition
in
WHERE clause to exclude empty rows.

You shouldn't need to create a defined Name. You can query the
worksheet as a table e.g.

SELECT my_col FROM [MySheet$]
WHERE my_col IS NOT NULL;

This queries the UsedRange (but at a much lower level than VBA) so will
pick up all rows on the sheet.

You can also query the sheet without headers if you put HDR=NO in the
connection string e.g.

SELECT F1 AS my_col FROM
[Excel
8.0;HDR=NO;Database=\\MyServer\MyShare\MyBook.xls;].[MySheet$]
WHERE F1 IS NOT NULL;

In lieu of explicit column headers, Jet assigns default names as F1,
F2, F3 etc.

Jamie.

--

Arvi Laanemets wrote:
All in vain! I put 2 queries into same sheet, and from another sheet
queried the result table to consolidate it (remove empty rows). On my
computer it works, on another asks for source again :-(((

Do you need to persist the summary table? In database terms this would
be denormalization The usual approach is to define a VIEW that
summarises the tables and query the VIEW.

VIEWs are not supported in Excel, of course. You could use the UNION
queries as a derived table in other queries, preferably with the
connection details in the query text itself e.g.

SELECT DerivedTable.key_col FROM (
SELECT CustID AS key_col, lname FROM
[Excel
8.0;Database=\\MySserver\MyShare\MyWorkbook.xls;].[Customers$]
UNION
SELECT EmpID AS key_col, lname FROM
[Excel
8.0;Database=\\MySserver\MyShare\MyWorkbook.xls;].[Employees$])
AS DerivedTable
ORDER BY DerivedTable.lname;

If you require the performance of a VIEW, or even a denormalized table,
you could consider defining it in a Jet .mdb file (a.k.a. 'Access
database' however the MS Access app is not a requirement) in the share.
You could subsequently consider moving the *data* into the database ...
<g.

Jamie.

--

Hi

Meantime I was experimenting, and as I found out, on all other computers, I
used for testing currently, even simplest ODBC query from same workbook
(manually entered 2x2 source table, and nothing more in workbook at all),
placed on computers hard disk, doesn't work properly after the workbook is
closed and reopened. In my computer, I have MS Query Add-in installed - on
other computers it isn't installed. I'll try, does installing it make any
difference

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


"Jamie Collins" wrote in message
oups.com...
Arvi Laanemets wrote:
All in vain! I put 2 queries into same sheet, and from another sheet
queried the result table to consolidate it (remove empty rows). On my
computer it works, on another asks for source again :-(((

Do you need to persist the summary table? In database terms this would
be denormalization The usual approach is to define a VIEW that
summarises the tables and query the VIEW.

VIEWs are not supported in Excel, of course. You could use the UNION
queries as a derived table in other queries, preferably with the
connection details in the query text itself e.g.

SELECT DerivedTable.key_col FROM (
SELECT CustID AS key_col, lname FROM
[Excel
8.0;Database=\\MySserver\MyShare\MyWorkbook.xls;].[Customers$]
UNION
SELECT EmpID AS key_col, lname FROM
[Excel
8.0;Database=\\MySserver\MyShare\MyWorkbook.xls;].[Employees$])
AS DerivedTable
ORDER BY DerivedTable.lname;

If you require the performance of a VIEW, or even a denormalized table,
you could consider defining it in a Jet .mdb file (a.k.a. 'Access
database' however the MS Access app is not a requirement) in the share.
You could subsequently consider moving the *data* into the database ...
<g.

Jamie.

--

Hi again

The solution of this problem is described in new thread (Excel2000 ODBC
query oddity)


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