Solved

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

Posted on 2011-02-24
2
318 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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now