Is there a simple way to open the query window

[magnusstefan]

Hi all!

I use SQL in Access, and I cannot find a easy way to open the Query window. Is there any one? Or can you build one with macros, for instance?

For the moment I must do this to open the Query window:


1) (Go to the tab) "Create".
2) Click on Query/Query design.
3) Click "Close" in the window "Show table".
4) Click on "SQL" in the upper left corner.

Now, that is too complex if you often use the Query window.
Any suggestions?!

[ssanfu]

The ribbon frustrates me, so I added my most used commands to the QAT.

That gets it down to
1) Click on the QAT icon to create a new query (I always have the ribbon "minimized")
2) Click "Close" in the window "Show table".
3) Click on "SQL" in the bottom right corner. (this is in A2010)

Best I have been able to do.

[ranman256]

Microsoft ruined Access by removing a dozen quick keys and icons I used CONSTANTLY.
Now designing is MUCH slower, having to find the correct toolbar.
Bad move Msoft.

[ItsMe]

After struggling to learn the Ribbon, I don't give it much thought. You can create new "Groups" within the Ribbon via Options. You can add the SQL view button to a new group. However, this is not too helpful because you need the query to be open already in DS view or Design view.

I usually right click objects in the Nav Pane and go straight to design view. There must be a way to create a custom shortcut menu that is global to your app and add the SQL view there.

[CJ_London]

If you only want the sql view you can create your own form very easily

You need a large unbound textbox, called say txtSQL and use whatever you want to choose your query - say a listbox (called lstQuery) then on the afterupdate event put

txtSQL = CurrentDb.QueryDefs(lstQuery).SQL

Obviously you'll need a save button to update the querydef once changed

You can also have a subform where the sourceobject is set to "query." & lstQuery to display the results - quite useful when developing the sql to see the results as you make changes - saves switching screens

[magnusstefan]

Hi!

I have followed your advice now, and it makes things a little easier. It does not actually solve the problem, but it eases the pain, and that is good too! =)

[magnusstefan]

Hi!

I did not understand this: "There must be a way to create a custom shortcut menu that is global to your app and add the SQL view there."
Do you think that there really are such a possibility, but you do not know how to do it?

[ItsMe]

Here is a sample DB that creates shortcut menus. I did not look real hard but I could not see a way to use the new custom shortcut menu as the shortcut menu Access uses when clicking the Navigation Pane. I checked in options. Maybe I did not spend enough time on it.

The other thing is I could not find a list of the Policy ID's for the various Control options within a CommandBar. So I do not know what number to use for SQL view or any other menu item.

[magnusstefan]

If you only want the sql view you can create your own form very easily

You need a large unbound textbox, called say txtSQL and use whatever you want to choose your query - say a listbox (called lstQuery) then on the afterupdate event put

txtSQL = CurrentDb.QueryDefs(lstQuery).SQL

Obviously you'll need a save button to update the querydef once changed

You can also have a subform where the sourceobject is set to "query." & lstQuery to display the results - quite useful when developing the sql to see the results as you make changes - saves switching screens

I tried to do this, but there are several things I do not understand I am afraid. Maybe the problem is that I do not understand the relation between the unbound textbox and the listbox. (There should be one, right?) I could not find a way to connect them. (Also, I did not understand why the textbox is needed at all.)

I will now try to upload the test file. This is REALLY awkward for you since I use a Swedish version, and I must really apologize for this! But I try to translate a few things. In the section Design (which have the same name in Swedish) the section "Verktyg" = Tools. "Egenskapssida" = Property page and the tab "händelse" = event. "Efter uppdatering" = After update. In the actual query form "obunden" = unbound.

Members 1.1.accdb

[magnusstefan]

Thank you for the suggestion!
I looked at this, and at the moment I do not know how to develop it further, but maybe I will find out something later on!

[CJ_London]

Here is a working copy to give you an idea how it works - you'll still need to add a save button

[magnusstefan]

My spontaneous reaction:

1) This is not what I asked for.
2) This is much better, and much more interesting!
3) I have never seen anything like it.
4) I do not understand much of this.

Point one does not matter, Point two is wonderful, Point Three is as it is and Point four is just embarrassing.
First: I suppose that VB-code is an important part of this, and I do not really know anything about VB-code.
Conclusion: Time to learn at least the basics in VBA, right?

I will try to explain which the few parts of this I actually understand are:
1) A few Queries have been designed first and they are somehow called upon by an element in the form.
2) That element seems to be an unbound listbox: On the event tab there is a event procedure that seems to lead to (come from?) some VBQ code there one part is exactly your first suggestion: "TxtSQL = CurrentDb.QueryDefs(lstqry).SQL"
3) Since the unbound textbox to the right in the form is name "TxtSQL" I understand that the code above is some kind of instruction to send the actual content of the chosen Query in to the box.
4) I have noted that on the tab Data on the listbox there is a SELECT Statement on the second line. I do not understand it at all. When I check it up I see a mysterious table named MSysobjects I do not understand either. I can only understand that the SELECT statement is about type and flag.

Do you think it is possible to explain how this work for me at the level I am at now, or should I rather come back after trying to learn the basics of VBA and maybe something more. (And if so, what should I learn?)

[CJ_London]

Conclusion: Time to learn at least the basics in VBA, right?

Probably - although Access provides some helpful facilities they can be quite limited and don't do exactly what you want.

2) That element seems to be an unbound listbox

it is unbound, but look at the rowsource (see your question 4)

I understand that the code above is some kind of instruction to send the actual content of the chosen Query

correct - but I would phrase it as 'the code populates the control txtSQL with the SQL property of the query selected'

I see a mysterious table named MSysobjects

MSysObject is a system table - you can view all the system tables if you go to file>options>current database>Navigation Options and tick the 'show system objects' box - click OK then OK again. You cannot change the contents of these tables but they can be useful references - particularly this one. An alternative method would be to have referenced the queries collection - something else for you to learn about!

should I rather come back after trying to learn the basics of VBA and maybe something more

It depends where you want to go with it. Access is a powerful development tool rather than a finished application like excel, although you can build and distribute finished applications. I would first learn about Access and using correct terminology. Learn about collections, objects and properties. And how they are referenced in VBA code and SQL code. It sounds like you have a background with SQL for other db systems, so no need to mention about table design, normalisation etc. But I would read up on using recordsets. Access SQL is similar to other SQLs but does not have all the same functions, although many will exist or can be created in VBA. For example Oracle has a recursive SQL functionality which does not exist in Access, but a function can be created in VBA which calls itself which achieves the same objective.

Remember Access is not a database, it is a development tool consisting of a database engine (ACE since 2007) and a GUI developer. You can just as easily use SQL Server, MySQL, etc instead of ACE for the back end.


You might find this link useful for some pointers

https://www.accessforums.net/access/...ess-52954.html

One thing I find useful when developing is to use overlapping windows - this means I can have several tables/queries open and visible at the same time rather than having to flip between tabs - helps with data verification etc

[magnusstefan]

Quick answer now:
1) Have begun studying VBA with the intention to learn at least the basics.
2) Have also studied you answer and the actual example in detail. My intention is to give an answer and a follow up question later this week.