asked on

Get 'query names' from Access via vbscript

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!

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ioane

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.

SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockiroads

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

Nathan08

ASKER

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!

Nathan08

ASKER

Tramtrak- that looks like VBA- if you were doing it from VBScript would you need to call the application etc first?

rockiroads

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

Nathan08

ASKER

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!

rockiroads

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