Win98SE, Excel2000: Newly created ODBC from Excel tables work only, when source workbook is opened !!!

Hi

During last days I encoutered a problem. When trying to create an ODBC query
from Excel tables, ODBC don't see any named ranges in source workbook, when
the source workbook is closed. When I open the source workbook, named ranges
in source workbook are visible and I can create the query. But when I close
after that the source workbook, and try to refresh the query, the error
message is displayed:
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not
find the object "MyNamedRange". Make sure the object exists and that you
spell its name and the path correctly."

All queries created earlier are working normally. The source workbook I was
working with, is situated on shared network resource which is mapped to my
computer, but the ODBC behaviour remained same, when I copied it to my
computer and tried to access it there - so this is not signifant.

From MS KB I didn't fint anything applicable.


Hope for some advice here. Thanks in advance!

--
Arvi Laanemets
(When sending e-mail, use address arvilAttarkon.ee)

"Arvi Laanemets" wrote ...

During last days I encoutered a problem. When trying to create an ODBC query
from Excel tables, ODBC don't see any named ranges in source workbook, when
the source workbook is closed. When I open the source workbook, named ranges
in source workbook are visible and I can create the query. But when I close
after that the source workbook, and try to refresh the query, the error
message is displayed:
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not
find the object "MyNamedRange". Make sure the object exists and that you
spell its name and the path correctly."

All queries created earlier are working normally. The source workbook I was
working with, is situated on shared network resource which is mapped to my
computer, but the ODBC behaviour remained same, when I copied it to my
computer and tried to access it there - so this is not signifant.

From MS KB I didn't fint anything applicable.

I don't use ODBC much so I can only make suggestions (I was going to
say I've never had this error but I turned up this unrelated thread:

http://groups.google.com/groups?thre...ing.google.com

Don't you hate it when your search turns up something you've posted
yourself and it doesn't help ?!)

I suspect it has something to do with the DSN and/or connection string
and/or data source and path used in the query text. For example, have
you changed the path in the query but not in the connection string
(only possible via VBA?) e.g.

http://www.dicks-clicks.com/excel/Ex...htm#ChangeConn

Another thing to consider is have you got the latest MS Jet Service
Pack i.e. download from MSDN:

http://support.microsoft.com/default...b;en-us;239114

Speaking of MSDN, I trust you saw those articles about Excel 4.0
files, defined Names with names longer then 64 characters, etc?

Jamie.

--