Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
328 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 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

722 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