Solved

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

Posted on 2011-02-24
2
323 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

734 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