auto-run all of my queries

[nh]

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

 

[Bas Cost Budde]

All queries:

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

 

[Nikos Yannacopoulos]

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

 

[nh]

That is perfect, thank you Nikos.

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..

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