473,583 Members | 3,437 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 18533
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**@bridgecan ada.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.

Unfortunatel y, 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**@bridgecan ada.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**@bridgecan ada.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**@bridgecan ada.com> wrote in
news:st******** *************** *********@4ax.c om:
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 "ProblemQue ry"
with the actual name of your defective query):

Debug.Print CurrentDB().Que ryDefs("Problem Query").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********@bwa y.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 "ProblemQue ry"
with the actual name of your defective query):

Debug.Print CurrentDB().Que ryDefs("Problem Query").SQL

You can then copy that SQL into a new query.


Nov 13 '05 #6
Mike Bridge <mi**@bridgecan ada.com> wrote in
news:0b******** *************** *********@4ax.c om:
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.somethingels e)

....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********@bwa y.net.invalid> wrote:
Mike Bridge <mi**@bridgecan ada.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**@bridgecan ada.com> wrote in
news:1f******** *************** *********@4ax.c om:
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
3084
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 application 'NAMD' (do searches for IMD and NAMD if you want to learn more). The protocol is very simple: both ends of the TCP connection can send...
4
11254
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 the content in a table changed, is it necessary for a old recordset to renew itself by do "Requery()"? Thanks for your help!
7
31546
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" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception...
12
18660
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 LEFT JOIN UserTeamAssoc UTA ON UTA.userID = U.userID, Role R, UserRoleAssoc URA, Team T WHERE U.userID = URA.userID AND URA.roleID = R.roleID AND...
1
1862
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 the table then I receive a dialog box (having an explanation mark and "Microsoft Access" as the dialog title) with "Unknown" as the warning. Macros...
8
9253
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 first rollover. It seems that the image height and width become set to zero after the image is displayed once; or perhaps that the...
4
3120
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 might be able to accomplish the results in two steps by using two queries. If this is possible how can I do it? Thank you, Stan Hanna
1
2916
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 2003 (I've been using Access since version 2). Our departemtn recently relocated and the printer I had been using has been switch off. So yeah, we have...
14
2885
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 this article that explains I can't edit because of the SQL specific statement. Is there a way around this so that I don't have to open this query, look...
0
7821
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8172
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8317
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7928
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6574
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5695
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5369
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3813
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
1151
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.