MS Access Databases Queries Editor

Download QueryEditor_v0.0.2.7.zip - 288.07 KB

Introduction

Try to create a Union query by MS Access, or an Update one, or an Insert one………..

If you wanted to see (or edit) the Sql query, What if the query a little bit long ………

The editor there sucks …………yes it does.

The Font Size doesn't change …Key words don't get colored….,

Sometimes Tables names are hard to remember, you have to memorize Tables/Queries/Columns names (or go to their places to copy and paste)

I think what mentioned has enough reasons to make me get sick with that editor and make my own one………

Any way…It's a Windows Application, developed on VB.Net 2008 IDE,

A very productive, easy to use tool to edit your Sql Queries.

Advantages

We're gonna see advantages through this little tutorial.

1. Connecting to a new DB: won't be a big deal for you any way.
2. Saving Current Query To A File: won't be a big deal for you either.
3. Data Base Schema:
   when you connect to a new Data Base you will see a tree of its objects (Queries-Tables-Columns).
   Each column in the Tree will have an icon that describes its Data Type.
   And in this Tree you'll see a sub Tree of Functions that could be used in creating queries.
   You can Drag and Drop Tables Name, Queries Names, Columns Name or Functions Name from the Tree to the Editor …..
4. Coloring Key Words:

   you'll see your query well colored, it's more readable so, even more, you can change the colored to your preferences, and it will save your preferences for next times.

5. Adding Parameters:

   It provides a grid for you to enter Parameters Names and Values to use in Parameterized Queries.

