Question posted 2009 · +11 upvotes
MS Access has limited capabilities to manage raw SQL queries: the editor is quite bad, no syntax highlighting, it reformats your raw SQL into a long string and you can’t insert comments.
Debugging complex SQL queries is a pain as well: either you have to split it into many smaller queries that become difficult to manage when your schema changes or you end-up with a giant query that is a nightmare to debug and update.
How do you manage your complex SQL queries in MS Access and how do you debug them?
Edit
At the moment, I’m mostly just using Notepad++ for some syntax colouring and SQL Pretty Printer for reformatting sensibly the raw SQL from Access.
Using an external repository is useful but keeping there’s always the risk of getting the two versions out of sync and you still have to remove comments before trying the query in Access…
Accepted answer +11 upvotes
For debugging, I edit them in a separate text editor that lets me format them sensibly. When I find I need to make changes, I edit the version in the text editor, and paste it back to Access, never editing the version in Access.
Still a major PITA.
Top ms-access Q&A (6)
- How can I modify a saved Microsoft Access 2007 or 2010 Import Specification? +31 (2008)
- OleDbCommand parameters order and priority +28 (2009)
- Is there an equivalent to the SUBSTRING function in MS Access SQL? +26 (2009)
- What do I need to read Microsoft Access databases using Python? +25 (2009)
- MS Access library for python +24 (2009)
- is there any replacement of Access? +21 (2009)
ms-access solutions on this site
.