Problem: An attempt to open a query in Microsoft Access 2007 results in the "Join expression not supported" error. You can't see the query results nor edit the query to correct the alleged problem because the query view closes immediately. However, you can execute the query from VBA and even export its results to Excel.
Solution:
Debug.Print CurrentDb().QueryDefs("YourQueryName").SQL
in the VBA editor will let you see the (incorrect) query definition. You can set the same SQL property to a new string to override the query definition. However, due to a bug in MS Access in handling JOINs the query might stay unopenable despite attempts to repair:
This problem occurs when a query contains a JOIN expression which links two tables using more than a single column in each of them. For example:
SELECT a.* FROM a LEFT JOIN b ON (a.col1=b.col1 AND b.col2='value')
Access tends to automatically and incorrectly strip the parentheses from the join expression, which leads to the error message mentioned earlier.
Posts
8 comments:
You say that Access incorrectly strips the parentheses from the join expression, but your example does not appear to show how to stop this happening? I need to create an outer join but am not able to do so as I get the error message "Join Expression Not Supported".
FalkosGold, if your problem is indeed the one described in this post (and not just a syntax error), then you won't be able to edit or open the query normally with the built-in Access query editor/viewer. However, you can define it with VBA (setting the SQL property to contain the query's SQL text, as mentioned in the article). Then you can access the records from VBA or from some other query which uses the problematic one.
How would someone with limited experience and no working knowledge of VB open up queries already created? I am having to rebuilt queries over-and-over again from scratch each time I need to adjust them in any way due to a business need change, etc.
I don't know of any workaround other than what I described. However, if you managed to rewrite the queries so that they wouldn't contain the bug inducing type of JOIN, they would remain openable.
Got it today.
Sorry for my expressions but I knew Access is shit, OK I can live with crazy SQL style, I can live with various non-existant features, but the fact that Access itself modifies my query that I'm not able to open any more is beyond my understanding. I didn't know it is so big shit :@
And THANK YOU for the solution.
Microsoft really screwed up their SQL syntax functionality here. I can plug in standard SQL into Excel and it runs like it would if ran through the SQL Server Management Tool. But plug it into Access and it gets all messed up. I can't see why it's do difficult for Microsoft to standardize their SQL syntax. ACCESS = CRAP.
Hi
I found one solution, it may not work for all scenarios but it fits most of them.
Problem Query:
SELECT a.* FROM a LEFT JOIN b ON (a.col1=b.col1 AND b.col2='value')
Work Around:
SELECT a.* FROM a LEFT JOIN b ON (a.col1&b.col2 = b.col1&'value')
Thank you so much for sharing this. It saved me hours of effort that I put to create a query that worked initially but didn't open after saving and trying to rerun. You are awesome!
Post a Comment