auto-run all of my queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nh - 30 Nov 2004 12:41 GMT

I am building a process which will ultimately be done in SQL server, but I
am prototyping in Access..

I have a load of tables and a series of action queries which import dats,
manipulate it and the output it.

qry1 - Transfer data into new table
qry2 - Delete old rows
qry3 - Remove commas
qry4 - Add brackets
qry.......

I have writtten a module which runs a them all one after another, but the
problem is that every time I cerate a new query in the process, I have to
add another line to the code to run the new query...

Can someone tell me how to write some code which systematically runs all of
the queries in the database in alphabetical order.. (this would also be
useful for so many other tasks..)

I know there is a system table which hold this information, but I have no
idea how to get a list of queries, and loop through it running each one as
it goes...

Can someone help me?

Thank you

Nick

Reply to this Message

Bas Cost Budde - 30 Nov 2004 13:31 GMT

All queries:

select name from msysobjects where type=5 and name not like "~sq*"

Signature

Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

Reply to this Message

Nikos Yannacopoulos - 30 Nov 2004 14:27 GMT

Nick,

Something like this will do it:

Sub Run_All_Queries()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT Name FROM MSysObjects"
strSQL = strSQL & " WHERE Left([Name],3) <> '~sq' AND Type = 5"
strSQL = strSQL & " ORDER BY Name"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
Do Until rst.EOF
DoCmd.SetWarnings False
DoCmd.OpenQuery rst.Fields(0)
DoCmd.SetWarnings True
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

I have to say, though, that as a minimum precaution you should make all your
action query names start with a given prefix, and filter on that rather than
just excluding the system ones (~sq), so you will be able to add non-action
queries if need be, without them being opened from the code.

Note: this piece of code requires an appropriate DAO reference.

HTH,
Nikos

> I am building a process which will ultimately be done in SQL server, but I
> am prototyping in Access..
[quoted text clipped - 25 lines]
>
> Nick

Reply to this Message

nh - 30 Nov 2004 14:40 GMT

That is perfect, thank you Nikos.

> Nick,
>
[quoted text clipped - 61 lines]
> >
> > Nick

Reply to this Message