Can't open query in SQL View to correct query ("Join expression not supported")

[Mike Bridge]

I am using MS Access 2003, and I typed in a query in SQL view which was working fine. Now when I open it, it gives me
the error "Join expression not supported". Apparently, Access "fixed" it for me.

Unfortunately, I can't correct it because when I click "OK", the window disappears. I don't see any way to open the
query directly in SQL view, and both the Design and Data views exit as soon as I close the error dialog.

Is there any way around this?

Thanks,

-Mike

[Mike Bridge]

Ok, my solution is to stop using Access and find some better software. I find the following unbelievable:

- I can drag the problem 'Query' from Access into Excel, and the result is fine, meaning my SQL was working fine.
- I still can't open the query in Access in Design or Data view (or SQL view, since the interface doesn't seem to
allow it). Is there a different database engine for dragging-and-dropping???
- Access has since decided to delete the contents my original query (!). I tried reopening it and it tells me that
no fields are defined. Fortunately, I copied it before Access lost the contents of it. And it deleted it multiple
times from multiple copies!!.

What wonderful software Microsoft writes.

-Mike

On Mon, 27 Jun 2005 18:08:41 GMT, Mike Bridge <mi**@bridgecanada.com> wrote:

I am using MS Access 2003, and I typed in a query in SQL view which was working fine. Now when I open it, it gives me
the error "Join expression not supported". Apparently, Access "fixed" it for me.

Unfortunately, I can't correct it because when I click "OK", the window disappears. I don't see any way to open the
query directly in SQL view, and both the Design and Data views exit as soon as I close the error dialog.

Is there any way around this?

Thanks,

-Mike

[Mike Bridge]

Hi-

Sorry, one more observation. I can run a second query on the original (still-)unopenable query and I get all my data
back without any problem.

Incredible.

-Mike

On Mon, 27 Jun 2005 18:39:38 GMT, Mike Bridge <mi**@bridgecanada.com> wrote:

Ok, my solution is to stop using Access and find some better software. I find the following unbelievable:

- I can drag the problem 'Query' from Access into Excel, and the result is fine, meaning my SQL was working fine.
- I still can't open the query in Access in Design or Data view (or SQL view, since the interface doesn't seem to
allow it). Is there a different database engine for dragging-and-dropping???
- Access has since decided to delete the contents my original query (!). I tried reopening it and it tells me that
no fields are defined. Fortunately, I copied it before Access lost the contents of it. And it deleted it multiple
times from multiple copies!!.

What wonderful software Microsoft writes.

-Mike

On Mon, 27 Jun 2005 18:08:41 GMT, Mike Bridge <mi**@bridgecanada.com> wrote:

I am using MS Access 2003, and I typed in a query in SQL view which was working fine. Now when I open it, it gives me
the error "Join expression not supported". Apparently, Access "fixed" it for me.

Unfortunately, I can't correct it because when I click "OK", the window disappears. I don't see any way to open the
query directly in SQL view, and both the Design and Data views exit as soon as I close the error dialog.

Is there any way around this?

Thanks,

-Mike

[Bas Cost Budde]

You may find my NavQueries useful; it sits on my site. I wrote it
exactly for this complaint.

Using "better" software can be a good solution. I still use A97 because
I have not been convinced till now that later versions are more stable.
You can consider using 97 as well. And then, there are known problems
with the database engine, and there is the dialect of SQL, the lack of
triggers.

For my serious business applications I use DB/2 anyway.

It must be said that creating reports has been constructed quite well.

Mike Bridge wrote:

Ok, my solution is to stop using Access and find some better software. I find the following unbelievable: What wonderful software Microsoft writes.

It's easy to blame such a big company. Must be difficult for them to be
really innovative now.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[David W. Fenton]

Mike Bridge <mi**@bridgecanada.com> wrote in
news:st********************************@4ax.com:

I am using MS Access 2003, and I typed in a query in SQL view
which was working fine. Now when I open it, it gives me the error
"Join expression not supported". Apparently, Access "fixed" it
for me.

Unfortunately, I can't correct it because when I click "OK", the
window disappears. I don't see any way to open the query directly
in SQL view, and both the Design and Data views exit as soon as I
close the error dialog.

Is there any way around this?

Compact the database in which the problematic query exists, then
open it. This will delete the query compilation, which is probably
the cause of the problem.

It's something that often happens when you start a query, save it
before finishing it, then make a lot of changes with multiple saves.
Each save compiles the query, but sometimes it gets confused.

If none of that works, you could also try copying the query and
pasting it under a new name to see if that makes it editable. This
new query won't have an compilation at all, so if corruption of the
compilation is the problem, it should work.

Another thing to do is to use DAO to read the QueryDef's SQL
property. In the Debug window, type this (replacing "ProblemQuery"
with the actual name of your defective query):

Debug.Print CurrentDB().QueryDefs("ProblemQuery").SQL

You can then copy that SQL into a new query.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Mike Bridge]

Hi-

Thanks for the tips. Retrieving the query showed that Access had arbitrarily "fixed" my query by removing some
parentheses, and in doing so it rendered the query invalid and unopenable. Who knows why it deleted the query contents
(thankfully, before I backed it up)---maybe it was embarrassed at having screwed up my SQL so badly.

-Mike



On Mon, 27 Jun 2005 23:24:24 GMT, "David W. Fenton" <dX********@bway.net.invalid> wrote:

