Can’t Access Table in MS Query
I received the following error message when trying to create a new database query in Excel 2003:
Can’t access table C:\Documents and Settings\…\My Documents\Tester.mdb.qryTest.
I found a few knowledge base articles, but nothing relevant to my situation. I almost never use the wizard to create external data queries, but I thought I’d give it a shot. Using the wizard, I get this error:
Undefined function ‘Nz’ in expression.
Well that’s a little more helpful. In fact, I do use the Nz function in the query I’m trying to bring over. I changed from:
SELECT tblTest.MyID, tblTest.MyName, tblTest.MyNumber, Nz([MyNumber],100) AS Expr1 FROM tblTest;
to
SELECT tblTest.MyID, tblTest.MyName, tblTest.MyNumber, Iif(IsNull([MyNumber]),100,[MyNumber]) AS Expr1 FROM tblTest;
and it all works fine. Oh, except for this persistent little problem.
The moral is that MSQuery doesn’t like the Nz function.
Howard:
Coalesce returns the first nonnull expression among its arguments.
How about this:
COALESCE([MyNumber,100) AS Expr1
in place of
Iif(IsNull([MyNumber]),100,[MyNumber]) AS Expr1
10 January 2008, 1:42 pmHoward:
Coalesce returns the first nonnull expression among its arguments.
How about this:
COALESCE([MyNumber],100) AS Expr1
in place of
Iif(IsNull([MyNumber]),100,[MyNumber]) AS Expr1
I left the ] off the end of MyNumber the first time.
10 January 2008, 1:45 pmRob Bruce:
Isn’t NZ a native Access function? I’m not surprised that MSQuery doesn’t recognise it.
It’s for this reason of compatibility issues between different flavours of SQL that I find it a good policy to use stored procs (SQL Server and other ‘proper’ RDBMSs) or parm queries (Access) to do all of the SQL work in my applications wherever possible.
10 January 2008, 2:58 pmEric W. Bachtal:
Yep, Nz() is a method of the Access.Application object, so isn’t available on JET db connections outside of Access. I just happened to write a little about this last November, if anyone’s interested:
http://ewbi.blogs.com/develops/2007/11/why-no-nz-via-j.html
10 January 2008, 5:22 pmHoward:
Nevermind - apparently COALESCE doesn’t work from MS Query when going against an Access database. It does work from MS Query with a SQL Server database.
10 January 2008, 6:19 pmRob:
In general I’ve stopped using MS Query (in favour of OLEDB) because of the number of things it doesn’t like - union queries being one of them.
Go via the ‘Import Data’ option in Data\Import External Data and choose the JET OLEDB provider. Then you can use any Access syntax you like (at least as far as I’ve found). There’s no GUI but I don’t expect that’s a problem.
The only thing it won’t do is parameter queries so you still have to use ODBC for that.
15 January 2008, 7:50 amDick Kusleika:
Rob: That’s freaking awesome! I may never use MSQ again. Writing my own parameter code may be worth it just to use this interface.
16 January 2008, 12:58 pmRob van Gelder:
If you want a GUI for OLEDB queries, try my Query Editor tool, available on my website.
16 January 2008, 1:04 pmRob:
That was about my reaction when i discovered it! I’d spent 4 years using weird systems to get round the lack of support for union queries.
Quite why MS hide it away so much I have no idea.
If you get some example parameter code, I’d love to see it.
21 January 2008, 8:53 amStew:
I’ve just had the same sort of problem. In my case the root cause was that I created the MS Access Query logged into MS Access as myself.
When I created the query using the “admin” user, I could link the query into Excel. No idea why, but at least it works now.
S.
16 July 2008, 2:48 am