473,326 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
Nov 13 '05 #1
9 18471
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


Nov 13 '05 #2
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


Nov 13 '05 #3
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

Nov 13 '05 #4
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
Nov 13 '05 #5
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.


Nov 13 '05 #6
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
Nov 13 '05 #7
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.


Nov 13 '05 #8
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

Nov 13 '05 #9
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
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: David Konerding | last post by:
Hello, I have written an app which opens a TCP connection to a server and uses a protocol to communicate with it. Specifically, I've written a python IMD client for the molecular dynamics...
4
by: sci | last post by:
Could someone help me by answering the questions below? What's a cursor? What's difference between Query and View? Is a RecordSet just part of a table? Can it be part of a query of view? If...
7
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
12
by: Phil Powell | last post by:
<cfquery name="getAll" datasource="#request.dsn#"> SELECT U.userID, U.fname, U.lname, U.phone, U.lastLoggedIn, U.choiceId, U.experience, T.label AS teamLabel, R.label AS roleLabel FROM User U...
1
by: ezysetup | last post by:
Last week I suddenly could not open Access tables, queries, etc. in either design or data entry view. If I create a new db, with a new table, everything seems okay. However, once I try to open...
8
by: fredo | last post by:
This question was asked in comp.lang.javascript with no result. In IE5.x and IE6, I want to display an image when the user rolls over a text link. The image does indeed display, but only on the...
4
by: Stan | last post by:
I am using MS Office Access 2003 (11.5614). My basic question is can I run a query of a query datasheet. I want to use more that one criteria and can not get that query to work. I thought I...
1
by: Tudor | last post by:
Hi All. I'm new to this forum (as a registree) although I do come by here to find answers which I do always find. I thought I'd post a question and see if anyone can help! I'm developing a DB in...
14
by: kpfunf | last post by:
I have a select query that uses an "OR" join. With that join in place, I cannot edit the data in the query datasheet; if I delete the "OR" and only use one join, then I can edit the data. I read...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.