Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Ability to list all queries on a form then output them to Excel. MS Access 2003

Posted on 2011-02-24
2
Medium Priority
?
330 Views
Last Modified: 2012-05-11
Hi,

I have a database with 75+ queries in which people run on a day to day basis, the majority of these people copy the results into Excel and then work on them.
I am trying to automate the process a bit and create a form which lists all these queries (in the 'Detail' bit of a form) and then have a button next to each of the query names which will export the query results into Excel, saving the file to C:/ and naing it the same name as the query.

Is this possible from MS Access? Will I have to have a table with all the query names in it or is there someway of picking up all the queries on the form?

Thanks!

Many thanks,
0
Comment
Question by:jasocke2
2 Comments
 
LVL 9

Accepted Solution

by:
borki earned 1000 total points
ID: 34968607
You could query the names of all the queries at run-time. See
 Using DAO to access the querydefs collection

However, I normally don't do this, instead I have a table, say tblSysQuery and it contains columns for ID, Name, Description and Queryname. I manually populate this table with the names of the queries I want the user to have access to. I present this to user in a form in a listbox with double click action.

That way I can display a meaningful name and description that is presented to the user. You could add further columns that have custom folders for the destination or any other thing you can think of.

0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 34970152
I think borki's suggestion is a great starting point, since it will end up providing you a more robust user interface. It will require some maintenance, but once you get the infrastructure built you should be able to add/remove queries quickly.

You can also use the MSysObjects table to get your queries. To do that, add a listbox to your form and use this as the RowSource:

SELECT [Name] FROM MSysObjects WHERE [Type]=251658241 AND Left([Name],3) <> '~sq'

Access stores info about "hidden" queries, which are used for various things throughout the db, in that table as well, but it prefixes them all with "~sq", so filtering for that will remove them.

Instead of a button next to each query, I'd add a listbox, and then add a Button with code like this:

Sub YourButton_Click()
  DoCmd.OpenQuery me.YourListBox.Column(0)  
End Sub

This will actually "open" the query (i.e. in the query designer). If you don't want to do that, and instead simply run them, you can do this:

Currentdb.Execute Me.YourListbox.Column(0)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question