6. Auto Completion:

   It provides auto completion when you press (Ctrl + Space),auto complete list contains every thing you could use in your query (Tables names , Queries names, Columns names, Functions names , Parameters names),If you started to write some word, and pressed (Ctrl + Space) the auto complete list is gonna get filtered so it contains only words that start with the part of word you have already entered, Then you can choose what you like from the list by one of the three available ways (mouse Double Clicking it , pressing Space while it's selected , or pressing Enter while it's selected),

   when you Choose your target from the list it will replace the part of the word you have already entered.

   Any time you wanted not to use the already shown auto complete list you can just press ESC, or mouse click out of it,

   (We'll talk later about technical treatments).

7. 7. Commenting & Executing Selected Part:

   I know ………I know ………MS Access Queries don't support commenting.What if the user is trying to make up his/her mind, user didn't exactly know what to write (yet), he/she is bringing info from some source, and needs to execute some part of it,

   user is gonna be a programmer any way, and he/she's got to know that he/she won't be able to use query the way it is in the Editor here.

   and so for "Executing Selected Part",I think no need to say more for this, all user has to do is to Select some part of the written text in the Editor and Execute,

   Any way, you can make a "Single Line Comment" by preceding it by a double Dash --or a "Multi Line Comment" by preceding it by this /* and following it by this */ (just surround it by these /* */ for god sakes)

8. Text To Code:

   It provides a nice tool that converts the query statement into code can be used in either languages VB.Net or C#.

   That nice idea was inspired from a great man (and most of its code is written by him), who has enlightened me by his books and Blogs, he is Turki Al-asiri

9. Exporting Output to Excel:

   it provides three manners to export the grid-output to Excel Sheet.I know one manner is enough, but I wanted this tool to be Educational to you more than being commercial or helping tool, so you'll see three manners to export data to Excel Sheet (and there are more)

   (We'll talk later about technical treatments).

10. Friendly User Interface:

    just discover the Menus and their shortcuts.And the DB Objects Context Menus

Disadvantages

1. Adding Parameters:
2. Unfortunately typing a Parameterized query won't add parameters to the Parameters list, too bad that you have to enter them manually.
3. Enormous Data:

   I'm so sorry to say that when it comes to the big data (very big data ) it's not gonna be a good result, It's gonna get un-predictable, it could take a long time to end (very long time :Minutes), or it could BREAK DOWN (god forbidden)

4. Relative Lazy DB Scheme Fetching:

   It's a technical thing, It got lazy because of the operations that have been done to make later usage Faster (like Filling the Tree, DB objects Auto complete list …..etc)

5. Lazy Keywords Coloring (only long queries):
6. Unfortunately long queries get too lazy to color keywords.

   For now I think I'm gonna leave it this way……….may be in future I could make a more tight Coloring Algorithm (I'm so sorry, I can't do it now, I'm too busy for next 5-6 months……..maybe),

   You're gonna ask me a question,How long does it have to be to feel the slowing?

   1. Well……..just for records, A query of 1000 letter didn't feel it.
   2. the much Strings (quotes ') and Aliases (Brackets []) your query has the much slowing it gets

Examples

I'm gonna put them as screen shots,I think what mentioned before gives a plenty of Illustrations, so no need for more comments:

Example 1:

Example 2:

Example 3:

Technical Treatments

1. Auto Completion:

   connect to the Northwind.mdb sample databaseconsider you tried to write the next statement

   Select [CategoryName] From [Categories]

   And for some reason you wanted to change the Selected Column

   From [CategoryName] to [CategoryID]you would try to delete the end of it and try to use the Auto Completion utility, like this

   Select [Category From [Categories]

   Now you pressed the (Ctrl + Space) and you had the Auto Completion list opened, ... You selected the Column you need and pressed (Enter) ,………Surprise ………………..You got this

   Select [[CategoryID] From [Categories]

   Auto Completion utility takes the word to complete without

   Non-Word characters (Brackets, Commas, Dollar Sign, Asterisks ……………etc).

   Not cool, but you need to take it into considerations.

2. Lazy Keywords Coloring (only long queries):

   The reason behind the lazy coloring is that the tool (RichTextBox) used to color words tries to color All Text every time the text in the tool changes,…….that takes time,………….

   I tried to make another algorithm that colors the only current affected line, it still slow,

   I tried to make another algorithm that colors the only current affected word, it still slow,

   I think we have to think about it later

3. Export Data To Excel:

   this tool uses three manners to export the data to Excel, I mentioned before that I want this tool to be Educational to you,

   Ironically Excel 2003 recognizes formats more than Excel 2007 (of course Excel 2007 does recognizes them but not in its own extension "*.xlsx", it recognizes them in "*.xls" extension)

   you see, if you write an HTML-Table file and save it as "*.xls"Excel 2003 will recognize it , but Excel 2007 won't with the "*.xlsx" extension ,but will with "*.xls" extension

   if you write an XML file (in special Format) and save it as "*.xls" Excel 2003 will recognize it ,but Excel 2007 won't with the "*.xlsx" extension ,but will with "*.xls" extension

   [ To learn about this XML special-Format, open Excel 2003, create your sheet, format it by Excel, then in the [Save As] dialog choose the "XML SpreadSheet", save it , and then open it with Notepad, it's very comprehensible.]

   [ the code used in XML creation is a Remixed Version of an C# Function written by Xodiak in an article he made here in codeproject from here, I converted it to VB.Net and re-organized it the way I like it to be]

   one more manner (but we didn't used),if you write a Tab-Separated file and save it as "*.xls" Excel 2003 will recognize it , but Excel 2007 won't with the "*.xlsx" extension ,but will with "*.xls" extension

   The last manner used is to use the Excel Application to do the job, it requires the user to have the Excel installed,and it's the most slow manner among the mentioned manners

   I hope you get a great information and ideas in this tool, so it helps you in other applications you create

4. What Do I need To Comprehend The Code:
5. the code uses some technologies you need to comprehend to be able to see how the code works:
1. Linq To Objects:
   It's been used all over the code, and you can't even think about reading the code without having a clue at least.Don't worry I've seen some tutorial for youin Arabic from here and a site speaks about it in English from here
2. Regular Expressions:
   It's been used to search in the text to color Key words, and you really need to have a clue at least,there is a very good tutorial here in CodeProject (It's all I have read about it) from here  

you're gonna find a copy of the article in the attached zip file , in the project resources , or you can get it from the help menu of the app

Happy Querying.