-
Notifications
You must be signed in to change notification settings - Fork 41
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Issue when using subqueries in JOINs #76
Comments
After some more testing, there is something interesting. I'm looking at the SQL statement produced by Access with the following piece of code in VBE:
Before loading with SELECT SubQuery.ObjectId
FROM MSysACEs LEFT JOIN (SELECT MSysACEs.* FROM MSysACEs) AS SubQuery ON MSysACEs.ACM = SubQuery.ACM; After loading with SELECT SubQuery.ObjectId
FROM MSysACEs LEFT JOIN [SELECT MSysACEs.* FROM MSysACEs] AS SubQuery ON MSysACEs.ACM = SubQuery.ACM; Notice the brackets It explains why Access fails because it looks for a table litterally called Now I wonder: could we try to identify a subquery, and replace the brackets with parenthesis when importing? |
Let me test this on my end to see if I get the same behavior... |
It works just fine on my system. It does not change the parentheses to brackets when exporting and importing. I am using Microsoft Access 2010 (English). I will be curious to learn what you are able to find out on this. |
That is surprising, but I'm using Access 2016 (French) so maybe it comes from either the new version or the language? Sadly I don't have another version on hand to try it out... |
I may be able to borrow another computer at the office tomorrow that has Access 2016 on it... That might help us narrow down the issue. |
I was able to reproduce this issue on the English version of Access 2016, so I don't think this is specific to the French version. The output files generated by the SaveAsText function also look significantly different. Access 2010
Access 2016
Surprisingly, the 2016 file imports just fine into the Access 2010 database, and the query even runs just fine after import. Outputting the SQL with |
MS internal shenanigans I guess! In any case, what about trying to fix the offending SQL by replacing brackets to parentheses for these subqueries? It could even be used behind a configuration flag "Try to fix subqueries after import", deactivated by default. |
I have found something interesting. 1st : create a new query without touching the designerCreate a new query, go to the SQL table, paste the SQL value and save (do not touch the visual designer or anything). Resulting file uses dbMemo format (click to expand)
Most important thing is: importing it works without the bracket issue! 2nd : modify the query with the designerGo back to the query, go to the visual designer, move around a table and save. Resulting file uses the other format (click to expand)
Importing it fails due to the bracket issue... 3rd: create a new query from scratchCreate a new working query like the first one, then: CurrentDb.CreateQueryDef "Temp", CurrentDb.QueryDefs("TestQuery").SQL
Application.SaveAsText acQuery, "Temp", "Test.bas" Resulting file uses dbMemo format and is surprisingly small (click to expand)
SummarySo to sum up:
|
Interesting... I found that I can actually reproduce the issue in Microsoft Access 2010! The key was that I had to adjust the layout in the designer. Another interesting note was that the format of the output file was virtually identical to the 2016 version. (Which would also explain why Access 2010 was able to import the file generated from Access 2016.) I am hesitant to replace brackets with parentheses for a couple reasons. First, brackets are legitimately used for several purposes within queries, such as prompting for input and around table/field names that are not compatible with SQL syntax. (Such as names that include spaces, or use reserved words.) Second, it would probably be pretty difficult to come up with a reliable way to determine which brackets need to be replaced with parentheses, especially in cases where the rebuilt SQL looks like this: A better approach might be to import the export *.sql version of the query directly into the |
I agree with you. String replacement in query would be very brittle with a lot of potential edge cases. I like your new proposition. Could we go even further and maybe import the |
That's an interesting idea... I had not thought of doing that, but it might prove the very best case scenario if it preserved both the layout and the original SQL. I suppose we could just do it across the board for all queries since the overhead would be fairly small compared with the risk of corrupting queries that contain subqueries. What would you think about an option that would turn on this feature for those that encounter this issue? I have never seen it with any of the databases I have worked on, but we can see how things go in the months to come and perhaps make the option on by default if the benefit seems to outweigh the risk. |
I agree, we can start with a flag deactivated by default. I'll activate it and use it extensively for my export/import and we'll see if it's stable (I have around 300 queries). Note that I could simply rewrite the only query that happen to have a subquery, but resolving the issue globally would be better as someone else could face this issue and not be able to migrate from subqueries 😺 |
I'll try to start working on a PR if you want 😉 |
Great work on a solid solution for this issue! You did an excellent job on the pull request. It made it really easy to review and merge the changes into the core. 👍 I did make a few tweaks, which I will describe in some inline notes on bf337b3. |
Thank you! Good improvements for sure 👍 I've taken the liberty to update the Wiki page using your nice explanation: https://github.com/joyfullservice/msaccess-vcs-integration/wiki/Documentation |
Thanks! I just updated the documentation screenshot as well. |
Hello,
Alright so this is more of an Access issue than this tool, but it's something that we should be aware.
Access can't seem to get the export/import right when the query performs a JOIN on a subquery.
For instance, simply create a new query
TestQuery
and use this SQL statement:Go to the VBE, and export it:
Then delete it and import it:
Try to run the query, you should be greeted by an error message:
It seems that it think the subquery should be a table name, search for it, obviously not finding it and report the error.
Modifying slightly the query and saving it again will make it work.
It is clearly an issue with Access and
LoadFromText
, but I wonder if we could detect these subqueries and show a warning or something?Thanks.
The text was updated successfully, but these errors were encountered: