Solved

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

Posted on 2011-02-24
2
322 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 250 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 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

830 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