Formatting SQL

[shaun.bauer]

Hi All

I have a problem at work where I need to alter queries in Access. Usally these queries are huge and involve unions and other tricky things that the Design editor can't handle so I am left with the SQL. I am pretty good with SQL, but anyone who has tried to edit large queries in the Access SQL editor would be feeling my pain.

Now I know that if I format the SQL, close and return, Access will put it back to a big block of data, but I was thinking that maybe I could write a module that I call from the toolbar which, when I click, makes the data more readable (don't mind if I have to do it on every open). After some googling, I found plenty of ways to edit SQL queries, but nothing that would do it right in front of me.

Does anyone know of a way to edit sql via a module within the editor, or know of somewhere that I can get a module that will already do what I need?

Many thanks

 

[Banana]

The problem is that when you save queries, Access reformat it back into its usual style so you lose all formatting. A old moaning point, that.

I'd just use a website to quickly beautify the SQL to aid in comprehension.

 

[ajetrumpet]

Hi All

I have a problem at work where I need to alter queries in Access. Usally these queries are huge and involve unions and other tricky things that the Design editor can't handle so I am left with the SQL. I am pretty good with SQL, but anyone who has tried to edit large queries in the Access SQL editor would be feeling my pain.

Now I know that if I format the SQL, close and return, Access will put it back to a big block of data, but I was thinking that maybe I could write a module that I call from the toolbar which, when I click, makes the data more readable (don't mind if I have to do it on every open). After some googling, I found plenty of ways to edit SQL queries, but nothing that would do it right in front of me.

Does anyone know of a way to edit sql via a module within the editor, or know of somewhere that I can get a module that will already do what I need?

Many thanks

hey shawn! get what? there might be a way around. make a good little interface, then have a combo will the list of all the queries from the MysysObjects table. get the type number of a query object from that table and query them out that way. SELECT THE name column. then on your form, say something like "select the query you would like to edit. once they select and press OK, have a jhuge-monstrous text area at the bottom that displays the sql for that query, in readable format for human beings. so example.....say tere's a combo, the recordsource is this:

SELECT [name] FROM MSYSobjects WHERE TYPE = 5

THEN ON CLICK OF THE BUTTON, PUT THE SQL OF THAT QUERY (in readable format of course, in the text area! like this:

on click()

me.textarea = currentdb.querydefs(me.combobox).sql
me.requery

then...say they change the stuff...afterwards, you update the query again, like so:

currentdb.querydefs(me.combobox).sql = me.textarea

don't worry about line breaks that are there to make it more humanized. access eliminates those automatically. double spaces on the hand will error out, so watch for them. syntax error will probably throw you an error as well, but i have no idea what the name of it would be. experiment with it, but it's probably want you to do in order to bypass that annoying 1 size sql font that MS came up with. ;D

 

[namliam]

Welcome to AWF Shaun,

How about simple stuff just in VBA?

mySQL = ""
mySQL = mySQL & " Select bla bla "
mySQL = mySQL & " from here "
mySQL = mySQL & " where this "
mySQL = mySQL & " Union all "
mySQL = mySQL & " select that "
mySQL = mySQL & " from there "
etc...

currentdb.querydef("YourQuery").sql = mysql

Edit your query in a nice format, without the need for reformatting etc... while beeing able to send your sql into your actual query real fast...
This is the way I do it anyways.

 

[Atomic Shrimp]

That's the way I do it too - it can be a bit of a nuisance though if you want to copy and paste the SQL out of your code in order to test it in the query designer, but you can get around this by setting a breakpoint right after the code that builds the mySQL string, then typing ? mySQL in the immediate window - and the built SQL will be shown, in easy-to-copy form.