Can't open query in SQL View to correct query ("Join expression not supported")
in [Help]
|
From: Mike Bridge on 27 Jun 2005 14:08 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
From: Mike Bridge on 27 Jun 2005 14:39 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 <mike(a)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
From: Mike Bridge on 27 Jun 2005 14:49 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 <mike(a)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 <mike(a)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
From: David W. Fenton on 27 Jun 2005 19:24 Mike Bridge <mike(a)bridgecanada.com> wrote in news:stf0c11vclgt41mfebcdv21t37p6btjsns(a)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
From: Mike Bridge on 27 Jun 2005 20:04
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" <dXXXfenton(a)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. |