Another thing to do is to use DAO to read the QueryDef's SQL
property. In the Debug window, type this (replacing "ProblemQuery"
with the actual name of your defective query):

Debug.Print CurrentDB().QueryDefs("ProblemQuery").SQL

You can then copy that SQL into a new query.

[David W. Fenton]

Mike Bridge <mi**@bridgecanada.com> wrote in
news:0b********************************@4ax.com:

Thanks for the tips. Retrieving the query showed that Access had
arbitrarily "fixed" my query by removing some parentheses, and in
doing so it rendered the query invalid and unopenable. Who knows
why it deleted the query contents (thankfully, before I backed it
up)---maybe it was embarrassed at having screwed up my SQL so
badly.

Well, maybe you placed your parentheses in a fashion that was
ambiguous, or unsupported in Jet SQL.

Can you design the same query with the QBE grid and get it to work
correctly? I know, when you know SQL well, it seems a pain to point
and click, but sometimes SQL that works in other dbs doesn't work
unchanged in Access. That works both ways, of course,

I have found that Access can mis-guess about parenthese unless you
overspecify, putting in all non-necessary ones, just like the QBE
designer does by default. Because of that, I basically use the QBE
to write all SQL that it can write, and dip into the SQL view only
to change what can't be changed in the graphical UI.

I've had very little in the way of problems approaching it that way.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Mike Bridge]

Hi-

The odd thing is that the query works with the jet engine; Access is just too stupid to parse it---and too stupid not to
realize that it shouldn't rewrite things it can't parse.

I'm doing a left join with an "AND" in the "ON" statement:

SELECT ... FROM a LEFT JOIN b ON (a.id=b.id AND b.somethingelse=a.somethingelse)

....or something to that effect. Access decided that the parentheses were superfluous and removed them.

It doesn't bother me that Access didn't understand the query---I could always write it a different way. What does
bother me is that Microsoft's basic design is so poor: first off, Access rewrites queries it doesn't understand, and
second, it prevents the user from viewing or fixing a query that it can't understand but is working fine (annoyingly,
the results show up behind the error message that says that the query is invalid!). And on top of that, it eventually
just deletes the contents of queries that can't be opened.

I figure that if a product has been around for over a decade and making [m/b]illions in profits, these basic design
flaws should be long gone.

-Mike

On Tue, 28 Jun 2005 01:21:33 GMT, "David W. Fenton" <dX********@bway.net.invalid> wrote:

Mike Bridge <mi**@bridgecanada.com> wrote in
news:0b********************************@4ax.com :

Thanks for the tips. Retrieving the query showed that Access had
arbitrarily "fixed" my query by removing some parentheses, and in
doing so it rendered the query invalid and unopenable. Who knows
why it deleted the query contents (thankfully, before I backed it
up)---maybe it was embarrassed at having screwed up my SQL so
badly.

Well, maybe you placed your parentheses in a fashion that was
ambiguous, or unsupported in Jet SQL.

Can you design the same query with the QBE grid and get it to work
correctly? I know, when you know SQL well, it seems a pain to point
and click, but sometimes SQL that works in other dbs doesn't work
unchanged in Access. That works both ways, of course,

I have found that Access can mis-guess about parenthese unless you
overspecify, putting in all non-necessary ones, just like the QBE
designer does by default. Because of that, I basically use the QBE
to write all SQL that it can write, and dip into the SQL view only
to change what can't be changed in the graphical UI.

I've had very little in the way of problems approaching it that way.

[Bri]

I've noticed that Access will try to figure out the SQL and modify it to
work in the QBE grid if you try to go into the QBE view if it. I have
NEVER had it try to modify the SQL if I go into Design SQL view, type in
the SQL and then SAVE without hitting Design view. This has always
worked for me. The only time Access tried to rewrite the SQL is when it
modifies it for the QBE. Going back to Design of these queries geos
straight into the SQL view. This is also the way it works for
Passthrough queries. No QBE = No Access rewrite.

--
Bri

[David W. Fenton]

Mike Bridge <mi**@bridgecanada.com> wrote in
news:1f********************************@4ax.com:

I figure that if a product has been around for over a decade and
making [m/b]illions in profits, these basic design flaws should be
long gone.

Well, then, don't use Access.

Either that, or just deal with the very small number of problems
involved.

As I said, I've never encountered what you describe, but I usually
design in the QBE and dip into the SQL view only to write SQL that
can't be written from the QBE. Of course I can write SQL by hand (I
do it all the time in VBA code), but I don't see any reason to do so
when the QBE allows me to do it.

I don't really care *how* Access thinks the results should be
returned. That is, if the QBE changes my SQL, but the result is
precisely the same, why should I give a rat's ass?

Last of all, if you've ever contemplated writing a UI to allow
someone to construct SQL statements on the fly, you'd realize that
the Access QBE is pretty much a work of genius. It's amazing how
well it handles everything.

You sound to me like someone who is hostile to a graphical UI, and
that's how you got into trouble. I've never seen SQL view as
anything but a behind-the-scenes view as a supplement to the QBE
view. You seem to be treating it as the principle UI for building
queries. I suspect that my view of it is probably closer to the view
behind the design of it, and that may be why you've encountered
problems but I never have, in nearly 10 years of near-daily use of
Access.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc