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

x
?
Solved

Get 'query names' from Access via vbscript

Posted on 2009-04-09
9
Medium Priority
?
584 Views
Last Modified: 2012-05-06
I have some access queries that return supplier records with missing data etc.  

Via vbscript I'd like to get access to the names of all available queries (to put in an array), or do some kind of 'loop through all queries' process (referring to the queries by reference number or something), so I can call each query in turn.  

How can I call queries by number or get a list of their names?

Thanks!
0
Comment
Question by:Nathan08
[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
9 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1200 total points
ID: 24104961
This will give you a list of queries:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

mx
0
 
LVL 13

Expert Comment

by:ioane
ID: 24104970
Hi Nathan08,

You can access the collection of querries in a databse by using:

   Application.CurrentDb.QueryDefs(n)

Can also use:

   Application.CurrentDb.QueryDefs.Count

to get the number of querries in the database etc.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 800 total points
ID: 24105006
To get a list of all queries in database.mdb you can use ...

SELECT [Name]
FROM MSysObjects in "C:\folder\database.mdb"
WHERE [Name] Not Like "~*" AND Type=5
ORDER BY [Name];
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24105057
Can u confirm, do you want it in vba? or actual proper vbscripting? the latter would require opening a database, then going through the queries using querydefs
0
 

Author Comment

by:Nathan08
ID: 24105425
Hi guys, thanks for the comments.  It's in vbscript- I'm using a tool called 'automate' which supports vbscript as well as executing sql queries like the ones mentioned above.  If something like those queries above do return the list of names it'll be fantastic, I'll be testing those the day after easter.

Thanks for your efforts so far guys!
0
 

Author Comment

by:Nathan08
ID: 24105431
Tramtrak- that looks like VBA- if you were doing it from VBScript would you need to call the application etc first?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24105820
from what I said before you have to open the database. But my mistake was to use tabledefs, not quite, as that is dao and we are creating access objects. Need to use allqueries which is obtained from the object

this example here iterates thru and displays each query name

dim acc
dim i

set acc = createobject("access.application")
acc.opencurrentdatabase "C:\mydb.mdb"

for i = 0 to acc.currentdata.allqueries.count-1
    wscript.echo acc.currentdata.allqueries(i).name
next

acc.closecurrentdatabase


0
 

Author Comment

by:Nathan08
ID: 24112981
Thanks everyone.  I was getting method or property not found for the vbscript but the query based approaches did work like a charm.  Note to those looking this up later: you'll get an error of 'no read permission on msysobjects'.  Fix this by going to options->view in access and showing system objects, then to tools->security->user and group permissions and give read access to admin for the msysobjects table.

Great work guys, not sure what the best method of dividing points is but since I could only get the queries to work and both queries had their own advantages I'll divide them.

thanks!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24113598
pity that vbscript didnt work for you as it worked fine for me. I tested it before I posted it. Oh well, maybe useful to somebody else then
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

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